[MySQL] MySQL lock and isolation level analysis 1

Reference: http://imysql.cn/2008_07_10_innodb_tx_isolation_and_lock_mode

This article is only for "SELECT... lock in share mode "and" SELECT... for the test of lock and RR isolation level in the FORUPDATE transaction, the trigger lock type is applicable to the table structure, index structure, and other isolation levels, you can refer to Netease he dengcheng's article "MySQL lock Processing Analysis plugin", which is very detailed.

He Deng into Baidu Network Disk: http://pan.baidu.com/share/home? Uk = 4265849107 & view = share

 

The following content is based on the test content of the above link blog. The text is slightly modified to facilitate your query and reading.

SELECT... lock in share mode sets a shared mode lock on the rows read. A shared mode lock enables other sessions to read the rows but not to modify them. the rows read are the latest available, so if they belong to another transaction that has not yet committed, the read blocks until that transaction ends. set a share mode lock on the read row. This shared lock allows other sessions to read data but cannot be modified. The row reads the latest data. If it is used by other transactions but not committed, the read lock will be blocked until the transaction ends. SELECT... for update sets an exclusive lock on the rows read. An exclusive lock prevents other sessions from accessing the rows for reading or writing. Set an exclusive lock on the read row. Organize other sessions to read or write row data

Test 1:

	Variable_name	Value
	Tx_isolation	REPEATABLE-READ	Session 1	Session 2
1	Update Not submitted	Select	Update t1 set B = 'Z' Where a = 1	Select * from t1 Where a = 1	Before session 1 commit, The results returned by a normal select statement are all results before session 1 commit is submitted.
2	Update Not submitted	Select... Lock in share mode	Update t1 set B = 'y' Where a = 1	Select * from t1 Where a = 1 lock in share mode	Session 2 returns results after session 1 commit
3	Update Not submitted	Select... For update	Update t1 set B = 'X' Where a = 1	Select * from t1 Where a = 1 for update	Session 2 returns results after session 1 commit
	RR isolation level. An exclusive lock will be applied to the update operation of Row a = 1. 1. Normal select queries the row data snapshot before session 1 transaction is committed. 2. select... Lock in share mode is a shared lock, which is mutually exclusive with the exclusive lock of session 1. You need to wait for session 1 to submit or roll back. 3. select... For update is an exclusive lock, which is mutually exclusive with the exclusive lock of session 1. Therefore, you also need to wait for session 1 to be submitted or rolled back.

 

Test 2:

 

	Variable_name	Value
	Tx_isolation	REPEATABLE-READ
	Session 1		Session 2
	Query	Result	Query	Result
1	Begin
2			Begin
3	Select * from t1 where a = 1 for update
4	Update t1 set B = 'U' where a = 1				Session 2 query needs to wait for session 1 to complete transaction processing or rollback
5			Select * from t1 where a = 1 for update Or Select * from t1 where a = 1 lock in share mode	No return, wait
6	Select * from t1 where a = 1 for update Or Select * from t1 where a = 1 lock in share mode	+ --- + ------ + | A | B | + --- + ------ + | 1 | u | + --- + ------ + 1 row in set (0.00 sec)		No return, wait	Session 2 query needs to wait for session 1 to complete transaction processing or rollback
7	Commit			+ --- + ------ + | A | B | + --- + ------ + | 1 | u | + --- + ------ + 1 row in set (33.02 sec)
8			Update t1 set B = 'W' where a = 1		Session 1 after the transaction is processed or rolled back, session 2 gets the query result
9			Select * from t1 where a = 1 for update Or Select * from t1 where a = 1 lock in share mode	+ --- + ------ + | A | B | + --- + ------ + | 1 | w | + --- + ------ + 1 row in set (0.00 sec)
10	Select * from t1 where a = 1 for update Or Select * from t1 where a = 1 lock in share mode	No return, wait			Session 2 after the transaction is processed or rolled back, session 1 gets the query result
11			Commit
12		+ --- + ------ + | A | B | + --- + ------ + | 1 | w | + --- + ------ + 1 row in set (10.46 sec)	Select * from t1 where a = 1 for update Or Select * from t1 where a = 1 lock in share mode	+ --- + ------ + | A | B | + --- + ------ + | 1 | w | + --- + ------ + 1 row in set (0.00 sec)

 

Test 3:

 

	Variable_name	Value
	Tx_isolation	REPEATABLE-READ
	Session 1		Session 2
	Query	Result	Query	Result
1	Begin
2	Select * from t1 where a = 1 lock in share mode	+ --- + ------ + | A | B | + --- + ------ + | 1 | w | + --- + ------ + 1 row in set (0.00 sec)
3			Begin
4			Select * from t1 where a = 1 lock in share mode	+ --- + ------ + | A | B | + --- + ------ + | 1 | w | + --- + ------ + 1 row in set (0.00 sec)	Although session 2 has only one select transaction, the share lock and exclusive lock held by update and select are mutually exclusive. Therefore, the update transaction of session 1 needs to be completed after session 2 is committed.
5	Update t1 set B = 'M' where a = 1	No return, wait
6		Query OK, 1 row affected (17.49 sec) Rows matched: 1 Changed: 1 Warnings: 0	Commit
7			Select * from t1 where a = 1 lock in share mode	No return, wait	Session 1 has not committed the transaction, wait
8	Commit			+ --- + ------ + | A | B | + --- + ------ + | 1 | m | + --- + ------ + 1 row in set (7.16 sec)

 

After that, I did several tests and summarized them as follows:

Type	Type
Select	Snapshots
Select... Lock in share mode	Shared lock
Select... For update	Exclusive lock
DML	Exclusive lock

 

	Select	Select... Lock in share mode	Select... For update	DML
Select	Snapshots	Snapshots	Snapshots	Snapshots
Select... Lock in share mode	Snapshots	Shared Real-Time	Mutex wait	Mutex wait
Select... For update	Snapshots	Mutex wait	Mutex wait	Mutex wait
DML	Snapshots	Mutex wait	Mutex wait	Mutex wait