我們知道在MySQL中存在幻讀的情況，也就是一個事務在讀取某個范圍內的記錄時，發現了另一個事務在該范圍內新增了記錄（或者刪除了記錄），導致兩次讀取的記錄數量不一致，進而產生了“幻覺”一般的現象。也就是說，幻讀是指在多個事務同時讀取同一范圍內的記錄時所產生的矛盾現象。

MySQL為了解決幻讀一般采用快照讀和間隙鎖的方式，其中快照讀在之前的文章已經多次提及，本篇文章重點介紹間隙鎖。

間隙鎖意如其名，就是鎖定符合條件但是實際不存在的記錄，也就是一定的區間，防止其他事務在某個事務執行期間向該區間插入新的記錄。

為清楚梳理間隙鎖的作用，我們在本文中使用的示例表如下：

CREATE TABLE `t` (
  `id` int(11) NOT NULL,
  `c` int(11) DEFAULT NULL,
  `d` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `c` (`c`)
) ENGINE=InnoDB;


insert into t values(0,0,0),(5,5,5),(10,10,10),(15,15,15),(20,20,20),(25,25,25);

在示例表中執行如下語句：

begin;
select * from t where d=5 for update;
commit;

語句中的select for update就是為了在查詢時，對相關語句進行加鎖，避免其他用戶對該表進行插入、修改、刪除等操作，造成表的不一致。

d=5這一行對應主鍵為Id=5，執行select語句后改行會被加寫鎖，并在commit后釋放。但是由于d列沒有索引，所以會被全表掃描，這時候真實的加鎖邏輯為：

1. 全表掃描一般指主鍵索引樹掃描；
2. 對于會不會被加鎖：

RC級別下，只會在滿足條件的行加行鎖（直至事務commit/rollback才會釋放），不滿足條件的是先加鎖然后再直接釋放鎖；

RR級別下會加行鎖+全表間隙鎖(next-key lock是左開右閉，間隙鎖是左開右開）；

這里可以先記住這個邏輯，我們在下面的文章中會逐步開始介紹。

1 幻讀

1.1 幻讀是什么

注意，如下的結論都是假設存在，從而引入間隙鎖的概念。

如果沒有間隙鎖，只有行鎖，即：上面的語句只會鎖住：id=5的這一行數據，那么就會出現如下圖所示的場景：

圖片

for update在當前讀可以理解為：MySQL認為for update已經給當前的行加了寫鎖，因此沒有必要再進行快照讀，但是這樣會造成幻讀的問題。

如果沒有間隙鎖，就會出現如下的結果：

1. Q1 只返回 id=5 這一行；
2. 在 T2 時刻，session B 把 id=0 這一行的 d 值改成了 5，因此 T3 時刻 Q2 查出來的是 id=0 和 id=5 這兩行；
3. 在 T4 時刻，session C 又插入一行（1,1,5），因此 T5 時刻 Q3 查出來的是 id=0、id=1 和 id=5 的這三行。

Q3讀到id=1這一行的現象就是”幻讀“，即：在同一個事務中，兩次讀取到的數據不一致的情況可稱為幻讀和不可重復讀，其中幻讀針對insert導致的數據不一致，不可重復讀針對的delete/update導致的數據不一致。注意：這里的讀指的是當前讀，比如查詢語句中包含for update、in share mode，以及修改刪除語句都會開啟當前讀，否則就是快照讀。

• 快照讀：指的是在語句執行之前或者在事務開始的時候創建一個一致性視圖，后面的讀都是基于這個視圖，不會再去查詢最新的值；
• 當前讀：指的是更新之前必須先查詢當前的值，因此叫做當前讀，比如說：select for update或者select in share mode；

SELECT ... LOCK IN SHARE MODE走的是IS鎖(意向共享鎖)，即在符合條件的rows上都加了共享鎖，這樣的話，其他session可以讀取這些記錄，也可以繼續添加IS鎖，但是無法修改這些記錄直到你這個加鎖的session執行完成(否則直接鎖等待超時)。 

SELECT ... FOR UPDATE 走的是IX鎖(意向排它鎖)，即在符合條件的rows上都加了排它鎖，其他session也就無法在這些記錄上添加任何的S鎖或X鎖。如果不存在一致性非鎖定讀的話，那么其他session是無法讀取和修改這些記錄的，但是innodb有非鎖定讀(快照讀并不需要加鎖)，for update之后并不會阻塞其他session的快照讀取操作；

除了select ...lock in share mode和select ... for update這種顯示加鎖的查詢操作。 通過對比，發現for update的加鎖方式無非是比lock in share mode的方式多阻塞了select...lock in share mode的查詢方式，并不會阻塞快照讀

1.2 幻讀的問題

1.2.1 語義上的問題

sessionA在T1時刻聲明：把所有d=5的行鎖住，不允許其他的事務進行讀寫操作，但是sessionB和sessionC卻能夠隨意改變語義，新增或者通過修改了對應行的值。

圖片

1.2.2 數據一致性問題

鎖的設計不僅僅是數據庫內存數據狀態的一致性，還包括數據與日志在邏輯上的一致性。

圖片

如果沒有間隙鎖，上面的操作在binlog的記錄（binlog是在commit提交時進行記錄）就是：

/** session B提交語句 */
update t set d=5 where id=0; /*(0,0,5)*/
update t set c=5 where id=0; /*(0,5,5)*/
/** session C提交語句 */
insert into t values(1,1,5); /*(1,1,5)*/
update t set c=5 where id=1; /*(1,5,5)*/
/** session A提交語句 */
update t set d=100 where d=5;/*所有d=5的行，d改成100*/

使用該binlog恢復或者備份，三行中d=100，出現異常；

進一步，我們增加寫鎖。

圖片

在binlog的記錄為：

insert into t values(1,1,5); /*(1,1,5)*/
update t set c=5 where id=1; /*(1,5,5)*/


update t set d=100 where d=5;/*所有d=5的行，d改成100*/


update t set d=5 where id=0; /*(0,0,5)*/
update t set c=5 where id=0; /*(0,5,5)*/

2 幻讀的解決方法

2.1 next-key lock

因此上面的幻讀產生的原因就是說，行鎖只是鎖住了行，但是新插入記錄這個動作，要更新的是記錄之間的間隙。這也是InnoDB引入間隙鎖（Gap Lock）的原因。

圖片

間隙鎖的增加邏輯為：

1. 對主鍵或者唯一索引，如果當前讀時，where條件全部精準命中（=或者in），這種場景本身就不會產生幻讀，所以只會加行記錄鎖；
2. 沒有索引的列，當前讀操作時，會加全表的gap鎖；
3. 非唯一索引列，如果where條件部分命中（>/</like等）或者全部沒有命中，則會加附近Gap間的間隙鎖；例如，某表數據如下，非唯一索引2,6,9,9,11,15。如下語句要操作非唯一索引列9的數據，gap鎖將會鎖定的列是(6,11]，該區間內無法插入數據。
4. 跟間隙鎖存在沖突關系的，是“往這個間隙中插入/更新/刪除一條新的記錄”這個操作，間隙鎖之間不存在沖突關系。

間隙鎖和行鎖合稱 next-key lock，每個 next-key lock 是前開后閉區間。也就是說，我們的表 t 初始化以后，如果用 select * from t for update 要把整個表所有記錄鎖起來，就形成了 7 個 next-key lock，分別是 (-∞,0]、(0,5]、(5,10]、(10,15]、(15,20]、(20, 25]、(25, +supremum]。

2.2 next-key lock引入的問題

如下的示例，在索引唯一的時候，Insert ... on duplicate key update可用，但是如果有多個唯一鍵的時候，會有異常。

begin;
select * from t where id=N for update;


/*如果行不存在*/
insert into t values(N,N,N);
/*如果行存在*/
update t set d=N set id=N;


commit;

圖片

在并發情況下，即使沒有后續的update操作也會引入死鎖。

1. sessionA執行select ... for update語句，由于id=9不存在，因此會加上間隙鎖（5，10）；
2. sessionB執行select ... for update語句，由于id=9不存在，因此會加上間隙鎖（5，10），間隙鎖之間不存在沖突，因此可以執行成功；
3. session B 試圖插入一行 (9,9,9)，被 session A 的間隙鎖擋住了，只好進入等待；
4. session A 試圖插入一行 (9,9,9)，被 session B 的間隙鎖擋住了。

即：間隙鎖的引入，可能會導致同樣的語句鎖住更大的范圍，影響并發度。

2.3 讀提交+row模式的Binlog解決幻讀

間隙鎖在可重復讀隔離級別下才會出現，因此，如果把隔離級別設置為讀提交，就可以避免幻讀的問題。同時，為了解決可能出現的數據和日志不一致的問題，需要將Binlog的格式設置為row。

舉例： 刪除 statement記錄的是這個刪除的語句，例如： delete from t where age>10 and modified_time<='2020-03-04' limit 1 而row格式記錄的是實際受影響的數據是真實刪除行的主鍵id，例如： delete from t where id=3 and age=12 and modified_time='2020-03-05'

那為什么RR級別不需要修改binlog_format呢：

1. 間隙鎖是可重復讀級別下解決幻讀的，同時解決了binlog和數據可能存在的不一致問題，即：binlog日志的寫入順序錯誤問題；
2. 間隙鎖解決了binlog的問題，而不是Binlog解決了間隙鎖的問題；
3. 讀提交級別也有binlog執行順序錯誤的問題，也沒有間隙鎖，因此，需要將binlog_format修改為row模式，來解決binlog可能帶來的錯誤；
4. binlog的row模式比statement要記錄的更全面，每一行記錄改變都記錄下來，導致日志大，同時IO次數更多；

如果業務不需要可重復讀場景，考慮在讀提交下操作數據的鎖范圍更小（沒有間隙鎖），這個選擇是合理的。

2.4 讀提交和可重復讀

可重復讀的場景舉例，比如說：金融業務，財務需要統計過去一段時間內某些數據，需要反復根據某些條件查找，此時如果有新數據行插入，會導致統計時發生數據不一致的情況，此時需要使用可重復讀的隔離級別。

又比如說邏輯備份時，mysqldump備份線程會設置為可重復讀，這樣在導數據時就會啟動一個事務，確保拿到一致性視圖。由于MVCC的支持，過程中數據可正常更新。使用可重復讀，是為了保證備份的數據都是那一時刻的最新數據，然后通過binlog再做后續的恢復即可。

業務線程是讀提交，備份線程是可重復讀，同時存在兩種事務隔離級別，是否會沖突？

答案是不會，因為不管是RC還是RR，都是MVCC支持，唯一不同在于生成快照的時間點不同，也就是能夠看到的數據版本不同，所以并不影響。備份完成后，恢復為RC即可。

3 間隙鎖的加鎖規則

加鎖規則總結如下：

1. 原則1：加鎖的基本單位是next-key lock，是前開后閉；
2. 原則2：查找過程中訪問到的對象（索引）才會加鎖；
3. 優化1：索引上的等值查詢，如果可以匹配到對應數據，則給唯一索引加鎖，next-key lock退化為行鎖；如果匹配不到，按照原則2加鎖；
4. 優化2：索引上的等值查詢，向右遍歷時且最后一個值不滿足等值條件時，next-key lock退化為間隙鎖；
5. 一個bug：唯一索引的范圍查詢會訪問到不滿足條件的第一個值為止；【該bug已經在MySQL8.0.18版本開始修復，但是也有提出實際上只修復了主鍵上的問題，唯一索引沒有修復，需要驗證】

原則2也就解釋了：

• 為什么未命中索引的查詢要走全表掃描后導致全表加鎖的原因；
• 這里說的訪問到的對象，是從底層結構來看待，而不是數據表的一行。例如，普通索引和主鍵索引，如果訪問到的是普通索引，而且通過索引覆蓋并不需要回表查主鍵索引，那么主鍵索引上并不需要加任何的鎖，因為并沒有訪問主鍵索引樹上的對象。

本節還是使用章節組開始的表進行說明。

3.1 等值查詢間隙鎖

圖片

表中沒有id=7的記錄，因此：

• 根據原則1，加鎖單位為next-key lock，sessionA的加鎖范圍為：（5，10]；由于是根據id進行檢索，所以會鎖住主鍵索引對象；
• 根據優化2，sessionA為等值查詢，id=10不滿足查詢條件，退化為間隙鎖，因此加鎖的最終范圍為(5,10)；

因此，插入id=8的記錄會被鎖住，等待sessionA鎖釋放，sessionC修改id=10這一行可以正常執行。

3.2 非唯一索引等值鎖

圖片

這個例子說明的就是原則2中的對象。

注意：sessionA要給索引c=5加讀鎖，而且是索引c獲取主鍵，實際上就是覆蓋索引，不需要回表。

• 根據原則1，加鎖單位為next-key lock，給(0,5]加next-key lock；
• c為普通索引，且非唯一，需要向右遍歷到第一個不符合條件的值才能停止，即：直到c=10放棄。根據原則2，被訪問到的對象都需要加鎖，因此，(5,10]加next-key lock；
• 根據優化2，因為是等值判斷，最后一個值不滿足c=5，因此退化為間隙鎖（5,10）；
• 根據原則2，只有被訪問到的對象才會加鎖，這個查詢使用覆蓋索引，并不需要主鍵索引，所以主鍵索引沒有加任何索，sessionB的update語句可以執行完成；sessionC的語句被sessionA的間隙鎖鎖住。

同時需要注意的是：

• for update：系統認為接下來會更新數據，因此會將主鍵索引滿足條件的行加行鎖；
• in share mode：如果有覆蓋索引優化，沒有訪問到主鍵索引，那么主鍵索引不會加鎖；

因此，這里也就存在說，如果要使用lock in share mode給行家讀鎖防止數據行被更新，就必須繞過覆蓋索引的優化

3.3 主鍵索引范圍鎖

對于表t，如下兩條語句的加鎖范圍完全不同，語句1只會加行鎖，那么語句2呢?

mysql> select * from t where id=10 for update;
mysql> select * from t where id>=10 and id<11 for update;

圖片

• 開始執行時，要找到第一個id=10的行，由于是主鍵，所以是唯一索引，由next-key lock(5,10]退化為行鎖id=10；
• 范圍查找繼續往后查找，找到id=15停止，因此需要加next-key lock(10,15]，從8.0.18版本，間隙鎖退化為(10,15)；

此時sessionA鎖的范圍為id=10的行鎖和(10,15]的間隙鎖，因此sessionB和sessionC被阻塞；

可以使用語句“select * from performance_schema.data_locks”表獲取加鎖的數據。

3.4 非唯一索引范圍鎖

使用索引c進行范圍查詢：

圖片

由于c不是唯一索引，因此需要加(5,10]和(10,15]兩個next-key lock，因此后兩個會話的操作全部被阻塞。

3.5 唯一索引范圍鎖bug

注意，這個bug在8.0.18版本及之后的版本已經優化，不再存在。

圖片

session A 是一個范圍查詢，按照原則 1 的話，應該是索引 id 上只加 (10,15]這個 next-key lock，并且因為 id 是唯一鍵，所以循環判斷到 id=15 這一行就應該停止了。

但是實現上，InnoDB 會往前掃描到第一個不滿足條件的行為止，也就是 id=20。而且由于這是個范圍掃描，因此索引 id 上的 (15,20]這個 next-key lock 也會被鎖上。

3.6 非唯一索引上存在“等值”的問題

執行插入語句：

mysql> insert into t values(30,10,30);

圖片

雖然有兩個c=10的索引，但是主鍵不同，因此，c=10記錄存在間隙。

圖片

sessionA在遍歷的時候，先訪問到第一個c=10的記錄，根據原則1，加鎖為：(c=5,id=5)到(c=10,id=10)這個next-key lock，即c的索引為(5,10]。

然后sessionA向右查找，直至(c=15,id=15)，循環結束。根據優化2，等值查詢，退化為(c=10,id=10)到(c=15,id=15)的間隙鎖，即c的索引為(10,15)；

主鍵索引上，增加了行鎖id=10和id=30；

因此，索引c上的加鎖范圍為下圖藍色區域：

圖片

藍色兩邊是虛線，表示開區間，即 (c=5,id=5) 和 (c=15,id=15) 這兩行上都沒有鎖。

這里再次舉例： 如果session b插入(4,5,50)，不會被鎖，如果插入（6,5,50） 會被鎖住，因為二級索引的葉子節點存儲的是主鍵值，二級索引的葉子節點也是有序的，這樣6,5,50根據二級索引來排的話 是在5,5,10后面的 。

3.7 limit語句加鎖

圖片

sessionA的delete語句加了limit 2，表內只有兩條數據，刪除效果一樣，但是加鎖效果不同。

delete語句加了limit 2的限制，遍歷到(c=10,id=30)這一行之后，滿足條件的語句已經有兩條，循環結束。因此，索引c的加鎖范圍變成了（c=5,id=5) 到（c=10,id=30) 這個前開后閉區間。

圖片

因此說，在執行刪除的時候盡量加Limit，但是這里需要注意的是，刪除的行數不清楚，可能會帶來業務的bug。

3.8 一個死鎖的例子

圖片

• sessionA啟動事務后執行查詢語句加lock in share mode，在索引c加next-key lock(5,10]和間隙鎖(10,15)；
• sessionB的update語句也要在索引c上加next-key lock(5,10]，進入鎖等待；
• 然后sessionA要再插入(8,8,8)這一行，被sessionB的間隙鎖鎖住。由于出現了死鎖，InnoDB讓sessionB回滾；

session B 的“加 next-key lock(5,10] ”操作，實際上分成了兩步，先是加 (5,10) 的間隙鎖，加鎖成功；然后加 c=10 的行鎖，這時候才被鎖住的。也就是說，我們在分析加鎖規則的時候可以用 next-key lock 來分析。但是要知道，具體執行的時候，是要分成間隙鎖和行鎖兩段來執行的。

就算分成了兩步，為什么session B加(5,10)就能成功呢？session A不是加了(5, 10]的鎖嗎？ 前面應該也是提到過的，間隙鎖和間隙鎖之間并不沖突，間隙鎖和insert到這個間隙的語句才會沖突，因此session B加間隙鎖(5, 10)是可以成功的，但是如果往(5, 10)里面插入的話會被阻塞。 但是如果直接加next-key lock(5, 10]，那么肯定是會被阻塞的，因此這個例子確實說明，加鎖的步驟是分兩步的，先是間隙鎖，后是行鎖。而且只要理解了間隙鎖和行鎖之間沖突的原則是不一樣的，也就很容易理解這兩個鎖并不是一起加的了。