一.基本資訊
版本：mysql 5.6.29
事務隔離級别(TR_ISOLATION): READ COMMITED
建表：
CREATE TABLE `test_locks` (
`id` int(11) DEFAULT NULL,
`name` varchar(20) DEFAULT NULL,
`age` INT(11)
) ENGINE=InnoDB
插入資料：
INSERT INTO test_locks(id,name,age) VALUES(1,'a',10);
INSERT INTO test_locks(id,name,age) VALUES(10,'b',50);
INSERT INTO test_locks(id,name,age) VALUES(16,'c',500);
二.案例測試
測試1(有阻塞)
S1：begin; UPDATE test_locks SET NAME=NAME+'UPDATE' WHERE ID=16;
S2：begin; SELECT * FROM test_locks WHERE id = 1 lock in share mode;
測試2(無阻塞)
S1：begin; SELECT * FROM test_locks WHERE id = 1 lock in share mode;
S2：begin; UPDATE test_locks SET NAME=NAME+'UPDATE' WHERE ID=16;
測試3(無阻塞)
S1：begin; UPDATE test_locks SET NAME=NAME+'UPDATE' WHERE ID=50;
S2：begin; SELECT * FROM test_locks WHERE id = 1 lock in share mode;
測試4(無阻塞)
S1：begin; SELECT * FROM test_locks WHERE id = 1 lock in share mode;
S2：begin; UPDATE test_locks SET NAME=NAME+'UPDATE' WHERE ID=50;
測試5：(有阻塞)
S1：begin; delete FROM test_locks WHERE id = 1;
S2：begin; delete FROM test_locks WHERE id = 50;
測試6：(有阻塞)
S1：begin; SELECT * FROM test_locks WHERE id = 1 FOR UPDATE;
S2：begin; SELECT * FROM test_locks WHERE id = 50 FOR UPDATE;
測試7：(有阻塞)
S1：begin; SELECT * FROM test_locks WHERE id = 1 lock in share mode;
S2：begin; SELECT * FROM test_locks WHERE id = 50 FOR UPDATE;
測試8：(有阻塞)
S1：begin; UPDATE test_locks SET NAME=NAME+'UPDATE' WHERE ID=10;
S2：begin; UPDATE test_locks SET NAME=NAME+'UPDATE' WHERE ID=10;
因為表沒有主鍵或索引，上面除測試2、測試3和測試4之外，都會鎖全表，是以會話2會阻塞。
我們先看測試3:
測試3(無阻塞)
S1：begin; UPDATE test_locks SET NAME=NAME+'UPDATE' WHERE ID=50;
S2：begin; SELECT * FROM test_locks WHERE id = 1 lock in share mode;
由于ID=50的記錄不存在，會話二有一個行鎖(實際就是鎖全表)，會話一沒有記錄鎖，是以沒有阻塞。
執行計劃如下：
(product)[email protected] [demo]> explain UPDATE test_locks SET NAME=NAME+'UPDATE' WHERE ID=50;
+----+-------------+------------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+------------+------+---------------+------+---------+------+------+-------------+
| 1 | SIMPLE | test_locks | ALL | NULL | NULL | NULL | NULL | 3 | Using where |
+----+-------------+------------+------+---------------+------+---------+------+------+-------------+
1 row in set (0.00 sec)1 row in set (0.00 sec)
(product)[email protected] [demo]> explain SELECT * FROM test_locks WHERE id = 1 lock in share mode;
+----+-------------+------------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+------------+------+---------------+------+---------+------+------+-------------+
| 1 | SIMPLE | test_locks | ALL | NULL | NULL | NULL | NULL | 3 | Using where |
+----+-------------+------------+------+---------------+------+---------+------+------+-------------+
Show engine innodb status\G如下：
---TRANSACTION 36625, ACTIVE 97 sec
2 lock struct(s), heap size 360, 1 row lock(s)
MySQL thread id 4, OS thread handle 0x7f20ec040700, query id 20 localhost root cleaning up
TABLE LOCK table `demo`.`test_locks` trx id 36625 lock mode IS
RECORD LOCKS space id 12 page no 3 n bits 72 index `GEN_CLUST_INDEX` of table `demo`.`test_locks` trx id 36625 lock mode S locks rec but not gap
---TRANSACTION 36624, ACTIVE 113 sec
2 lock struct(s), heap size 360, 0 row lock(s)
MySQL thread id 3, OS thread handle 0x7f20ec071700, query id 22 localhost root init
show engine innodb status
TABLE LOCK table `demo`.`test_locks` trx id 36624 lock mode IX
RECORD LOCKS space id 12 page no 3 n bits 72 index `GEN_CLUST_INDEX` of table `demo`.`test_locks` trx id 36624 lock_mode X locks rec but not gap
若把測試3改成如下：
S1：begin; UPDATE test_locks SET NAME=NAME+'UPDATE' WHERE ID=10;
S2：begin; SELECT * FROM test_locks WHERE id = 1 lock in share mode;
會話一會排它鎖全表，會話二再請求共享鎖是就會阻塞。
執行計劃如下：
(product)[email protected] [demo]> explain UPDATE test_locks SET NAME=NAME+'UPDATE' WHERE ID=10;
+----+-------------+------------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+------------+------+---------------+------+---------+------+------+-------------+
| 1 | SIMPLE | test_locks | ALL | NULL | NULL | NULL | NULL | 3 | Using where |
+----+-------------+------------+------+---------------+------+---------+------+------+-------------+
1 row in set (0.00 sec)1 row in set (0.00 sec)
(product)[email protected] [demo]> explain SELECT * FROM test_locks WHERE id = 1 lock in share mode;
+----+-------------+------------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+------------+------+---------------+------+---------+------+------+-------------+
| 1 | SIMPLE | test_locks | ALL | NULL | NULL | NULL | NULL | 3 | Using where |
+----+-------------+------------+------+---------------+------+---------+------+------+-------------+
1 row in set (0.00 sec)
Show engine innodb status\G如下：
---TRANSACTION 36627, ACTIVE 17 sec
2 lock struct(s), heap size 360, 1 row lock(s)
MySQL thread id 9, OS thread handle 0x7f20ec040700, query id 37 localhost root cleaning up
TABLE LOCK table `demo`.`test_locks` trx id 36627 lock mode IS
RECORD LOCKS space id 12 page no 3 n bits 72 index `GEN_CLUST_INDEX` of table `demo`.`test_locks` trx id 36627 lock mode S locks rec but not gap
---TRANSACTION 36626, ACTIVE 42 sec
2 lock struct(s), heap size 360, 1 row lock(s), undo log entries 1
MySQL thread id 8, OS thread handle 0x7f20ec071700, query id 38 localhost root init
show engine innodb status
TABLE LOCK table `demo`.`test_locks` trx id 36626 lock mode IX
RECORD LOCKS space id 12 page no 3 n bits 72 index `GEN_CLUST_INDEX` of table `demo`.`test_locks` trx id 36626 lock_mode X locks rec but not gap
再看下測試2：
測試2(無阻塞)
S1：begin; SELECT * FROM test_locks WHERE id = 1 lock in share mode;
S2：begin; UPDATE test_locks SET NAME=NAME+'UPDATE' WHERE ID=16;
由于沒有主鍵或索引，會話一會對全表産生共享鎖，會話二執行update時，需要擷取排它鎖，但因半一緻性讀特性，update會讀取符合該條件的最近一次送出的記錄并鎖定，并不需要等待會話一釋放鎖。
執行計劃同前面一樣。
Show engine innodb status\G如下：
---TRANSACTION 36630, ACTIVE 20 sec
2 lock struct(s), heap size 360, 1 row lock(s), undo log entries 1
MySQL thread id 11, OS thread handle 0x7f20ec071700, query id 59 localhost root init
show engine innodb status
TABLE LOCK table `demo`.`test_locks` trx id 36630 lock mode IX
RECORD LOCKS space id 12 page no 3 n bits 72 index `GEN_CLUST_INDEX` of table `demo`.`test_locks` trx id 36630 lock_mode X locks rec but not gap
---TRANSACTION 36629, ACTIVE 35 sec
2 lock struct(s), heap size 360, 1 row lock(s)
MySQL thread id 12, OS thread handle 0x7f20ec040700, query id 56 localhost root cleaning up
TABLE LOCK table `demo`.`test_locks` trx id 36629 lock mode IS
RECORD LOCKS space id 12 page no 3 n bits 72 index `GEN_CLUST_INDEX` of table `demo`.`test_locks` trx id 36629 lock mode S locks rec but not gap
最後測試4無阻塞是因為ID=50的記錄不存在，與測試3情況一樣。
測試4(無阻塞)
S1：begin; SELECT * FROM test_locks WHERE id = 1 lock in share mode;
S2：begin; UPDATE test_locks SET NAME=NAME+'UPDATE' WHERE ID=50;
三.半一緻性讀
什麼是半一緻性讀(semi-consistent read)？
簡單來說，semi-consistent read是read committed與consistent read兩者的結合。一個update語句，如果讀到一行已經加鎖的記錄，此時InnoDB傳回記錄最近送出的版本，由MySQL上層判斷此版本是否滿足 update的where條件。若滿足(需要更新)，則MySQL會重新發起一次讀操作，此時會讀取行的最新版本(并加鎖)。semi-consistent read隻會發生在read committed隔離級别下，或者是參數innodb_locks_unsafe_for_binlog被設定為true(該參數即将被廢棄)。
發生semi consistent read(半一緻性讀)情形：
1、RC、RU模式下，或者 innodb_locks_unsafe_for_binlog = 1。
2、先執行非UPDATE SQL，後執行UPDATE。
3、隻影響有實際存在的行。
semi-consistent優缺點分析:
優點:
減少了更新同一行記錄時的沖突，減少鎖等待。
無并發沖突，讀記錄最新版本并加鎖；有并發沖突，讀事務最新的commit版本，不加鎖，無需鎖等待。
可以提前放鎖，進一步減少并發沖突機率。
對于不滿足update更新條件的記錄，可以提前放鎖，減少并發沖突的機率。
在了解了semi-consistent read原理及實作方案的基礎上，可以酌情考慮使用semi-consistent read，提高系統的并發性能。
缺點:
非沖突串行化政策，是以對于binlog來說，是不安全的
兩條語句，根據執行順序與送出順序的不同，通過binlog複制到備庫後的結果也會不同。不是完全的沖突串行化結果。
是以隻能在事務的隔離級别為read committed(或以下)，或者設定了innodb_locks_unsafe_for_binlog參數的情況下才能夠使用。