MySQL 中關(guān)于gap lock（間隙鎖） 、 next-key lock（間隙鎖+行鎖） 的一個(gè)問(wèn)題
在學(xué)習(xí) MySQL 的過(guò)程中遇到的一個(gè)關(guān)于鎖的問(wèn)題六敬，包含多個(gè) MySQL 相關(guān)的知識(shí)碘赖；相關(guān)資料在文章末尾
問(wè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 會(huì)出現(xiàn)上述情況，為什么外构？
加鎖過(guò)程
- 在索引 b 上的等值查詢普泡，給索引 b 加上了 next-key lock (4, 6]；索引向右遍歷审编，且最后一個(gè)值不滿足條件時(shí)退化為間隙鎖撼班；所以會(huì)再加上間隙鎖 (6,8)；所以索引 b 上的 next-key lock 的范圍是(b=4,id=3)到(b=6,id=5)這個(gè)左開(kāi)右閉區(qū)間和(b=6,id=5)到(b=8,id=7)這個(gè)開(kāi)區(qū)間垒酬。（讀起來(lái)有點(diǎn)繞口砰嘁，看不懂的可以看下文中的圖）
- for update 會(huì)給 b = 6 這一行加上行鎖；因此 (b=6,id=5) 這一行上有行鎖
- 這么看來(lái)上述語(yǔ)句都不在鎖的范圍內(nèi)勘究，為什么會(huì)被鎖
這個(gè)問(wèn)題其實(shí)是因?yàn)闆](méi)有理解索引的結(jié)構(gòu)矮湘，所以認(rèn)為所有值都不應(yīng)該被鎖
- 如圖所示，此時(shí)索引 b 上的鎖：
- 圖中綠色部分即為被鎖范圍口糕；索引會(huì)根據(jù) b 和 id 的值進(jìn)行排序缅阳，插入不同的值，鎖的范圍是不一樣的景描；分別插入 (b=4,id=2) 和(b=4,id=4)券时，插入的位置如圖所示：
- 因?yàn)樗饕怯行虻模藭r(shí)伏伯，由于記錄(b=4,id=3)的存在橘洞，(b=4,id=2)不在鎖的范圍內(nèi)，可以插入说搅，但(b=4,id=4)在鎖的范圍內(nèi)炸枣，所以插入時(shí)需要等待鎖釋放，被 blocked
- 對(duì)于其他(id,b)的值(2,8),(4,8),(8,4),(8,8)也是同樣的道理；因此适肠，得出以下結(jié)論：
- id <= 2 時(shí)霍衫，b 索引鎖范圍為 (4,8]
- 2 < id < 8 時(shí)，b 索引鎖范圍為 [4,8]
- a >= 8 時(shí)侯养，b 索引鎖范圍為 [4,8)
- 但是敦跌，實(shí)踐發(fā)現(xiàn)，當(dāng) id=0 時(shí)逛揩，被鎖的范圍為 [4,8)柠傍，這和我們得到的第一個(gè)結(jié)論(4,8]不一樣；此時(shí)分析得到的示意圖為：
- 在 session A 中嘗試插入 (b=4, id=0)并查詢結(jié)果：
INSERT INTO z VALUES (0, 4);
SELECT * FROM z;
- 此時(shí)辩稽，發(fā)現(xiàn)表中并沒(méi)有發(fā)現(xiàn) (b=4, id=0)這條記錄惧笛，但是多了 (b=4,id=10)這條；所以插入 (b=4, id=0)的時(shí)候真正插入的是 (b=4,id=10)這個(gè)值逞泄；這是因?yàn)槲覀冊(cè)趧?chuàng)建表的時(shí)候指定主鍵 id 的值
AUTO INCREMENT患整，當(dāng)插入的主鍵值為0的時(shí)候，會(huì)被替換為AUTO_INCREMENT的值喷众，即10
- 對(duì)此各谚，MySQL 官方文檔中的解釋是：在非
NO_AUTO_VALUE_ON_ZERO模式下，給自增的列賦值為 0到千，都會(huì)被替換為自增序列的下一個(gè)值昌渤；當(dāng)該自增列值指定 NOT NULL 時(shí)賦值 NULL，也會(huì)被替換父阻；當(dāng)插入其他值時(shí)愈涩，自增序列的值會(huì)被替換為當(dāng)前列中最大值的下一個(gè)值；參考 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) 會(huì)得到這條記錄
問(wèn)題一的部分來(lái)自MySQL 中關(guān)于gap lock / next-key lock 的一個(gè)問(wèn)題
問(wèn)題2描述
問(wèn)題二部分來(lái)自什么是間隙鎖？到底鎖了什么斟览？
當(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會(huì)出現(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ī)則：
加鎖過(guò)程
- 在索引 b 上的等值查詢，給索引 b 加上了 next-key lock (4, 6]躁绸；索引向右遍歷裕循，且最后一個(gè)值不滿足條件時(shí)退化為間隙鎖臣嚣；所以會(huì)再加上間隙鎖 (6,8)；所以索引 b 上的 next-key lock 的范圍是(b=4,id=3)到(b=6,id=5)這個(gè)左開(kāi)右閉區(qū)間和(b=6,id=5)到(b=8,id=7)這個(gè)開(kāi)區(qū)間剥哑。（讀起來(lái)有點(diǎn)繞口硅则，看不懂的可以看下文中的圖）
- for update 會(huì)給 b = 6 這一行加上行鎖；因此 (b=6,id=5) 這一行上有行鎖
鎖住的部分應(yīng)該如下圖所示
- 執(zhí)行
UPDATE z SET b = 7 WHERE id = 7;株婴，會(huì)刪掉(b=8,id=7)的索引且新增(b=7,id=7)的索引怎虫，新增部分根據(jù)索引有序的規(guī)則，將會(huì)落在鎖住的部分區(qū)間困介，所以會(huì)被阻塞大审。
- 執(zhí)行
UPDATE z SET id = 6 WHERE id = 8;，會(huì)將b索引上（b=8,id=8）葉子節(jié)點(diǎn)刪掉逻翁，并增加(b=8,id=6)的葉子節(jié)點(diǎn)饥努〖裼悖可以看到(b=8,id=6)的葉子節(jié)點(diǎn)也落入鎖住的部分區(qū)間八回，所以會(huì)被阻塞住。
本文是作者根據(jù)日常業(yè)務(wù)場(chǎng)景驾诈，寫(xiě)出的一些解決問(wèn)題或?qū)嵤┫敕ǖ臍v程缠诅。如有錯(cuò)誤的地方，還請(qǐng)指出乍迄，相互學(xué)習(xí)管引，共同進(jìn)步。
