MySQL 中關(guān)于gap lock（間隙鎖） 、 next-key lock（間隙鎖+行鎖） 的一個問題
在學(xué)習(xí) MySQL 的過程中遇到的一個關(guān)于鎖的問題，包含多個 MySQL 相關(guān)的知識；相關(guān)資料在文章末尾
問題1描述
- 表初始化
CREATE TABLE z (
id INT PRIMARY KEY AUTO_INCREMENT,
b INT,
KEY b(b)
) ENGINE = InnoDB DEFAULT CHARSET = utf8;
INSERT INTO z
(id, b)
VALUES
(1, 2),
(3, 4),
(5, 6),
(7, 8),
(9, 10);
當(dāng)前表中的數(shù)據(jù)為：
- session A
BEGIN;
SELECT * FROM z WHERE b = 6 FOR UPDATE;
- session B
INSERT INTO z VALUES (2, 4);/*success*/
INSERT INTO z VALUES (2, 8);/*blocked*/
INSERT INTO z VALUES (4, 4);/*blocked*/
INSERT INTO z VALUES (4, 8);/*blocked*/
INSERT INTO z VALUES (8, 4);/*blocked*/
INSERT INTO z VALUES (8, 8);/*success*/
INSERT INTO z VALUES (0, 4);/*blocked*/
INSERT INTO z VALUES (-1, 4);/*success*/
分別執(zhí)行 session B中的insert 會出現(xiàn)上述情況，為什么？
加鎖過程
- 在索引 b 上的等值查詢，給索引 b 加上了 next-key lock (4, 6]；索引向右遍歷，且最后一個值不滿足條件時退化為間隙鎖；所以會再加上間隙鎖 (6,8)；所以索引 b 上的 next-key lock 的范圍是(b=4,id=3)到(b=6,id=5)這個左開右閉區(qū)間和(b=6,id=5)到(b=8,id=7)這個開區(qū)間。（讀起來有點繞口，看不懂的可以看下文中的圖）
- for update 會給 b = 6 這一行加上行鎖；因此 (b=6,id=5) 這一行上有行鎖
- 這么看來上述語句都不在鎖的范圍內(nèi)，為什么會被鎖
這個問題其實是因為沒有理解索引的結(jié)構(gòu)，所以認(rèn)為所有值都不應(yīng)該被鎖
- 如圖所示，此時索引 b 上的鎖：
- 圖中綠色部分即為被鎖范圍；索引會根據(jù) b 和 id 的值進(jìn)行排序，插入不同的值，鎖的范圍是不一樣的；分別插入 (b=4,id=2) 和(b=4,id=4)，插入的位置如圖所示：
- 因為索引是有序的，此時，由于記錄(b=4,id=3)的存在，(b=4,id=2)不在鎖的范圍內(nèi)，可以插入，但(b=4,id=4)在鎖的范圍內(nèi)，所以插入時需要等待鎖釋放，被 blocked
- 對于其他(id,b)的值(2,8),(4,8),(8,4),(8,8)也是同樣的道理；因此，得出以下結(jié)論：
- id <= 2 時，b 索引鎖范圍為 (4,8]
- 2 < id < 8 時，b 索引鎖范圍為 [4,8]
- a >= 8 時，b 索引鎖范圍為 [4,8)
- 但是，實踐發(fā)現(xiàn)，當(dāng) id=0 時，被鎖的范圍為 [4,8)，這和我們得到的第一個結(jié)論(4,8]不一樣；此時分析得到的示意圖為：
- 在 session A 中嘗試插入 (b=4, id=0)并查詢結(jié)果：
INSERT INTO z VALUES (0, 4);
SELECT * FROM z;
- 此時，發(fā)現(xiàn)表中并沒有發(fā)現(xiàn) (b=4, id=0)這條記錄，但是多了 (b=4,id=10)這條；所以插入 (b=4, id=0)的時候真正插入的是 (b=4,id=10)這個值；這是因為我們在創(chuàng)建表的時候指定主鍵 id 的值
AUTO INCREMENT，當(dāng)插入的主鍵值為0的時候，會被替換為AUTO_INCREMENT的值，即10
- 對此，MySQL 官方文檔中的解釋是：在非
NO_AUTO_VALUE_ON_ZERO模式下，給自增的列賦值為 0，都會被替換為自增序列的下一個值；當(dāng)該自增列值指定 NOT NULL 時賦值 NULL，也會被替換；當(dāng)插入其他值時，自增序列的值會被替換為當(dāng)前列中最大值的下一個值；參考 MySQL 8.0 Reference Manual 文檔，Tutorial 的 Examples of Common Queries , 3.6.9 Using AUTO_INCREMENT
No value was specified for the AUTO_INCREMENT column, so MySQL assigned sequence numbers automatically. You can also explicitly assign 0 to the column to generate sequence numbers, unless the NO_AUTO_VALUE_ON_ZERO SQL mode is enabled.
If the column is declared NOT NULL, it is also possible to assign NULL to the column to generate sequence numbers.
When you insert any other value into an AUTO_INCREMENT column, the column is set to that value and the sequence is reset so that the next automatically generated value follows sequentially from the largest column value.
- 如果將主鍵修改為不自增，插入 (b=4, id=0) 會得到這條記錄
問題一的部分來自MySQL 中關(guān)于gap lock / next-key lock 的一個問題
問題2描述
問題二部分來自什么是間隙鎖？到底鎖了什么？
當(dāng)前表中的數(shù)據(jù)為：
- session A
BEGIN;
SELECT * FROM z WHERE b = 6 FOR UPDATE;
- session B
UPDATE z SET b = 7 WHERE id = 7;/*blocked*/
UPDATE z SET id = 6 WHERE id = 8;/*blocked*/
分別執(zhí)行 session B中的UPDATE會出現(xiàn)上述情況，為什么？
建表后，b字段上的2,4,6,8是以B+tree的數(shù)據(jù)結(jié)構(gòu)出現(xiàn)，為了方便理解，這里我們不強(qiáng)調(diào)B+tree的結(jié)構(gòu)，強(qiáng)調(diào)有序。索引b上的數(shù)據(jù)結(jié)圖如下所示：
- 根據(jù)上文解釋的上鎖規(guī)則：
加鎖過程
- 在索引 b 上的等值查詢，給索引 b 加上了 next-key lock (4, 6]；索引向右遍歷，且最后一個值不滿足條件時退化為間隙鎖；所以會再加上間隙鎖 (6,8)；所以索引 b 上的 next-key lock 的范圍是(b=4,id=3)到(b=6,id=5)這個左開右閉區(qū)間和(b=6,id=5)到(b=8,id=7)這個開區(qū)間。（讀起來有點繞口，看不懂的可以看下文中的圖）
- for update 會給 b = 6 這一行加上行鎖；因此 (b=6,id=5) 這一行上有行鎖
鎖住的部分應(yīng)該如下圖所示
- 執(zhí)行
UPDATE z SET b = 7 WHERE id = 7;，會刪掉(b=8,id=7)的索引且新增(b=7,id=7)的索引，新增部分根據(jù)索引有序的規(guī)則，將會落在鎖住的部分區(qū)間，所以會被阻塞。
- 執(zhí)行
UPDATE z SET id = 6 WHERE id = 8;，會將b索引上（b=8,id=8）葉子節(jié)點刪掉，并增加(b=8,id=6)的葉子節(jié)點?？梢钥吹?b=8,id=6)的葉子節(jié)點也落入鎖住的部分區(qū)間，所以會被阻塞住。
本文是作者根據(jù)日常業(yè)務(wù)場景，寫出的一些解決問題或?qū)嵤┫敕ǖ臍v程。如有錯誤的地方，還請指出，相互學(xué)習(xí)，共同進(jìn)步。
