MySQL Locking analysis

Reference: MySQL plus lock processing analysis. The article has been described in detail, but also easy to understand, the following is only a summary of the individual.

I. Background 1.1 ISOLATION LEVEL 1.2 lock process

　　Deal with each, add lock by article.

1.3 Two-stage lock 2pl1.4 Gap lock

Gap Lock is a gap lock, that is, two adjacent to effectively record the gap of the lock (the lock is the gap), it is for the insert , to solve the occurrence of phantom reading. It blocks the insert, but does not block delete/update and so on (the record does not exist).

The important difference between RC and RR is phantom reading. So RR needs to introduce gap lock.

Two, lock combination analysis

Say what lock, first of all to understand two prerequisites: 1) Isolation level, 2) the index used. Different isolation levels, different indexes will affect the lock.

2.1 can be submitted read RC

	Primary key/Unique key	Second-level index	No index
Select	No	No	No
Insert	Row lock	Row lock	Row lock
Select...for Update/update/delete	Row lock	Row locks for valid rows	Full table locked Clustered index

2.2 REPEATABLE Read RR

	PRIMARY key/Unique key	Level two index	no index
none	None	none
	row lock	row lock	row lock
	row lock	Row lock for valid rows +gap lock	full table lock clustered index

2.3 Serializable S

	Primary key/Unique key	Second-level index	No index
Select (Snapshot Read)	S	S	S
Other (current read)	X	X	X

Note: When there is no index, the select...for update/update/delete is required to lock the full table, but mysql_server can be optimized, when each lock, if found not the target record, you can release the lock. But that would violate the principle of 2PL.

2.4 Conclusion

Whether or not a deadlock occurs is not the number of SQL statements in a transaction, the key to a deadlock is that the order of two (or more) session Locks is inconsistent.

The lock-up process is processed on a locking-by-article-by-article (Finally, reflected on the clustered index).

• The primary key is added to the clustered index;
• A secondary index, plus a level two index + clustered index;
• No index, added to the clustered index.

So if two sessions use different two-level indexes, then the lock order of the clustered index is inconsistent, which leads to the holding and competition of the lock between sessions, and it is easy to generate deadlock.

MySQL Locking analysis