看來，MySQL next-key lock 的 bug 并沒有被修復(fù)！

前言

在上一篇文章《MySQL next-key lock 加鎖范圍是什么？》中已經(jīng)介紹了主鍵索引的加鎖范圍，現(xiàn)在來回顧一下：

1. 加鎖時(shí)，會(huì)先給表添加意向鎖，IX 或 IS；
2. 加鎖是如果是多個(gè)范圍，是分開加了多個(gè)鎖，每個(gè)范圍都有鎖；（這個(gè)可以實(shí)踐下 id < 20 的情況）
3. 主鍵等值查詢，數(shù)據(jù)存在時(shí)，會(huì)對(duì)該主鍵索引的值加行鎖 X,REC_NOT_GAP；
4. 主鍵等值查詢，數(shù)據(jù)不存在時(shí)，會(huì)對(duì)查詢條件主鍵值所在的間隙添加間隙鎖 X,GAP；
5. 主鍵等值查詢，范圍查詢時(shí)情況則比較復(fù)雜：
1. 8.0.17 版本是前開后閉，而 8.0.18 版本及以后，修改為了前開后開區(qū)間；
2. 臨界 <= 查詢時(shí)，8.0.17 會(huì)鎖住下一個(gè) next-key 的前開后閉區(qū)間，而 8.0.18 及以后版本，修復(fù)了這個(gè) bug。

這篇文章會(huì)對(duì)非主鍵唯一索引進(jìn)行操作實(shí)踐。

1

數(shù)據(jù)庫表數(shù)據(jù)

CREATE TABLE `t` (
  `id` int NOT NULL COMMENT '主鍵',
  `a` int DEFAULT NULL COMMENT '唯一索引',
  `c` int DEFAULT NULL COMMENT '普通索引',
  `d` int DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uniq_a` (`a`),
   KEY `idx_c` (`c`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci

數(shù)據(jù)庫數(shù)據(jù)如下：

數(shù)據(jù)庫的字段 a 是唯一索引。

2

非主鍵唯一索引

非主鍵唯一索引等值查詢 —— 數(shù)據(jù)存在

mysql> begin; select * from t where a = 110 for update;

分析一下這條 SQL：

1. select * 查詢條件肯定會(huì)回表；
2. 唯一索引，所以定位到數(shù)據(jù)后不需要繼續(xù)查詢；
3. 猜測(cè)是鎖住唯一索引及對(duì)應(yīng)的主鍵索引。

查看 data_locks

1. 表鎖 IX；
2. 索引 uniq_a 上面加了 X,REC_NOT_GAP 行鎖，其中 110, 10 表示是 a = 110 這行數(shù)據(jù)，后面的 10 是這行數(shù)據(jù)對(duì)應(yīng)的主鍵；
3. 主鍵 id = 10 上添加了 X,REC_NOT_GAP 行鎖。

一切和分析的一樣。

如果把 for update 換成 for share，其實(shí)也是相同，在主鍵和唯一索引上都加了鎖。

這里執(zhí)行的 SQL 都是 select *，如果替換為 select id 呢？

mysql> begin; select id from t where a = 110 for update;

分析一下這條 SQL：

1. select id 查詢，滿足索引覆蓋，不會(huì)回表；
2. 唯一索引，所以定位到數(shù)據(jù)后不需要繼續(xù)查詢；
3. 那這里是鎖主鍵索引還是兩個(gè)都鎖？

所以看出并無什么區(qū)別。

把 for update 換成 for share，這時(shí)候區(qū)別來了：

只有兩條鎖記錄：表意向鎖和 uniq_a 索引的 S,REC_NOT_GAP 鎖。

很明顯，for share 覆蓋索引時(shí)，只是對(duì)自己的索引加鎖。

update t set c = 2101 where id = 10;

這時(shí)候使用主鍵更新 c 是否能更新？那下面兩個(gè) SQL 呢？

update t set a = 1101 where id = 10;

update t set c = 2101 where a = 110;

執(zhí)行結(jié)果很顯然，第一個(gè)可以執(zhí)行，而后兩個(gè)是會(huì)阻塞的。

所以，非主鍵唯一索引等值查詢，數(shù)據(jù)存在，for update 是會(huì)在主鍵加鎖的，而 for share 只有在走覆蓋索引的情況下，會(huì)僅在自己索引上加鎖。

非主鍵唯一索引等值查詢 —— 數(shù)據(jù)不存在

mysql> begin; select * from t where a = 111 for update;

分析這一條 SQL：

1. 首先加了 for update，肯定會(huì)在 索引 uniq_a 和 主鍵索引上都加上鎖；
2. 字段 a 具有唯一性，但是數(shù)據(jù) a = 111 不存在，會(huì)一直查，查到 115 區(qū)間；
3. 所以會(huì)加索引 uniq_a 和 主鍵索引的間隙鎖。（并不對(duì) ）

事實(shí)證明，分析結(jié)果不正確。

并且我執(zhí)行 update t set c = 2101 where id = 15; 也過了。

所以是不是可以理解為，非主鍵索引等值查詢，數(shù)據(jù)不存在，相當(dāng)于一個(gè)范圍查詢，僅僅會(huì)在非主鍵索引上加鎖，加的還是間隙鎖，前開后開區(qū)間；

如果此時(shí)走索引覆蓋呢？其實(shí)結(jié)果也是相同的。

非主鍵唯一索引范圍查詢

mysql> begin; select * from t where a >= 110 and a < 115 for update;

分析 SQL

1. a >= 110 and a < 115，非主鍵唯一索引 [110,115)，肯定是要加鎖的；
2. 對(duì)應(yīng)的主鍵索引 10 應(yīng)該也會(huì)加鎖！

事實(shí)證明，又一次是錯(cuò)誤的！

分析 data_locks:

1. 怎么會(huì)對(duì)非主鍵唯一索引的 110 加了鎖？LOCK_MODE 還是 X，如果加了行鎖我還能理解。
2. 怎么會(huì)對(duì)非主鍵唯一索引的 115 加了鎖？

很明顯 110 和 115 之前的間隙以及它們自身的記錄都被鎖住了。

經(jīng)過一番分析，難道是因?yàn)?code style="font-size: 14px;padding: 2px 4px;background-color: rgba(27, 31, 35, 0.05);font-family: "Operator Mono", Consolas, Monaco, Menlo, monospace;word-break: break-all;color: rgb(255, 73, 108);border-radius: 2px;">前開后閉。

腦袋炸裂呀，完全和主鍵索引的 next-key lock 加鎖范圍不同，人家 sql 是什么就鎖什么。

有小伙伴知道原因可以告訴我。

如果我把 sql 改成下面的這種呢？

mysql> begin; select *  from t where a > 110 and a < 114 for update;

誒？？？

奇了怪了！

我唯一能想到的原因就是前開后閉了。因?yàn)?a >= 10 中的等于是屬于上一個(gè)區(qū)間的，所以需要鎖住上一個(gè)區(qū)間。

我只能說懵逼三連了?。?！

其實(shí)還是有結(jié)論的：

在非主鍵唯一索引范圍查詢時(shí)，會(huì)對(duì)相應(yīng)的范圍加前開后閉區(qū)間，并且如果存在數(shù)據(jù)，會(huì)對(duì)對(duì)應(yīng)的主鍵加行鎖。

這時(shí)候如果走覆蓋索引呢？？

mysql> begin; select id from t where a >= 110 and a < 115 for update;

按照剛才的思路，前開后閉：

1. 鎖住主鍵索引 110 的區(qū)間，115 的區(qū)間
2. 鎖住主鍵 10 的行鎖

事實(shí)又錯(cuò)了！

還鎖住了主鍵 15 的行鎖。

把等號(hào)去掉 15 是鎖住的。

感覺腦袋完全不夠用啊。重點(diǎn)是我沒有理解怎么主鍵還是前開后開，這里就前開后閉了？

難道我在這里試試那個(gè) bug？

啪啪打臉啊！

之前還說這個(gè) bug 在 8.0.18 被修復(fù)了，并優(yōu)化成了前開后開區(qū)間，這直接打臉，明擺著沒有修復(fù)。

我只是操作 a > 100 and a <= 115 for update; 竟然把 120 給我鎖住了，不就是 next-key 的 bug。

嘗試一下 sql

很明顯~ 這個(gè) bug 在非主鍵唯一索引上，并沒有修復(fù)?。。?/p>

3

總結(jié)

在非主鍵唯一索引情況下：

1. 非主鍵唯一索引等值查詢，數(shù)據(jù)存在，for update 是會(huì)在主鍵加鎖的，而 for share 只有在走覆蓋索引的情況下，會(huì)僅在自己索引上加鎖；
2. 非主鍵索引等值查詢，數(shù)據(jù)不存在，無論是否索引覆蓋，相當(dāng)于一個(gè)范圍查詢，僅僅會(huì)在非主鍵索引上加鎖，加的還是間隙鎖，前開后開區(qū)間；
3. 在非主鍵唯一索引范圍查詢時(shí)，不是覆蓋索引的時(shí)候，會(huì)對(duì)相應(yīng)的范圍加前開后閉區(qū)間，并且如果存在數(shù)據(jù)，會(huì)對(duì)對(duì)應(yīng)的主鍵加行鎖；
4. 在非主鍵唯一索引范圍查詢時(shí)，如果是覆蓋索引時(shí)，會(huì)對(duì)所有的后閉區(qū)間對(duì)應(yīng)的主鍵，加行鎖。

實(shí)踐完本文的所有操作，個(gè)人處于有些懵逼的狀態(tài)。我使用的版本是 8.0.25

1. 主鍵不是前開后閉，而非主鍵唯一索引看樣子又很遵循前開后閉原則；
2. next key 的 bug 在非主鍵唯一索引上，并沒有被修復(fù)！

仔細(xì)一想，似乎又可以理解。

因?yàn)橹麈I上的 next-key 的 bug 被修復(fù)了，同時(shí)優(yōu)化了前開后閉區(qū)間為前開后開區(qū)間，而非主鍵唯一索引上這個(gè) bug 沒有被修復(fù)，所以沒有優(yōu)化。

嗯~ 大概就是這樣吧！