超全面MySQL語句加鎖分析（上篇）

說在前面的話

本文是用來系統闡述在MySQL中，不同語句在各種條件下的加鎖情況，並不是解釋各種鎖是什麼（或者說加鎖的本質是什麼）

事前準備

建立一個存儲三國英雄的hero表：

CREATE TABLE hero (
    number INT,
    name VARCHAR(100),
    country varchar(100),
    PRIMARY KEY (number),
    KEY idx_name (name)
) Engine=InnoDB CHARSET=utf8;

然後向這個表里插入幾條記錄：

INSERT INTO hero VALUES
    (1, 'l劉備', '蜀'),
    (3, 'z諸葛亮', '蜀'),
    (8, 'c曹操', '魏'),
    (15, 'x荀彧', '魏'),
    (20, 's孫權', '吳');

然後現在hero表就有了兩個索引（一個二級索引，一個聚簇索引），示意圖如下：

語句加鎖分析

其實啊，「XXX語句該加什麼鎖」本身就是個偽命題，一條語句需要加的鎖受到很多條件制約，比方說：

事務的隔離級別
語句執行時使用的索引（比如聚簇索引、唯一二級索引、普通二級索引）
查詢條件（比方說=、=<、>=等等）
具體執行的語句類型

在繼續詳細分析語句的加鎖過程前，大家一定要有一個全局概念：加鎖只是解決並發事務執行過程中引起的髒寫、髒讀、不可重複讀、幻讀這些問題的一種解決方案（MVCC算是一種解決髒讀、不可重複讀、幻讀這些問題的一種解決方案），一定要意識到加鎖的出發點是為了解決這些問題，不同情景下要解決的問題不一樣，才導致加的鎖不一樣，千萬不要為了加鎖而加鎖，容易把自己繞進去。當然，有時候因為MySQL具體的實現而導致一些情景下的加鎖有些不太好理解，這就得我們死記硬背了～

我們這裡把語句分為3種大類：普通的SELECT語句、鎖定讀的語句、INSERT語句，我們分別看一下。

普通的SELECT語句

普通的SELECT語句在：

READ UNCOMMITTED隔離級別下，不加鎖，直接讀取記錄的最新版本，可能發生髒讀、不可重複讀和幻讀問題。
READ COMMITTED隔離級別下，不加鎖，在每次執行普通的SELECT語句時都會生成一個ReadView，這樣解決了髒讀問題，但沒有解決不可重複讀和幻讀問題。
REPEATABLE READ隔離級別下，不加鎖，只在第一次執行普通的SELECT語句時生成一個ReadView，這樣把髒讀、不可重複讀和幻讀問題都解決了。不過這裡有一個小插曲：# 事務T1，REPEATABLE READ隔離級別下
mysql> BEGIN;
Query OK, 0 rows affected (0.00 sec)
mysql> SELECT * FROM hero WHERE number = 30;
Empty set (0.01 sec)
# 此時事務T2執行了：INSERT INTO hero VALUES(30, 'g關羽', '魏'); 並提交
mysql> UPDATE hero SET country = '蜀' WHERE number = 30;
Query OK, 1 row affected (0.01 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> SELECT * FROM hero WHERE number = 30;
+--------+---------+---------+
| number | name | country |
+--------+---------+---------+
| 30 | g關羽 | 蜀 |
+--------+---------+---------+
1 row in set (0.01 sec)在REPEATABLE READ隔離級別下，T1第一次執行普通的SELECT語句時生成了一個ReadView，之後T2向hero表中新插入了一條記錄便提交了，ReadView並不能阻止T1執行UPDATE或者DELETE語句來對改動這個新插入的記錄（因為T2已經提交，改動該記錄並不會造成阻塞），但是這樣一來這條新記錄的trx_id隱藏列就變成了T1的事務id，之後T1中再使用普通的SELECT語句去查詢這條記錄時就可以看到這條記錄了，也就把這條記錄返回給客戶端了。因為這個特殊現象的存在，你也可以認為InnoDB中的MVCC並不能完完全全的禁止幻讀。
SERIALIZABLE隔離級別下，需要分為兩種情況討論：
- 在系統變量autocommit=0時，也就是禁用自動提交時，普通的SELECT語句會被轉為SELECT ... LOCK IN SHARE MODE這樣的語句，也就是在讀取記錄前需要先獲得記錄的S鎖，具體的加鎖情況和REPEATABLE READ隔離級別下一樣，我們後邊再分析。
- 在系統變量autocommit=1時，也就是啟用自動提交時，普通的SELECT語句並不加鎖，只是利用MVCC來生成一個ReadView去讀取記錄。為啥不加鎖呢？因為啟用自動提交意味著一個事務中只包含一條語句，一條語句也就沒有啥不可重複讀、幻讀這樣的問題了。

鎖定讀的語句

我們把下邊四種語句放到一起討論：

語句一：SELECT ... LOCK IN SHARE MODE;
語句二：SELECT ... FOR UPDATE;
語句三：UPDATE ...
語句四：DELETE ...

我們說語句一和語句二是MySQL中規定的兩種鎖定讀的語法格式，而語句三和語句四由於在執行過程需要首先定位到被改動的記錄並給記錄加鎖，也可以被認為是一種鎖定讀。

READ UNCOMMITTED/READ COMMITTED隔離級別下

在READ UNCOMMITTED下語句的加鎖方式和READ COMMITTED隔離級別下語句的加鎖方式基本一致，所以就放到一塊兒說了。值得注意的是，採用加鎖方式解決並發事務帶來的問題時，其實髒讀和不可重複讀在任何一個隔離級別下都不會發生（因為讀-寫操作需要排隊進行）。

對於使用主鍵進行等值查詢的情況

使用SELECT ... LOCK IN SHARE MODE來為記錄加鎖，比方說：SELECT * FROM hero WHERE number = 8 LOCK IN SHARE MODE;這個語句執行時只需要訪問一下聚簇索引中number值為8的記錄，所以只需要給它加一個S型正經記錄鎖就好了，如圖所示：
使用SELECT ... FOR UPDATE來為記錄加鎖，比方說：SELECT * FROM hero WHERE number = 8 FOR UPDATE;這個語句執行時只需要訪問一下聚簇索引中number值為8的記錄，所以只需要給它加一個X型正經記錄鎖就好了，如圖所示：小貼士：為了區分S鎖和X鎖，我們之後在示意圖中就把加了S鎖的記錄染成藍色，把加了X鎖的記錄染成紫色。
使用UPDATE ...來為記錄加鎖，比方說：UPDATE hero SET country = '漢' WHERE number = 8;這條UPDATE語句並沒有更新二級索引列，加鎖方式和上邊所說的SELECT ... FOR UPDATE語句一致。如果UPDATE語句中更新了二級索引列，比方說：UPDATE hero SET name = 'cao曹操' WHERE number = 8;該語句的實際執行步驟是首先更新對應的number值為8的聚簇索引記錄，再更新對應的二級索引記錄，所以加鎖的步驟就是：
為number值為8的聚簇索引記錄加上X型正經記錄鎖（該記錄對應的）。
為該聚簇索引記錄對應的idx_name二級索引記錄（也就是name值為'c曹操'，number值為8的那條二級索引記錄）加上X型正經記錄鎖。
畫個圖就是這樣：小貼士：我們用帶圓圈的數字來表示為各條記錄加鎖的順序。
使用DELETE ...來為記錄加鎖，比方說：DELETE FROM hero WHERE number = 8;我們平時所說的「DELETE表中的一條記錄」其實意味著對聚簇索引和所有的二級索引中對應的記錄做DELETE操作，本例子中就是要先把number值為8的聚簇索引記錄執行DELETE操作，然後把對應的idx_name二級索引記錄刪除，所以加鎖的步驟和上邊更新帶有二級索引列的UPDATE語句一致，就不畫圖了。

對於使用主鍵進行範圍查詢的情況

使用SELECT ... LOCK IN SHARE MODE來為記錄加鎖，比方說：SELECT * FROM hero WHERE number <= 8 LOCK IN SHARE MODE;這個語句看起來十分簡單，但它的執行過程還是有一丟丟小複雜的：
先到聚簇索引中定位到滿足number <= 8的第一條記錄，也就是number值為1的記錄，然後為其加鎖。
判斷一下該記錄是否符合索引條件下推中的條件。我們前邊介紹過一個稱之為索引條件下推（ Index Condition Pushdown，簡稱ICP）的功能，也就是把查詢中與被使用索引有關的查詢條件下推到存儲引擎中判斷，而不是返回到server層再判斷。不過需要注意的是，索引條件下推只是為了減少回表次數，也就是減少讀取完整的聚簇索引記錄的次數，從而減少IO操作。而對於聚簇索引而言不需要回表，它本身就包含著全部的列，也起不到減少IO操作的作用，所以設計InnoDB的大叔們規定這個索引條件下推特性只適用於二級索引。也就是說在本例中與被使用索引有關的條件是：number <= 8，而number列又是聚簇索引列，所以本例中並沒有符合索引條件下推的查詢條件，自然也就不需要判斷該記錄是否符合索引條件下推中的條件。
判斷一下該記錄是否符合範圍查詢的邊界條件因為在本例中是利用主鍵number進行範圍查詢，設計InnoDB的大叔規定每從聚簇索引中取出一條記錄時都要判斷一下該記錄是否符合範圍查詢的邊界條件，也就是number <= 8這個條件。如果符合的話將其返回給server層繼續處理，否則的話需要釋放掉在該記錄上加的鎖，並給server層返回一個查詢完畢的信息。對於number值為1的記錄是符合這個條件的，所以會將其返回到server層繼續處理。
將該記錄返回到server層繼續判斷。server層如果收到存儲引擎層提供的查詢完畢的信息，就結束查詢，否則繼續判斷那些沒有進行索引條件下推的條件，在本例中就是繼續判斷number <= 8這個條件是否成立。噫，不是在第3步中已經判斷過了麼，怎麼在這又判斷一回？是的，設計InnoDB的大叔採用的策略就是這麼簡單粗暴，把凡是沒有經過索引條件下推的條件都需要放到server層再判斷一遍。如果該記錄符合剩餘的條件（沒有進行索引條件下推的條件），那麼就把它發送給客戶端，不然的話需要釋放掉在該記錄上加的鎖。
然後剛剛查詢得到的這條記錄（也就是number值為1的記錄）組成的單向鍊表繼續向後查找，得到了number值為3的記錄，然後重複第2，3，4、5這幾個步驟。
小貼士：上述步驟是在MySQL 5.7.21這個版本中驗證的，不保證其他版本有無出入。但是這個過程有個問題，就是當找到number值為8的那條記錄的時候，還得向後找一條記錄（也就是number值為15的記錄），在存儲引擎讀取這條記錄的時候，也就是上述的第1步中，就得為這條記錄加鎖，然後在第3步時，判斷該記錄不符合number <= 8這個條件，又要釋放掉這條記錄的鎖，這個過程導致number值為15的記錄先被加鎖，然後把鎖釋放掉，過程就是這樣：這個過程有意思的一點就是，如果你先在事務T1中執行：# 事務T1
BEGIN;
SELECT * FROM hero WHERE number <= 8 LOCK IN SHARE MODE;然後再到事務T2中執行：# 事務T2
BEGIN;
SELECT * FROM hero WHERE number = 15 FOR UPDATE;是沒有問題的，因為在T2執行時，事務T1已經釋放掉了number值為15的記錄的鎖，但是如果你先執行T2，再執行T1，由於T2已經持有了number值為15的記錄的鎖，事務T1將因為獲取不到這個鎖而等待。我們再看一個使用主鍵進行範圍查詢的例子：SELECT * FROM hero WHERE number >= 8 LOCK IN SHARE MODE;這個語句的執行過程其實和我們舉的上一個例子類似。也是先到聚簇索引中定位到滿足number >= 8這個條件的第一條記錄，也就是number值為8的記錄，然後就可以沿著由記錄組成的單向鍊表一路向後找，每找到一條記錄，就會為其加上鎖，然後判斷該記錄符不符合範圍查詢的邊界條件，不過這裡的邊界條件比較特殊：number >= 8，只要記錄不小於8就算符合邊界條件，所以判斷和沒判斷是一樣一樣的。最後把這條記錄返回給server層，server層再判斷number >= 8這個條件是否成立，如果成立的話就發送給客戶端，否則的話就結束查詢。不過InnoDB存儲引擎找到索引中的最後一條記錄，也就是Supremum偽記錄之後，在存儲引擎內部就可以立即判斷這是一條偽記錄，不必要返回給server層處理，也沒必要給它也加上鎖（也就是說在第1步中就壓根兒沒給這條記錄加鎖）。整個過程會給number值為8、15、20這三條記錄加上S型正經記錄鎖，畫個圖表示一下就是這樣：
使用SELECT ... FOR UPDATE語句來為記錄加鎖：和SELECT ... FOR UPDATE語句類似，只不過加的是X型正經記錄鎖。
使用UPDATE ...來為記錄加鎖，比方說：UPDATE hero SET country = '漢' WHERE number >= 8;這條UPDATE語句並沒有更新二級索引列，加鎖方式和上邊所說的SELECT ... FOR UPDATE語句一致。如果UPDATE語句中更新了二級索引列，比方說：UPDATE hero SET name = 'cao曹操' WHERE number >= 8;這時候會首先更新聚簇索引記錄，再更新對應的二級索引記錄，所以加鎖的步驟就是：
為number值為8的聚簇索引記錄加上X型正經記錄鎖。
然後為上一步中的記錄索引記錄對應的idx_name二級索引記錄加上X型正經記錄鎖。
為number值為15的聚簇索引記錄加上X型正經記錄鎖。
然後為上一步中的記錄索引記錄對應的idx_name二級索引記錄加上X型正經記錄鎖。
為number值為20的聚簇索引記錄加上X型正經記錄鎖。
然後為上一步中的記錄索引記錄對應的idx_name二級索引記錄加上X型正經記錄鎖。
畫個圖就是這樣：如果是下邊這個語句：UPDATE hero SET namey = '漢' WHERE number <= 8;則會對number值為1、3、8聚簇索引記錄以及它們對應的二級索引記錄加X型正經記錄鎖，加鎖順序和上邊語句中的加鎖順序類似，都是先對一條聚簇索引記錄加鎖後，再給對應的二級索引記錄加鎖。之後會繼續對number值為15的聚簇索引記錄加鎖，但是隨後InnoDB存儲引擎判斷它不符合邊界條件，隨即會釋放掉該聚簇索引記錄上的鎖（注意這個過程中沒有對number值為15的聚簇索引記錄對應的二級索引記錄加鎖）。具體示意圖就不畫了。
使用DELETE ...來為記錄加鎖，比方說：DELETE FROM hero WHERE number >= 8;和DELETE FROM hero WHERE number <= 8;這兩個語句的加鎖情況和更新帶有二級索引列的UPDATE語句一致，就不畫圖了。

對於使用二級索引進行等值查詢的情況

小貼士：在READ UNCOMMITTED和READ COMMITTED隔離級別下，使用普通的二級索引和唯一二級索引進行加鎖的過程是一樣的，所以我們也就不分開討論了。

使用SELECT ... LOCK IN SHARE MODE來為記錄加鎖，比方說：SELECT * FROM hero WHERE name = 'c曹操' LOCK IN SHARE MODE;這個語句的執行過程是先通過二級索引idx_name定位到滿足name = 'c曹操'條件的二級索引記錄，然後進行回表操作。所以先要對二級索引記錄加S型正經記錄鎖，然後再給對應的聚簇索引記錄加S型正經記錄鎖，示意圖如下：這裡需要再次強調一下這個語句的加鎖順序：
先對name列為'c曹操'二級索引記錄進行加鎖。
再對相應的聚簇索引記錄進行加鎖
小貼士：我們知道idx_name是一個普通的二級索引，到idx_name索引中定位到滿足name= 'c曹操'這個條件的第一條記錄後，就可以沿著這條記錄一路向後找。可是從我們上邊的描述中可以看出來，並沒有對下一條二級索引記錄進行加鎖，這是為什麼呢？這是因為設計InnoDB的大叔對等值匹配的條件有特殊處理，他們規定在InnoDB存儲引擎層查找到當前記錄的下一條記錄時，在對其加鎖前就直接判斷該記錄是否滿足等值匹配的條件，如果不滿足直接返回（也就是不加鎖了），否則的話需要將其加鎖後再返回給server層。所以這裡也就不需要對下一條二級索引記錄進行加鎖了。現在要介紹一個非常有趣的事情，我們假設上邊這個語句在事務T1中運行，然後事務T2中運行下邊一個我們之前介紹過的語句：UPDATE hero SET name = '曹操' WHERE number = 8;這兩個語句都是要對number值為8的聚簇索引記錄和對應的二級索引記錄加鎖，但是不同點是加鎖的順序不一樣。這個UPDATE語句是先對聚簇索引記錄進行加鎖，後對二級索引記錄進行加鎖，如果在不同事務中運行上述兩個語句，可能發生一種賊奇妙的事情 ——
- 事務T2持有了聚簇索引記錄的鎖，事務T1持有了二級索引記錄的鎖。
- 事務T2在等待獲取二級索引記錄上的鎖，事務T1在等待獲取聚簇索引記錄上的鎖。
兩個事務都分別持有一個鎖，而且都在等待對方已經持有的那個鎖，這種情況就是所謂的死鎖，兩個事務都無法運行下去，必須選擇一個進行回滾，對性能影響比較大。
使用SELECT ... FOR UPDATE語句時，比如：SELECT * FROM hero WHERE name = 'c曹操' FOR UPDATE;這種情況下與SELECT ... LOCK IN SHARE MODE語句的加鎖情況類似，都是給訪問到的二級索引記錄和對應的聚簇索引記錄加鎖，只不過加的是X型正經記錄鎖罷了。
使用UPDATE ...來為記錄加鎖，比方說：與更新二級索引記錄的SELECT ... FOR UPDATE的加鎖情況類似，不過如果被更新的列中還有別的二級索引列的話，對應的二級索引記錄也會被加鎖。
使用DELETE ...來為記錄加鎖，比方說：與SELECT ... FOR UPDATE的加鎖情況類似，不過如果表中還有別的二級索引列的話，對應的二級索引記錄也會被加鎖。

對於使用二級索引進行範圍查詢的情況

使用SELECT ... LOCK IN SHARE MODE來為記錄加鎖，比方說：SELECT * FROM hero FORCE INDEX(idx_name) WHERE name >= 'c曹操' LOCK IN SHARE MODE;小貼士：因為優化器會計算使用二級索引進行查詢的成本，在成本較大時可能選擇以全表掃描的方式來執行查詢，所以我們這裡使用FORCE INDEX(idx_name)來強制使用二級索引idx_name來執行查詢。這個語句的執行過程其實是先到二級索引中定位到滿足name >= 'c曹操'的第一條記錄，也就是name值為c曹操的記錄，然後就可以沿著這條記錄的鍊表一路向後找，從二級索引idx_name的示意圖中可以看出，所有的用戶記錄都滿足name >= 'c曹操'的這個條件，所以所有的二級索引記錄都會被加S型正經記錄鎖，它們對應的聚簇索引記錄也會被加S型正經記錄鎖。不過需要注意一下加鎖順序，對一條二級索引記錄加鎖完後，會接著對它相應的聚簇索引記錄加鎖，完後才會對下一條二級索引記錄進行加鎖，以此類推～畫個圖表示一下就是這樣：再來看下邊這個語句：SELECT * FROM hero FORCE INDEX(idx_name) WHERE name <= 'c曹操' LOCK IN SHARE MODE;這個語句的加鎖情況就有點兒有趣了。前邊說在使用number <= 8這個條件的語句中，需要把number值為15的記錄也加一個鎖，之後又判斷它不符合邊界條件而把鎖釋放掉。而對於查詢條件name <= 'c曹操'的語句來說，執行該語句需要使用到二級索引，而與二級索引相關的條件是可以使用索引條件下推這個特性的。設計InnoDB的大叔規定，如果一條記錄不符合索引條件下推中的條件的話，直接跳到下一條記錄（這個過程根本不將其返回到server層），如果這已經是最後一條記錄，那麼直接向server層報告查詢完畢。但是這裡頭有個問題呀：先對一條記錄加了鎖，然後再判斷該記錄是不是符合索引條件下推的條件，如果不符合直接跳到下一條記錄或者直接向server層報告查詢完畢，這個過程中並沒有把那條被加鎖的記錄上的鎖釋放掉呀！！！。本例中使用的查詢條件是name <= 'c曹操'，在為name值為'c曹操'的二級索引記錄以及它對應的聚簇索引加鎖之後，會接著二級索引中的下一條記錄，也就是name值為'l劉備'的那條二級索引記錄，由於該記錄不符合索引條件下推的條件，而且是範圍查詢的最後一條記錄，會直接向server層報告查詢完畢，重點是這個過程中並不會釋放name值為'l劉備'的二級索引記錄上的鎖，也就導致了語句執行完畢時的加鎖情況如下所示：這樣子會造成一個尷尬情況，假如T1執行了上述語句並且尚未提交，T2再執行這個語句：SELECT * FROM hero WHERE name = 'l劉備' FOR UPDATE;T2中的語句需要獲取name值為l劉備的二級索引記錄上的X型正經記錄鎖，而T1中仍然持有name值為l劉備的二級索引記錄上的S型正經記錄鎖，這就造成了T2獲取不到鎖而進入等待狀態。小貼士：為啥不能釋放不符合索引條件下推中的條件的二級索引記錄上的鎖呢？這個問題我也沒想明白，人家就是這麼規定的，如果有明白的小夥伴可以加我微信 xiaohaizi4919 來討論一下哈～再強調一下，我使用的MySQL版本是5.7.21，不保證其他版本中的加鎖情景是否完全一致。
使用SELECT ... FOR UPDATE語句時：和SELECT ... FOR UPDATE語句類似，只不過加的是X型正經記錄鎖。
使用UPDATE ...來為記錄加鎖，比方說：UPDATE hero SET country = '漢' WHERE name >= 'c曹操';小貼士： FORCE INDEX只對SELECT語句起作用，UPDATE語句雖然支持該語法，但實質上不起作用，DELETE語句壓根兒不支持該語法。假設該語句執行時使用了idx_name二級索引來進行鎖定讀，那麼它的加鎖方式和上邊所說的SELECT ... FOR UPDATE語句一致。如果有其他二級索引列也被更新，那麼也會為對應的二級索引記錄進行加鎖，就不贅述了。不過還有一個有趣的情況，比方說：UPDATE hero SET country = '漢' WHERE name <= 'c曹操';我們前邊說的索引條件下推這個特性只適用於SELECT語句，也就是說UPDATE語句中無法使用，那麼這個語句就會為name值為'c曹操'和'l劉備'的二級索引記錄以及它們對應的聚簇索引進行加鎖，之後在判斷邊界條件時發現name值為'l劉備'的二級索引記錄不符合name <= 'c曹操'條件，再把該二級索引記錄和對應的聚簇索引記錄上的鎖釋放掉。這個過程如下圖所示：
使用DELETE ...來為記錄加鎖，比方說：DELETE FROM hero WHERE name >= 'c曹操';和DELETE FROM hero WHERE name <= 'c曹操';如果這兩個語句採用二級索引來進行鎖定讀，那麼它們的加鎖情況和更新帶有二級索引列的UPDATE語句一致，就不畫圖了。

全表掃描的情況

比方說：

SELECT * FROM hero WHERE country  = '魏' LOCK IN SHARE MODE;

由於country列上未建索引，所以只能採用全表掃描的方式來執行這條查詢語句，存儲引擎每讀取一條聚簇索引記錄，就會為這條記錄加鎖一個S型正常記錄鎖，然後返回給server層，如果server層判斷country = '魏'這個條件是否成立，如果成立則將其發送給客戶端，否則會釋放掉該記錄上的鎖，畫個圖就像這樣：

使用SELECT ... FOR UPDATE進行加鎖的情況與上邊類似，只不過加的是X型正經記錄鎖，就不贅述了。

對於UPDATE ...和DELETE ...的語句來說，在遍歷聚簇索引中的記錄，都會為該聚簇索引記錄加上X型正經記錄鎖，然後：

如果該聚簇索引記錄不滿足條件，直接把該記錄上的鎖釋放掉。
如果該聚簇索引記錄滿足條件，則會對相應的二級索引記錄加上X型正經記錄鎖（DELETE語句會對所有二級索引列加鎖，UPDATE語句只會為更新的二級索引列對應的二級索引記錄加鎖）。

來源：我們都是小青蛙(微信公眾號)