"MySQL" MySQL lock and isolation level analysis of a

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 UPDATE "in transaction lock and RR isolation level testing, for the table structure, index structure and other isolation level of the trigger lock type, you can refer to NetEase DENGFA into the network disk" MySQL lock processing analysis. pdf "This article is very detailed.

DENGFA into Baidu network disk: Http://pan.baidu.com/share/home?uk=4265849107&view=share

The following is a reference to the above link to test the content of the text slightly modified, convenient for their own query and reading.

SELECT... LOCKinchSHARE mode sets a shared MODE lock onThe rowsRead. A Shared mode lock enables other sessions to ReadThe Rows but not  toModify them. The rowsReadIs the latest available, soifThey belong toAnotherTransactionThat has notYetcommitted, theReadBlocks until thatTransactionends. Set a shared-mode lock on the read line. This shared lock allows the other session to read the data but does not allow it to be modified. The line reads the latest data, and if he is not committed by other transactions, the read lock will be blocked until the end of the transaction. SELECT... for UPDATESets an exclusive lock onThe rowsRead. An exclusive lock prevents other sessions fromAccessing the Rows forReadingorwriting. Set an exclusive lock on the read line. Organization other session read or write row data

Test One:

	Variable_name	Value
	Tx_isolation	Repeatable-read	Session 1	Session 2
1	Update not committed	Select	Update T1 set b= ' Z ' where a=1	SELECT * FROM t1 where a=1	Before session 1 Commit, the normal select returns the result of Session 1 before commit commit
2	Update not committed	Select ... lock in share mode	Update T1 set b= ' y ' where a=1	SELECT * FROM t1 where A=1 lock in share mode	Session 1 Commit after session 2 return result
3	Update not committed	Select ... for update	Update T1 set b= ' x ' where a=1	SELECT * FROM t1 where a=1 for update	Session 1 Commit after session 2 return result
	The isolation level of the RR, which adds an exclusive lock to the row for the update operation of the A=1 row 1. Normal select is only for the row data snapshot query before session 1 transaction commit 2. Select ... lock in share mode is a shared lock, mutex with session 1 exclusive lock, need to wait for session 1 to commit or rollback 3, select ... for update is an exclusive lock, and the exclusive lock of Session 1, so also need to wait for session 1 commit or rollback

Test Two:

	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 queries need to wait for session 1 transaction to complete 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 queries need to wait for session 1 transaction to complete 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 Transaction Completion or rollback session 2 get query results
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 Transaction Completion or rollback session 1 Get query results
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 Three:

	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)	Session 2 Transaction Although there is only one select but due to the update and select two holds a shared lock, exclusive lock mutex, so session 1 update transaction needs to wait until session 2 commits to complete
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 uncommitted transaction, waiting for
8	Commit			+---+------+ | A | B | +---+------+ | 1 | m | +---+------+ 1 row in Set (7.16 sec)

Several tests have been done since then and are summarized as follows:

Type	Type
Select	Snapshot
Select ... lock in share mode	Shared locks
Select ... for update	Exclusive lock
Dml	Exclusive lock

	Select	Select ... lock in share mode	Select ... for update	Dml
Select	Snapshot	Snapshot	Snapshot	Snapshot
Select ... lock in share mode	Snapshot	Share Live	Mutex wait	Mutex wait
Select ... for update	Snapshot	Mutex wait	Mutex wait	Mutex wait
Dml	Snapshot	Mutex wait	Mutex wait	Mutex wait