參考：http://imysql.cn/2008_07_10_innodb_tx_isolation_and_lock_mode
本文只是對於“SELECT ... LOCK IN SHARE MODE”和“SELECT ... FORUPDATE”事務中的鎖和RR隔離級別內的測試，針對於表結構、索引結構以及其他隔離級別情況下的觸發鎖類型，可以參考網易何登成網盤中“MySQL 加鎖處理分析.pdf”這篇文章，很細致。
何登成百度網盤：http://pan.baidu.com/share/home?uk=4265849107&view=share
下面的內容是參考上面鏈接博文測試的內容，文字略加修改，方便自己查詢和閱讀。
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.
在讀取的行上設置一個共享模式的鎖。這個共享鎖允許其它session讀取數據但不允許修改它。 行讀取的是最新的數據，如果他被其它事務使用中而沒有提交，讀取鎖將被阻塞直到那個事務結束。
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.
在讀取行上設置一個排他鎖。組織其他session讀取或者寫入行數據
測試一：
Variable_name
Value
tx_isolation
REPEATABLE-READ
session 1
session 2
1
update未提交
select
update t1 set b='z'
where a=1
select
* from t1
where a=1
session
1 commit之前，普通select返回的結果都是session 1 commit提交前結果
2
update未提交
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以後session 2返回結果
3
update未提交
select
… for update
update
t1 set b='x'
where a=1
select
* from t1
where a=1 for update
session
1 commit以後session 2返回結果
RR的隔離級別，對於a=1行的update操作會給行加排他鎖
1、普通的select只是對於session 1事務提交前的行數據快照查詢
2、select … lock in share mode屬於共享鎖，與session 1的排他鎖互斥，需要等待session
1提交或者回滾
3、select … for update屬於排他鎖，與session 1的排它鎖互斥，所以也需要等待需要等待session 1提交或者回滾
測試二：
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查詢需要等待session 1事務處理完成或者回滾
5
select * from t1 where a=1 for update
或
select * from t1 where a=1 lock in share mode
無返回，等待
6
select
* from t1 where a=1 for update
或
select * from t1 where a=1 lock in share mode
+---+------+
| a | b |
+---+------+
| 1 | u |
+---+------+
1 row in set (0.00 sec)
無返回，等待
session
2查詢需要等待session 1事務處理完成或者回滾
7
commit
+---+------+
| a | b |
+---+------+
| 1 | u |
+---+------+
1 row in set (33.02 sec)
8
update t1 set b='w' where a=1
session
1事務處理完成或者回滾後session 2獲得查詢結果
9
select
* from t1 where a=1 for update
或
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
或
select * from t1 where a=1 lock in share mode
無返回，等待
session
2事務處理完成或者回滾後session 1獲得查詢結果
11
commit
12
+---+------+
| a | b |
+---+------+
| 1 | w |
+---+------+
1 row in set (10.46 sec)
select
* from t1 where a=1 for update
或
select * from t1 where a=1 lock in share mode
+---+------+
| a | b |
+---+------+
| 1 | w |
+---+------+
1 row in set (0.00 sec)
測試三：
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事務雖然只有一個select但是由於update和select兩個所持有的共享鎖、排他鎖互斥，所以session 1的update事務需要等到session 2提交以後完成
5
update t1 set b='m' where a=1
無返回，等待
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
無返回，等待
session 1未提交事務，等待
8
commit
+---+------+
| a | b |
+---+------+
| 1 | m |
+---+------+
1 row in set (7.16 sec)
此後又做了幾個測試，總結如下：
type
類型
select
快照
select … lock in share mode
共享鎖
select … for update
排它鎖
DML
排它鎖
select
select … lock in share mode
select … for update
DML
select
快照
快照
快照
快照
select … lock in share mode
快照
共享實時
互斥等待
互斥等待
select … for update
快照
互斥等待
互斥等待
互斥等待
DML
快照
互斥等待
互斥等待
互斥等待
