我們知道在MySQL中存在幻讀的情況，也就是一個事務(wù)在讀取某個范圍內(nèi)的記錄時，發(fā)現(xiàn)了另一個事務(wù)在該范圍內(nèi)新增了記錄（或者刪除了記錄），導(dǎo)致兩次讀取的記錄數(shù)量不一致，進(jìn)而產(chǎn)生了“幻覺”一般的現(xiàn)象。也就是說，幻讀是指在多個事務(wù)同時讀取同一范圍內(nèi)的記錄時所產(chǎn)生的矛盾現(xiàn)象。

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

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

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

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);

在示例表中執(zhí)行如下語句：

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

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

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

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

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

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

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

1 幻讀

1.1 幻讀是什么

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

如果沒有間隙鎖，只有行鎖，即：上面的語句只會鎖?。篿d=5的這一行數(shù)據(jù)，那么就會出現(xiàn)如下圖所示的場景：

圖片

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

如果沒有間隙鎖，就會出現(xiàn)如下的結(jié)果：

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這一行的現(xiàn)象就是”幻讀“，即：在同一個事務(wù)中，兩次讀取到的數(shù)據(jù)不一致的情況可稱為幻讀和不可重復(fù)讀，其中幻讀針對insert導(dǎo)致的數(shù)據(jù)不一致，不可重復(fù)讀針對的delete/update導(dǎo)致的數(shù)據(jù)不一致。注意：這里的讀指的是當(dāng)前讀，比如查詢語句中包含for update、in share mode，以及修改刪除語句都會開啟當(dāng)前讀，否則就是快照讀。

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

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

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

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

1.2 幻讀的問題

1.2.1 語義上的問題

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

圖片

1.2.2 數(shù)據(jù)一致性問題

鎖的設(shè)計(jì)不僅僅是數(shù)據(jù)庫內(nèi)存數(shù)據(jù)狀態(tài)的一致性，還包括數(shù)據(jù)與日志在邏輯上的一致性。

圖片

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

/** 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恢復(fù)或者備份，三行中d=100，出現(xiàn)異常；

進(jìn)一步，我們增加寫鎖。

圖片

在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

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

圖片

間隙鎖的增加邏輯為：

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

間隙鎖和行鎖合稱 next-key lock，每個 next-key lock 是前開后閉區(qū)間。也就是說，我們的表 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;

圖片

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

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

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

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

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

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

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

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

如果業(yè)務(wù)不需要可重復(fù)讀場景，考慮在讀提交下操作數(shù)據(jù)的鎖范圍更?。]有間隙鎖），這個選擇是合理的。

2.4 讀提交和可重復(fù)讀

可重復(fù)讀的場景舉例，比如說：金融業(yè)務(wù)，財(cái)務(wù)需要統(tǒng)計(jì)過去一段時間內(nèi)某些數(shù)據(jù)，需要反復(fù)根據(jù)某些條件查找，此時如果有新數(shù)據(jù)行插入，會導(dǎo)致統(tǒng)計(jì)時發(fā)生數(shù)據(jù)不一致的情況，此時需要使用可重復(fù)讀的隔離級別。

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

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

答案是不會，因?yàn)椴还苁荝C還是RR，都是MVCC支持，唯一不同在于生成快照的時間點(diǎn)不同，也就是能夠看到的數(shù)據(jù)版本不同，所以并不影響。備份完成后，恢復(fù)為RC即可。

3 間隙鎖的加鎖規(guī)則

加鎖規(guī)則總結(jié)如下：

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

原則2也就解釋了：

• 為什么未命中索引的查詢要走全表掃描后導(dǎo)致全表加鎖的原因；
• 這里說的訪問到的對象，是從底層結(jié)構(gòu)來看待，而不是數(shù)據(jù)表的一行。例如，普通索引和主鍵索引，如果訪問到的是普通索引，而且通過索引覆蓋并不需要回表查主鍵索引，那么主鍵索引上并不需要加任何的鎖，因?yàn)椴]有訪問主鍵索引樹上的對象。

本節(jié)還是使用章節(jié)組開始的表進(jìn)行說明。

3.1 等值查詢間隙鎖

圖片

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

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

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

3.2 非唯一索引等值鎖

圖片

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

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

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

同時需要注意的是：

• for update：系統(tǒng)認(rèn)為接下來會更新數(shù)據(jù)，因此會將主鍵索引滿足條件的行加行鎖；
• in share mode：如果有覆蓋索引優(yōu)化，沒有訪問到主鍵索引，那么主鍵索引不會加鎖；

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

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;

圖片

• 開始執(zhí)行時，要找到第一個id=10的行，由于是主鍵，所以是唯一索引，由next-key lock(5,10]退化為行鎖id=10；
• 范圍查找繼續(xù)往后查找，找到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”表獲取加鎖的數(shù)據(jù)。

3.4 非唯一索引范圍鎖

使用索引c進(jìn)行范圍查詢：

圖片

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

3.5 唯一索引范圍鎖bug

注意，這個bug在8.0.18版本及之后的版本已經(jīng)優(yōu)化，不再存在。

圖片

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

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

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

執(zhí)行插入語句：

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

圖片

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

圖片

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

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

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

因此，索引c上的加鎖范圍為下圖藍(lán)色區(qū)域：

圖片

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

這里再次舉例： 如果session b插入(4,5,50)，不會被鎖，如果插入（6,5,50） 會被鎖住，因?yàn)槎壦饕娜~子節(jié)點(diǎn)存儲的是主鍵值，二級索引的葉子節(jié)點(diǎn)也是有序的，這樣6,5,50根據(jù)二級索引來排的話 是在5,5,10后面的 。

3.7 limit語句加鎖

圖片

sessionA的delete語句加了limit 2，表內(nèi)只有兩條數(shù)據(jù)，刪除效果一樣，但是加鎖效果不同。

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

圖片

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

3.8 一個死鎖的例子

圖片

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

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

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