阿裡面試官:MySQL的InnoDB引擎是如何解決幻讀問題的？（下）

為何會資料不一緻？

這是我們假設“

select * from t where d=5 for update

這條語句隻給d=5這一行，也就是id=5的這一行加鎖”導緻的。

是以我們認為，上面的設定不合理，要改。

那怎麼改？

把掃描過程中碰到的行，也都加上寫鎖，再來看看執行效果。

• 假設掃描到的行都被加上了行鎖

• 

• 由于session A把所有行都加了寫鎖，是以session B在執行第一個update語句時就被鎖住。需要等到T6時session A送出後，session B才能繼續執行。

這樣對于id=0這行，在DB的最終結果還是 (0,5,5)。在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)*/      

可見按日志順序執行，id=0這行的最終結果也是(0,5,5)。是以，id=0這行問題解決了。

但id=1這一行，在DB裡結果是(1,5,5)，而根據binlog的執行結果是(1,5,100)，即幻讀依舊。

為何把所有記錄都上鎖了，還是阻止不了id=1這行的插入和更新？

在T3時，我們給所有行加鎖時，id=1這行還不存在，不存在也就加不上鎖。

即使把所有記錄都加鎖，還是阻止不了新插入的記錄，這也是為什麼“幻讀”會被單獨拿出來解決。

InnoDB解決幻讀

• 幻讀的原因

  行鎖隻能鎖行，但是新插入記錄這個動作，要更新的是記錄之間的“間隙”。是以，為了解決幻讀，InnoDB隻好引入間隙鎖(Gap Lock)，兩個值之間的空隙。比如文章開頭的表t，初始化插入了6個記錄，這就産生了7個間隙。

• 表t主鍵索引上的行鎖和間隙鎖

• 

• 當執行 select * from t where d=5 for update時，就不止是給資料庫中已有的6個記錄加上了行鎖，還同時加了7個間隙鎖。這樣就確定了無法再插入新記錄。

即在一行行掃描過程中，不僅給行加上了行鎖，還給行兩邊的空隙加上了間隙鎖。

資料行是可以加上鎖的實體，資料行之間的間隙，也是可以加上鎖的實體。

兩種行鎖間的沖突關系

• 跟行鎖有沖突關系的是“另外一個行鎖”。

  但間隙鎖不一樣，跟間隙鎖存在沖突關系的，是“往這個間隙中插入一個記錄”這個操作。

  間隙鎖之間不存在沖突關系。

  舉個例子：

• 間隙鎖之間不互鎖

• 

• session B不會被堵住。因為表t裡并沒c=7記錄，是以session A加的間隙鎖(5,10)。而session B也是在這個間隙加的間隙鎖。它們有共同的目标，即：保護這個間隙，不允許插入值。但它們之間不沖突。

間隙鎖和行鎖合稱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]。

本文沒有特别說明，就把間隙鎖記為開區間，把next-key lock記為前開後閉區間。

• supremum是啥玩意？

  因為

  +∞

  是開區間，代碼實作上，InnoDB給每個索引加了不存在的最大值supremum，就符合後閉區間了。

間隙鎖和next-key lock解決了幻讀，但也帶來“困擾”。

案例

需求

任意鎖住一行，若：

• 該行不存在，就插入
• 存在，就更新資料

實作

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;      

就這？

insert … on duplicate key update      

不就能解決。但在有多個唯一鍵時，該方法無法滿足該需求。

邏輯分析

該邏輯一旦有并發，就可能死鎖。可這個邏輯每次操作前用

for update

鎖了，已經是最嚴格模式了，怎麼還有死鎖？

模拟

兩個session并發，假設N=9。

• 間隙鎖導緻的死鎖

• 

• 不需要用到後面的update語句，就已經死鎖。

session A 執行select … for update，由于id=9這行不存在，是以會加間隙鎖(5,10)

session B 執行select … for update，同樣加間隙鎖(5,10)，間隙鎖之間不沖突，是以可執行成功

session B 試圖插入(9,9,9)，被session A的間隙鎖擋住，進入等待

session A試圖插入(9,9,9)，被session B的間隙鎖擋住

session互相等待形成死鎖。當然，InnoDB的死鎖檢測馬上就發現了這對死鎖關系，讓session A的insert語句報錯傳回了。

是以間隙鎖的引入可能導緻同樣語句鎖住更大範圍。

為解決幻讀，引入這麼多内容，有更簡單方法嗎？

沒有特别說明，本文分析都是可重複讀，間隙鎖在可重複讀隔離級别下才生效。

是以，你如果把隔離級别設定為讀送出，就沒間隙鎖。

但同時，要解決可能出現的資料和日志不一緻問題，要把binlog格式設為row。這也是很多公司使用的配置。

如果讀送出隔離級别夠用，即業務無需保證可重複讀，考慮到讀送出下操作資料的鎖範圍更小（無間隙鎖），這個選擇就是合适的。

如果大家都用讀送出，可是邏輯備份時，mysqldump為什麼要把備份線程設定成可重複讀？

然後，在備份期間，備份線程用的是可重複讀，而業務線程用的是讀送出。同時存在兩種事務隔離級别，會不會有問題？

進一步地，這兩個不同的隔離級别現象有什麼不一樣的，關于我們的業務，“用讀送出就夠了”這個結論是怎麼得到的？

如果業務開發和運維團隊這些問題都沒有弄清楚，那麼“沒問題”這個結論，本身就是有問題的。

總結

即使給所有行加上行鎖，仍無法解決幻讀，是以引入間隙鎖。

行鎖确實比較直覺，判斷規則也相對簡單，間隙鎖的引入會影響系統的并發度，也增加了鎖分析的複雜度，但也有章可循。