MySQL Lock summary

This article was also published in HTTPS://GITHUB.COM/ZHANGYACHEN/ZHANGYACHEN.GITHUB.IO/ISSUES/78

• MySQL Lock Basics

Referring to the structure of the Ho Dengcheng Teacher's article MySQL lock processing analysis, in the middle and added some of the situation that they feel need to consider.

Analyze the lock mode of this session

1. What is the isolation level of the system? Is it RC or RR?
2. Determine the type of lock for SQL, is it a shared or exclusive lock?
3. What is the execution plan for SQL, which involves indexing?
4. If the index is used, is it a primary key index or a level two index?

5. If it is a level two index, is the index unique?

   Analyze if other parallel sessions are blocked
6. First analyze the lock mode of this session according to the above method
7. Traverse all locks on the scan record, including the waiting lock, and enter the lock waiting queue when there is a state conflict.
8. After entering the lock waiting queue, determine the deadlock and select the victim. (using Wait-for-graph, you can refer to the deadlock section in the first link)

9. After the previous transaction releases the lock, the lock is acquired sequentially.

   Data preparation

Mysql> ShowCreate Tabletest\g;***************************1.RowTable:testCreateTable:CREATE TABLE' Test ' (' ID ')int( One) not NULL default ' 0 ', ' v1 'int( One)default NULL, ' v2 'int( One)default NULL, ' v3 'int(Ten) unsigned not NULL default ' 0 ',PRIMARY KEY(' id '),UNIQUE KEY' V3 ' (' V3 '),KEY' Idx_v1 ' (' v1 ')) Engine=innodbDEFAULTCharset=utf81 Row inch Set(0.00SEC) ERROR:No QuerySpecifiedmysql>Select* fromtest;+----+------+------+----+|ID| V1 | V2 | V3 |+----+------+------+----+|0|4| the|0||1|1|0|1||2|3|1|2||3|4|2|3||5|5|9|5||7|7|4|7||8|7|3|8||Ten|9|5|Ten|| -|8| the| -|+----+------+------+----+9 rows inch Set(0.00Sec

Primary key is ID, unique index v3, level two normal index v1.
In the example below, the data in the table is the data from the select query above.

Query primary key lookup + RC

Session1	Session2
Begin
	Begin
Update test set v1=100 where id=10;
	SELECT * FROM test where id=10 for update; Blocking
	SELECT * FROM test where id=9 for update; Empty Set (0.00 sec)
	SELECT * FROM test where id=11 for update; Empty Set (0.00 sec)

Conclusion: Only the X-lock can be added to the corresponding primary key record at this time.

Query unique index lookup + RC

Session1	Session2
Begin
	Begin
Update test set v2=100 where v3=10;
	SELECT * FROM test where id=10 for update; Blocking
	SELECT * FROM test where v3=10 for update; Blocking
	SELECT * FROM test where v2=10 for update; Blocking
	SELECT * FROM test where id=9 for update; Empty Set (0.00 sec)

Why is the X lock added to the primary key? Assuming that there is a concurrency sql: delete from test where id=10 , then concurrent update will not be aware of the presence of the DELETE statement, which violates the constraint that the update/delete on the same record needs to be executed serially.

Why select * from test where v2=10 for update; is it blocking? Because V2 does not have an index on it, MySQL determines that a full table scan adds X locks to each record, but the ID=10 records in the table are X-locked, incompatible, and therefore blocked.
Note Session1 in the UPDATE statement first added IX intent lock, session2 judgment table has IX lock, indicating that the underlying record has a session in addition to the X lock, so direct blocking. Such a flower does not have to go deep into every record in the bottom, to determine whether each record has an IX lock, which is too time-consuming. (see MySQL Lock Base Intent lock section for details)

Conclusion: You need to add two X locks at this point, one for v3=10 on a unique index, and a id=10 tuple on the clustered index.

Find non-unique index + RC

Ditto. The difference is that all the X locks that satisfy the SQL query record are added, and the corresponding primary key is also x lock.

Query no index +RC

Session1	Session2
Begin
	Begin
Update test set v2=1000 where v2=15;
	SELECT * FROM test where v1=4 for update; Blocking

Because the query cannot be indexed, only full table scans are performed, and all records on the clustered index are X-locked (not a table lock, or a row lock on a record that satisfies the condition).
Why not lock on the record that satisfies the condition? If a condition cannot be quickly filtered through the index, then the storage engine plane will lock all records back and then be filtered by the MySQL Server layer. So they locked up all the records.
However, in the 5.1 and later versions, MySQL will release the lock for all non-qualifying records after filtering at the server level, but in earlier versions, MySQL releases the lock only after the transaction has been committed. (High performance MySQL Chinese version third edition P181)

Conclusion: X locks are added to each record.

Query primary key lookup + RR

Same as query primary key lookup + RC.

Query unique index lookup + RR

Same as Query unique index lookup + RC.

Find non-unique index + RR

Session1	Session2
Begin
	Begin
Update test set v2=1000 where v1=7;
	Update test set v1=6 where v1=9; Blocking
	Update test set v1=8 where v1=9; Blocking
	Update test set v1=5 where v1=9; Blocking
	Update test set v1=9 where v1=9; Query OK, 1 row Affected (0.00 sec)

Unlike RC mode, the RR mode requires no magic read, that is, in the same transaction, the current read twice in a row, then these two times the current read returns exactly the same record. The session1 here update test set v2=1000 where v1=7 is the current reading, in order to ensure that no phantom reading, need to add the gap lock at both ends of the v1=7, to ensure that other transactions can not be inserted in this range of data.
Why does the unique index not add gap lock? Because the uniqueness of the unique index guarantees that two times the current read must return a data instead of two, because uniqueness. So be sure that no new data is inserted. But what if the first time you read update test set v2=100 where v3=10 a query with no qualifying records? MySQL will also add gap lock, to ensure that the interval will not have data insertion.

But what this individual does not understand is why the ends of the gap are closed? That updates v1=5 and v1=8 will block?

Query no index + RR

This combination of the above several examples is better understood: each record will be added X lock, second, the cluster index between each record gap (GAP), but also added a gap lock.

More complex examples

Refer to more complex examples

MySQL first in the index layer with gap lock, and then the cluster index corresponding to the primary key plus x lock, and then the server layer to do filtering. Instead of filtering first, and then the clustered index primary key plus x lock.

Summarize

• For locking read, InnoDB locks on all index records it scan to, regardless of whether the record conforms to the Where condition.
• The only effect of the gap lock is to block writes of other parallel transactions, preventing phantom reads. So the best way to decide whether or not to add a gap lock is to determine if the SQL statement needs to prevent Phantom reading.
• For a range query that is not a unique index, Range_read (Start_key,end_key):
• Find the first record that satisfies the criteria by index
• Sequential backward scan, on-the-Go Records, plus lock_ordinary (lock record and previous gap)
• End_key Locate the first record that does not meet the criteria, exit

Where Condition	Positioning conditions	Termination Conditions	Lock Range
ID < X	Infinum	X	(Infinum,x]
ID <= X	Infinum	Next Record of X	(Next record of infinum,x)
ID > X	Next Record of X	Maxnum	(X,maxnum]
ID >= X	X	Maxnum	[X,maxnum]

Resources:
http://hedengcheng.com/?p=771
Http://blog.sina.com.cn/s/blog_a1e9c7910102vnrj.html

MySQL Lock summary