MySQL next-key lock 加鎖範圍是什麼?
前言
某天,突然被問到 MySQL 的 next-key lock,我瞬間的反應就是:
這都是啥啥啥???
這一個截圖我啥也看不出來呀?
仔細一看,好像似曾相識,這不是《MySQL 45 講》裡面的內容么?
什麼是 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.
官網的解釋大概意思就是:next-key 鎖是索引記錄上的記錄鎖和索引記錄之前的間隙上的間隙鎖的組合。
先給自己來一串小問號???
- 在主鍵、唯一索引、普通索引以及普通欄位上加鎖,是鎖住了哪些索引?
- 不同的查詢條件,分別鎖住了哪些範圍的數據?
- for share 和 for update 等值查詢和範圍查詢的鎖範圍?
- 當查詢的等值不存在時,鎖範圍是什麼?
- 當查詢條件分別是主鍵、唯一索引、普通索引時有什麼區別?
既然啥都不懂,那隻好從頭開始操作實踐一把了!
先看看看 《MySQL 45 講》中丁奇老師的結論:
看了這結論,應該可以解答一大部分問題,不過有一句非常非常重點的話需要關註:MySQL 後面的版本可能會改變加鎖策略,所以這個規則只限於截止到現在的最新版本,即 5.x 系列<=5.7.24,8.0 系列 <=8.0.13
所以,以上的規則,對現在的版本並不一定適用,下面我以 MySQL 8.0.25
版本為例,進行多角度驗證 next-key lock 加鎖範圍。
環境準備
MySQL 版本:8.0.25
隔離級別:可重複讀(RR)
存儲引擎:InnoDB
mysql> select @@global.transaction_isolation,@@transaction_isolation\G
mysql> show create table t\G
如何使用 Docker 安裝 MySQL,可以參考另一篇文章《使用 Docker 安裝並連接 MySQL》
主鍵索引
首先來驗證主鍵索引的 next-key lock 的範圍
此時資料庫的數據如圖所示,對主鍵索引來說此時數據間隙如下:
主鍵等值查詢 —— 數據存在
mysql> begin; select * from t where id = 10 for update;
這條 SQL,對 id = 10
進行加鎖,可以先思考一下加了什麼鎖?鎖住了什麼數據?
可以通過 data_locks
查看鎖資訊,SQL 如下:
# mysql> select * from performance_schema.data_locks;
mysql> select * from performance_schema.data_locks\G
具體欄位含義可以參考 官方文檔
結果主要包含引擎、庫、表等資訊,咱們需要重點關注以下幾個欄位:
- INDEX_NAME:鎖定索引的名稱
- LOCK_TYPE:鎖的類型,對於 InnoDB,允許的值為 RECORD 行級鎖 和 TABLE 表級鎖。
- LOCK_MODE:鎖的類型:S, X, IS, IX, and gap locks
- LOCK_DATA:鎖關聯的數據,對於 InnoDB,當 LOCK_TYPE 是 RECORD(行鎖),則顯示值。當鎖在主鍵索引上時,則值是鎖定記錄的主鍵值。當鎖是在輔助索引上時,則顯示輔助索引的值,並附加上主鍵值。
結果很明顯,這裡是對錶添加了一個 IX 鎖 並對主鍵索引 id = 10 的記錄,添加了一個 X,REC_NOT_GAP
鎖,表示只鎖定了記錄。
同樣 for share
是對錶添加了一個 IS 鎖並對主鍵索引 id = 10 的記錄,添加了一個 S 鎖。
可以得出結論:
對主鍵等值加鎖,且值存在時,會對錶添加意向鎖,同時會對主鍵索引添加行鎖。
主鍵等值查詢 —— 數據不存在
mysql> select * from t where id = 11 for update;
如果是數據不存在的時候,會加什麼鎖呢?鎖的範圍又是什麼?
在驗證之前,分析一下數據的間隙。
id = 11
是肯定不存在的。但是加了for update
,這時需要加 next-key lock,id = 11
所屬區間為 (10,15] 的前開後閉區間;- 因為是
等值查詢
,不需要鎖id = 15
那條記錄,next-key lock 會退化為間隙鎖; - 最終區間為 (10,15) 的前開後開區間。
使用 data_locks 分析一下鎖資訊:
看下鎖的資訊 X,GAP
表示加了間隙鎖,其中 LOCK_DATA = 15,表示鎖的是 主鍵索引 id = 15 之前的間隙。
此時在另一個 Session 執行 SQL,答案顯而易見,是 id = 12 不可以插入,而 id = 15 是可以更新的。
可以得出結論,在數據不存在時,主鍵等值查詢,會鎖住該主鍵查詢條件所在的間隙。
主鍵範圍查詢(重點)
mysql> begin; select * from t where id >= 10 and id < 11 for update;
根據 《MySQL 45 講》分析得出下面結果:
id >= 10
定位到 10 所在的區間 (10,+∞);- 因為是 >= 存在等值判斷,所以需要包含 10 這個值,變為 [10,+∞) 前閉後閉區間;
id < 11
限定後續範圍,則根據 11 判斷下一個區間為 15 的前開後閉區間;- 結合起來則是 [10,15]。(不完全正確)
先看下 data_locks
可以看到除了表鎖之外,還有 id = 10 的行鎖(X,REC_NOT_GAP
)以及主鍵索引 id = 15 之前的間隙鎖(X,GAP
)。
所以實際上 id = 15 是可以進行更新的。也就是說前開後閉區間
出現了問題,個人認為應該是 id < 11
這個條件判斷,導致不需要進行了鎖 15 這個行鎖。
結果驗證也是正確的,id = 12 插入阻塞,id = 15 更新成功。
當範圍的右側是包含等值查詢呢?
mysql> begin; select * from t where id > 10 and id <= 15 for update;
來分析一下這個 SQL:
id > 10
定位到 10 所在的區間 (10,+∞);id <= 15
定位是 (-∞, 15];- 結合起來則是 (10,15]。
同樣先看一下 data_locks
可以看出只添加了一個主鍵索引 id = 15 的 X 鎖。
驗證下 id = 15 是否可以更新?再驗證 id = 16 是否可以插入?
事實證明是沒有問題的!
當然,這裡有小夥伴會說,在 《MySQL 45 講》 裡面說這裡有一個 bug,會鎖住下一個 next-key。
事實證明,這個 bug 已經被修復了。修復版本為 MySQL 8.0.18
。但是並沒有完全修復!!!
參考鏈接地址:
//dev.mysql.com/doc/relnotes/mysql/8.0/en/news-8-0-18.html#mysqld-8-0-18-bug
搜索關鍵字:Bug #29508068)
咱們可以分別用 8.0.17 進行復現一下:
在 8.0.17 中 id <= 15
會將 id = 20 這條數據也鎖著,而在 8.0.25 版本中則不會。所以這個 bug 是被修復了的。
再來看下是前開後閉
還是前開後開
的問題,嚴謹一下,使用 8.0.17 和 8.0.18 做比較。
現在我估計大概率是在 8.0.18 版本修復 Bug #29508068
的時候,把這個前開後閉
給優化成了前開後開
了。
對比 data_locks 數據:
注意紅色下劃線部分,在 8.0.17 版本中 id < 17
時 LOCK_MODE 是 X
,而在 8.0.25 版本中則是 X,GAP
。
總結
本文主要通過實際操作,對主鍵加鎖時的 next-key lock 範圍進行了驗證,並查閱資料,對比版本得出不同的結論。
結論一:
- 加鎖時,會先給表添加意向鎖,IX 或 IS;
- 加鎖是如果是多個範圍,是分開加了多個鎖,每個範圍都有鎖;(這個可以實踐下 id < 20 的情況)
- 主鍵等值查詢,數據存在時,會對該主鍵索引的值加行鎖
X,REC_NOT_GAP
; - 主鍵等值查詢,數據不存在時,會對查詢條件主鍵值所在的間隙添加間隙鎖
X,GAP
; - 主鍵等值查詢,範圍查詢時情況則比較複雜:
- 8.0.17 版本是前開後閉,而 8.0.18 版本及以後,進行了優化,主鍵時判斷不等,不會鎖住後閉的區間。
- 臨界
<=
查詢時,8.0.17 會鎖住下一個 next-key 的前開後閉區間,而 8.0.18 及以後版本,修復了這個 bug。
優化後,導致後開,這個不知道是因為優化後,主鍵的區間會直接後開,還是因為是個 bug。具體小夥伴可以嘗試一下。
結論二
通過使用 select * from performance_schema.data_locks;
和操作實踐,可以看出 LOCK_MODE 和 LOCK_DATE 的關係:
LOCK_MODE | LOCK_DATA | 鎖範圍 |
---|---|---|
X,REC_NOT_GAP | 15 | 15 那條數據的行鎖 |
X,GAP | 15 | 15 那條數據之前的間隙,不包含 15 |
X | 15 | 15 那條數據的間隙,包含 15 |
LOCK_MODE = X
是前開後閉區間;X,GAP
是前開後開區間(間隙鎖);X,REC_NOT_GAP
行鎖。
基本已經摸清主鍵的 next-key lock 範圍,注意版本使用的是 8.0.25。
疑問
- 那唯一索引的 next-key lock 範圍是什麼?
- 當索引覆蓋時鎖的範圍和加鎖的索引分別是什麼?
- 我為什麼說這個 bug 沒有完全修復,也是在非主鍵唯一索引中復現了這個 bug。
文章篇幅有限,小夥伴可以先自己思考一下,盡量自己操作試一試,實踐出真知。至於具體答案,那就需要下一篇文章進行驗證並總結結論了。