InnoDB Next-Key Lock淺析

來自專欄 有財家的雜談

什麼是幻讀

同一事務下，連續執行兩次同樣的SQL可能得到不同的結果，第二次的SQL可能返回了不存在的行

官方給出了一個例子：

child表只有id為90和102的記錄，執行這樣一條查詢語句：

select * from child where id > 100 for update;

如果此時沒有鎖鎖定90到102這個範圍的話，另一個線程可能會成功插入一條id為101的數據，那麼再次執行這個查詢，就會出現一條id為101的記錄，這就是幻讀問題。

InnoDB採用Next-Key Lock解決幻讀問題。

舉例說明

以下所有的操作都是在Read Repeatable級別下進行測試，測試版本是MySQL官方5.7.21版本

新建一張表：

CREATE TABLE `test` ( `id` int(11) primary key auto_increment, `xid` int, KEY `xid` (`xid`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8; insert into test(xid) values (1), (3), (5), (8), (11);

注意，這裡xid上是有索引的，因為該演算法總是會去鎖住索引記錄。

現在，該索引可能被鎖住的範圍如下：

(-∞, 1], (1, 3], (3, 5], (5, 8], (8, 11], (11, +∞)

根據下面的方式開啟事務執行SQL：

Session A執行後會鎖住的範圍：

(5, 8], (8, 11]

除了鎖住8所在的範圍，還會鎖住下一個範圍，所謂Next-Key。

這樣，Session B執行到第六步會阻塞，跳過第六步不執行，第七步也會阻塞，但是並不阻塞第八步，第九步也不阻塞。

上面的結果似乎並不符合預期，因為11這個值看起來就是在(8, 11]區間里，而5這個值並不在(5, 8]這個區間里。

那麼先引進一張圖：

輔助索引中黃色部分是被record lock鎖住的行，除此之外還有兩個Gap Lock，鎖住了我上面說的範圍。

先說為什麼鎖住了5的插入，觀察主鍵索引，主鍵索引是自增的，因此在id=4這條記錄之前，是不允許插入一條xid=5的記錄的。

上面說的並不是很能讓人信服,什麼時候看了源碼再說吧，不過記住這個結論能夠推斷出正確的鎖定範圍的。

不鎖定xid=11的寫入還是可以用id是自增的解釋，B+樹是有序的，並不會阻塞後續的插入。

因此在判斷Next-Key Lock的鎖定範圍的時候，首先要判斷出區間，然後分析，判斷區間邊緣值是否會被鎖定

如果id是唯一索引，那麼此時Session B的所有步驟中只有第四步和第六步會報唯一鍵約束錯誤，其他步驟全都成功。這是因為唯一索引上不採用Next-Key Lock，而是只鎖一行。

如果session A執行這個SQL：

select * from test where xid = 1 for update;

這個時候的鎖定範圍是(-∞, 1], (1, 3]，但是不鎖定3

如果Session B執行下面的SQL都會阻塞：

insert into test(xid) values (-100); insert into test(xid) values (0); insert into test(xid) values (1); insert into test(xid) values (2);

這些都在上面說的區間內，也印證了我的看法，但是這條SQL開始就不會被阻塞了：

insert into test(xid) values (3);

下面來測試一下到正無窮大的是否滿足我的理解：

session A執行這個SQL：

select * from test where xid = 11 for update;

推斷鎖定的範圍是：(8, 11], (11, +∞)，鎖定8和上述區間內的數據插入

現在session B執行這些SQL應該都會被阻塞：

insert into test(xid) values (8); insert into test(xid) values (9); insert into test(xid) values (10); insert into test(xid) values (11); insert into test(xid) values (99999);

但是session B執行這個SQL就不會阻塞：

insert into test(xid) values (7);

經過實際測試結果符合我的預期。

再研究一個主鍵不是自增的例子，這個例子來自何登成的博客，下面有鏈接，我稍微改動了一下。

首先建表：

create table test ( uname varchar(10) primary key, xid int, key (xid) );

插入數據如下：

insert into test(uname, xid) values (a, 15),(c,10), (e,6),(g, 10),(m,11),(z,2);

下面Session A試圖鎖住xid=10：

begin; select * from test where xid=10 for update;

下面是主鍵索引和輔助索引的關係示意圖：

鎖住的範圍推斷應該是((6,e),(10,c)],((10,c),(10,g)]

因為不是自增主鍵了，所以情況會稍微麻煩一點，但是有一點原則要把握好就是B+樹的連續性不能被破壞，那麼可以判斷下面的SQL一定被阻塞：

-- 因為(10,d)的組合會破壞B+樹的連續性，可以自己畫圖 insert into test(uname, xid) values (d, 10); -- 因為(10,f)組合也會破壞B+樹連續性 insert into test(uname, xid) values (f, 10); -- 同理也會阻塞 insert into test(uname, xid) values (d, 7); insert into test(uname, xid) values (d, 8); insert into test(uname, xid) values (d, 9);

現在來分析一下本文開頭時MySQL官方給的例子，需要注意的是如果沒有顯式的指定主鍵InnoDB會選擇一個rowid作為主鍵，這個rowid是單調遞增的。

select * from child where id > 100 for update;

這個SQL在表中只有90和102兩個數據的情況下Next-Key Lock鎖定的範圍是：

(90, 102], (102, +∞)

因此在這個區間的保護下，官方例子中插入一個id=101的記錄是會被阻塞的，這也就完成了幻讀保護，同樣插入90也是不可以的，插入比102大的數據也不可以。

而實際的測試也印證了我的推斷。

參考資料

何登成的主頁

Innodb鎖機制：Next-Key Lock 淺談

14.5.1 InnoDB Locking