前面兩篇文章討論了MySQL InnoDB的鎖類型與加鎖方式，這次，我們來看看在不同的場景下，不同的SQL會以什麼樣的方式加什麼類型的鎖。
在開始之前，我們先了解一下什麼是聚族索引？
每一張InnoDB表都有且僅有一表特殊的索引，聚族索引（Clustered Index），表中的數據是直接存放在聚族索引的葉子節點頁面中，這樣，根據聚族索引查詢就會比普通索引更快，因為少了一次IO操作。通常，聚族索引就是表的主鍵；如果表沒有主鍵，那InnoDB會把第一個非空的唯一索引當作聚族索引；如果表既無主鍵，又無非空的唯一索引，那麼InnoDB會創建一個隱藏的索引。表中的其它全部索引，都叫做第二索引（Secondary Index），第二索引中只包含自身索引列和聚族索引列的內容，所以當一個表的主鍵很長時，其它的索引都會受到影響。
為什麼要先講聚族索引呢？因為這對理解InnoDB加鎖機制很重要，InnoDB加鎖的對象不是返回的數據記錄，而是查詢這些數據時所掃描過的索引。當我們執行一個鎖讀（SELECT ... LOCK IN SHARE MODE或者SELECT ... FOR UPDATE）時，InnoDB不是對最終的返回結果加鎖，而是對查詢這些結果時所掃描的索引加鎖，如果被掃描的索引不是聚族索引，那被掃描的索引所指向的聚族索引以及其它指向相同聚族索引的索引也會被加鎖。由此可知，當一個鎖讀無法使用索引的話，InnoDB就是遍歷整個表（遍歷整個聚族索引），從而把整張表都鎖住。
我們來看一個例子，首先創建一張表：
CREATE TABLE `tb` ( `id1` int(11) NOT NULL, `id2` int(11) NOT NULL, `id3` int(11) NOT NULL, `id4` int(11) DEFAULT NULL, PRIMARY KEY (`id1`), UNIQUE KEY `uidx` (`id2`), KEY `idx` (`id3`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8
插入一些數據：
mysql> select * from tb; +-----+-----+-----+------+ | id1 | id2 | id3 | id4 | +-----+-----+-----+------+ | 1 | 1 | 1 | 1 | | 5 | 5 | 5 | 5 | | 9 | 9 | 9 | 9 | +-----+-----+-----+------+
會話S1根據id4查詢一條記錄
mysql> start transaction; Query OK, 0 rows affected (0.00 sec) mysql> select * from tb where id4 = 1 lock in share mode; +-----+-----+-----+------+ | id1 | id2 | id3 | id4 | +-----+-----+-----+------+ | 1 | 1 | 1 | 1 | +-----+-----+-----+------+ 1 row in set (0.00 sec) mysql>
接著會話S2中嘗試對id2=5的記錄加鎖。
mysql> start transaction; Query OK, 0 rows affected (0.00 sec) mysql> select * from tb where id2 = 5 for update; ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction mysql>
發生了鎖等待超時，因為會話S1根據非索引字段id4查詢，InnoDB會掃描整個聚族索引（字段id1），並對掃描過的聚族索引及所有指向相同聚族索引的其它索引都加鎖（本例中所有的索引都被加鎖了），所以會話S2在嘗試對id2=5的記錄加鎖時只能等待了。由此可見，正確的設計和使用索引，不光對性能有影響，對並行性的影響也至關重要。
再看一個例子，在可重復讀隔離級別下，會話S1以id3=5（普通索引）字段加鎖查詢tb表
mysql> select * from tb; +-----+-----+-----+------+ | id1 | id2 | id3 | id4 | +-----+-----+-----+------+ | 1 | 1 | 1 | 1 | | 5 | 5 | 5 | 5 | | 9 | 9 | 9 | 9 | +-----+-----+-----+------+ 3 rows in set (0.01 sec) mysql> set session transaction isolation level repeatable read; Query OK, 0 rows affected (0.00 sec) mysql> start transaction; Query OK, 0 rows affected (0.00 sec) mysql> select * from tb where id3=5 for update; +-----+-----+-----+------+ | id1 | id2 | id3 | id4 | +-----+-----+-----+------+ | 5 | 5 | 5 | 5 | +-----+-----+-----+------+ 1 row in set (0.01 sec) mysql>
會話S2的情況如下
mysql> set session transaction isolation level repeatable read; Query OK, 0 rows affected (0.00 sec) mysql> start transaction; Query OK, 0 rows affected (0.00 sec) mysql> select * from tb where id3 = 5 for update; ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction mysql> insert into tb(id1,id2,id3,id4) values(2,2,2,2); ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction mysql> insert into tb(id1,id2,id3,id4) values(8,8,8,8); ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction mysql> update tb set id4 = 6 where id2 = 5; ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction mysql> update tb set id4 = 6 where id1 = 5; ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction mysql>由結果可知：基於普通索引字段（id3 = 5）查詢，在可重復讀隔離級別下，InnoDB會以Next-Key Lock（即Gap-Key Lock與Index-Record Lock的組合）的方式對索引加鎖，如果被掃描的索引（字段id3 = 5）不是聚族聚引，那被掃描索引指向的聚族索引（id1 = 5），及其它指向相同聚族索引的索引（id2 = 5）都會被加鎖。 明白了上面的例子，那官網的總結就很容易理解了，在這裡簡單總結一下： 在可重復讀級別下，InnoDB以Next-Key Lock的方式對索引加鎖；在讀已提交級別下，InnoDB以Index-Record Lock的方式對索引加鎖。被加鎖的索引如果不是聚族索引，那被鎖的索引所指向的聚族索引以及其它指向相同聚族索引的索引也會被加鎖。SELECT * FROM ... LOCK IN SHARE MODE對索引加共享鎖；SELECT * FROM ... FOR UPDATE對索引加排他鎖。SELECT * FROM ... 是非阻塞式讀，（除Serializable級別）不會對索引加鎖。在讀已提交級別下，總是查詢記錄的最新、有效的版本；在可重復讀級別下，會記住第一次查詢時的版本，之後的查詢會基於該版本。例外的情況是在串行化級別，這時會以Next-Key Lock的方式對索引加共享鎖。UPDATE ... WHERE 與DELETE ... WHERE對索引加排他鎖。INSERT INTO ... 以Index-Record Lock的方式對索引加排他鎖。
