MySQL next-key lock 加鎖范圍是什么？

前言

某天，突然被問(wèn)到 MySQL 的 next-key lock，我瞬間的反應(yīng)就是：

這都是啥啥啥？？？

這一個(gè)截圖我啥也看不出來(lái)呀？

仔細(xì)一看，好像似曾相識(shí)，這不是《MySQL 45 講》里面的內(nèi)容么？

1

什么是 next-key lock

A next-key lock is a combination of a record lock on the index record and a gap lock on the gap before the index record.

官網(wǎng)的解釋大概意思就是：next-key 鎖是索引記錄上的記錄鎖和索引記錄之前的間隙上的間隙鎖的組合。

先給自己來(lái)一串小問(wèn)號(hào)？？？

1. 在主鍵、唯一索引、普通索引以及普通字段上加鎖，是鎖住了哪些索引？
2. 不同的查詢條件，分別鎖住了哪些范圍的數(shù)據(jù)？
3. for share 和 for update 等值查詢和范圍查詢的鎖范圍？
4. 當(dāng)查詢的等值不存在時(shí)，鎖范圍是什么？
5. 當(dāng)查詢條件分別是主鍵、唯一索引、普通索引時(shí)有什么區(qū)別？

既然啥都不懂，那只好從頭開(kāi)始操作實(shí)踐一把了！

先看看看 《MySQL 45 講》中丁奇老師的結(jié)論：

看了這結(jié)論，應(yīng)該可以解答一大部分問(wèn)題，不過(guò)有一句非常非常重點(diǎn)的話需要關(guān)注：MySQL 后面的版本可能會(huì)改變加鎖策略，所以這個(gè)規(guī)則只限于截止到現(xiàn)在的最新版本，即 5.x 系列<=5.7.24，8.0 系列 <=8.0.13

所以，以上的規(guī)則，對(duì)現(xiàn)在的版本并不一定適用，下面我以 MySQL 8.0.25 版本為例，進(jìn)行多角度驗(yàn)證 next-key lock 加鎖范圍。

2

環(huán)境準(zhǔn)備

MySQL 版本：8.0.25

隔離級(jí)別：可重復(fù)讀（RR）

存儲(chǔ)引擎：InnoDB

mysql> select @@global.transaction_isolation,@@transaction_isolation\G
mysql> show create table t\G

如何使用 Docker 安裝 MySQL，可以參考另一篇文章《使用 Docker 安裝并連接 MySQL》

3

主鍵索引

首先來(lái)驗(yàn)證主鍵索引的 next-key lock 的范圍

此時(shí)數(shù)據(jù)庫(kù)的數(shù)據(jù)如圖所示，對(duì)主鍵索引來(lái)說(shuō)此時(shí)數(shù)據(jù)間隙如下：

主鍵等值查詢 —— 數(shù)據(jù)存在

mysql> begin; select * from t where id = 10 for update;

這條 SQL，對(duì) id = 10 進(jìn)行加鎖，可以先思考一下加了什么鎖？鎖住了什么數(shù)據(jù)？

可以通過(guò) data_locks 查看鎖信息，SQL 如下：

# mysql> select * from performance_schema.data_locks;
mysql> select * from performance_schema.data_locks\G

具體字段含義可以參考 官方文檔^[1]

結(jié)果主要包含引擎、庫(kù)、表等信息，咱們需要重點(diǎn)關(guān)注以下幾個(gè)字段：

• INDEX_NAME：鎖定索引的名稱
• LOCK_TYPE：鎖的類型，對(duì)于 InnoDB，允許的值為 RECORD 行級(jí)鎖 和 TABLE 表級(jí)鎖。
• LOCK_MODE：鎖的類型：S, X, IS, IX, and gap locks
• LOCK_DATA：鎖關(guān)聯(lián)的數(shù)據(jù)，對(duì)于 InnoDB，當(dāng) LOCK_TYPE 是 RECORD（行鎖），則顯示值。當(dāng)鎖在主鍵索引上時(shí)，則值是鎖定記錄的主鍵值。當(dāng)鎖是在輔助索引上時(shí)，則顯示輔助索引的值，并附加上主鍵值。

結(jié)果很明顯，這里是對(duì)表添加了一個(gè) IX 鎖 并對(duì)主鍵索引 id = 10 的記錄，添加了一個(gè) X,REC_NOT_GAP 鎖，表示只鎖定了記錄。

同樣 for share 是對(duì)表添加了一個(gè) IS 鎖并對(duì)主鍵索引 id = 10 的記錄，添加了一個(gè) S 鎖。

可以得出結(jié)論：

對(duì)主鍵等值加鎖，且值存在時(shí)，會(huì)對(duì)表添加意向鎖，同時(shí)會(huì)對(duì)主鍵索引添加行鎖。

主鍵等值查詢 —— 數(shù)據(jù)不存在

mysql> select * from t where id = 11 for update;

如果是數(shù)據(jù)不存在的時(shí)候，會(huì)加什么鎖呢？鎖的范圍又是什么？

在驗(yàn)證之前，分析一下數(shù)據(jù)的間隙。

1. id = 11 是肯定不存在的。但是加了 for update，這時(shí)需要加 next-key lock，id = 11 所屬區(qū)間為 (10,15] 的前開(kāi)后閉區(qū)間；
2. 因?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;">等值查詢，不需要鎖 id = 15 那條記錄，next-key lock 會(huì)退化為間隙鎖；
3. 最終區(qū)間為 (10,15) 的前開(kāi)后開(kāi)區(qū)間。

使用 data_locks 分析一下鎖信息：

看下鎖的信息 X,GAP 表示加了間隙鎖，其中 LOCK_DATA = 15，表示鎖的是 主鍵索引 id = 15 之前的間隙。

此時(shí)在另一個(gè) Session 執(zhí)行 SQL，答案顯而易見(jiàn)，是 id = 12 不可以插入，而 id = 15 是可以更新的。

可以得出結(jié)論，在數(shù)據(jù)不存在時(shí)，主鍵等值查詢，會(huì)鎖住該主鍵查詢條件所在的間隙。

主鍵范圍查詢（重點(diǎn)）

mysql> begin; select * from t where id >= 10 and id < 11 for update;

根據(jù) 《MySQL 45 講》分析得出下面結(jié)果：

1. id >= 10 定位到 10 所在的區(qū)間 (10,+∞)；
2. 因?yàn)槭?>= 存在等值判斷，所以需要包含 10 這個(gè)值，變?yōu)?[10,+∞) 前閉后閉區(qū)間；
3. id < 11 限定后續(xù)范圍，則根據(jù) 11 判斷下一個(gè)區(qū)間為 15 的前開(kāi)后閉區(qū)間；
4. 結(jié)合起來(lái)則是 [10,15]。（不完全正確）

先看下 data_locks

可以看到除了表鎖之外，還有 id = 10 的行鎖（X,REC_NOT_GAP）以及主鍵索引 id = 15 之前的間隙鎖（X,GAP）。

所以實(shí)際上 id = 15 是可以進(jìn)行更新的。也就是說(shuō)前開(kāi)后閉區(qū)間出現(xiàn)了問(wèn)題，個(gè)人認(rèn)為應(yīng)該是 id < 11 這個(gè)條件判斷，導(dǎo)致不需要進(jìn)行了鎖 15 這個(gè)行鎖。

結(jié)果驗(yàn)證也是正確的，id = 12 插入阻塞，id = 15 更新成功。

當(dāng)范圍的右側(cè)是包含等值查詢呢？

mysql> begin; select * from t where id > 10 and id <= 15 for update;

來(lái)分析一下這個(gè) SQL：

1. id > 10 定位到 10 所在的區(qū)間 (10,+∞)；
2. id <= 15 定位是 (-∞, 15]；
3. 結(jié)合起來(lái)則是 (10,15]。

同樣先看一下 data_locks

可以看出只添加了一個(gè)主鍵索引 id = 15 的 X 鎖。

驗(yàn)證下 id = 15 是否可以更新？再驗(yàn)證 id = 16 是否可以插入？

事實(shí)證明是沒(méi)有問(wèn)題的！

當(dāng)然，這里有小伙伴會(huì)說(shuō)，在 《MySQL 45 講》 里面說(shuō)這里有一個(gè) bug，會(huì)鎖住下一個(gè) next-key。

事實(shí)證明，這個(gè) bug 已經(jīng)被修復(fù)了。修復(fù)版本為 MySQL 8.0.18。但是并沒(méi)有完全修復(fù)！！！

參考鏈接地址：

https://dev.mysql.com/doc/relnotes/mysql/8.0/en/news-8-0-18.html#mysqld-8-0-18-bug

搜索關(guān)鍵字：Bug #29508068)

咱們可以分別用 8.0.17 進(jìn)行復(fù)現(xiàn)一下：

在 8.0.17 中 id <= 15 會(huì)將 id = 20 這條數(shù)據(jù)也鎖著，而在 8.0.25 版本中則不會(huì)。所以這個(gè) bug 是被修復(fù)了的。

再來(lái)看下是前開(kāi)后閉還是前開(kāi)后開(kāi)的問(wèn)題，嚴(yán)謹(jǐn)一下，使用 8.0.17 和 8.0.18 做比較。

現(xiàn)在我估計(jì)大概率是在 8.0.18 版本修復(fù) Bug #29508068 的時(shí)候，把這個(gè)前開(kāi)后閉給優(yōu)化成了前開(kāi)后開(kāi)了。

對(duì)比 data_locks 數(shù)據(jù)：

注意紅色下劃線部分，在 8.0.17 版本中 id < 17 時(shí) LOCK_MODE 是 X，而在 8.0.25 版本中則是 X,GAP。

4

總結(jié)

本文主要通過(guò)實(shí)際操作，對(duì)主鍵加鎖時(shí)的 next-key lock 范圍進(jìn)行了驗(yàn)證，并查閱資料，對(duì)比版本得出不同的結(jié)論。

結(jié)論一：

1. 加鎖時(shí)，會(huì)先給表添加意向鎖，IX 或 IS；
2. 加鎖是如果是多個(gè)范圍，是分開(kāi)加了多個(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 版本是前開(kāi)后閉，而 8.0.18 版本及以后，進(jìn)行了優(yōu)化，主鍵時(shí)判斷不等，不會(huì)鎖住后閉的區(qū)間。
2. 臨界 <= 查詢時(shí)，8.0.17 會(huì)鎖住下一個(gè) next-key 的前開(kāi)后閉區(qū)間，而 8.0.18 及以后版本，修復(fù)了這個(gè) bug。

優(yōu)化后，導(dǎo)致后開(kāi)，這個(gè)不知道是因?yàn)閮?yōu)化后，主鍵的區(qū)間會(huì)直接后開(kāi)，還是因?yàn)槭莻€(gè) bug。具體小伙伴可以嘗試一下。

結(jié)論二

通過(guò)使用 select * from performance_schema.data_locks; 和操作時(shí)間，可以看出 LOCK_MODE 和 LOCK_DATE 的關(guān)系：

1. LOCK_MODE = X 是前開(kāi)后閉區(qū)間；
2. X,GAP 是前開(kāi)后開(kāi)區(qū)間（間隙鎖）；
3. X,REC_NOT_GAP 行鎖。

基本已經(jīng)摸清主鍵的 next-key lock 范圍，注意版本使用的是 8.0.25。

疑問(wèn)

1. 那唯一索引的 next-key lock 范圍是什么?
2. 當(dāng)索引覆蓋時(shí)鎖的范圍和加鎖的索引分別是什么？
3. 我為什么說(shuō)這個(gè) bug 沒(méi)有完全修復(fù)，也是在非主鍵唯一索引中復(fù)現(xiàn)了這個(gè) bug。
   

文章篇幅有限，小伙伴可以先自己思考一下，盡量自己操作試一試，實(shí)踐出真知。至于具體答案，那就需要下一篇文章進(jìn)行驗(yàn)證并總結(jié)結(jié)論了。

引用鏈接：