某天,突然被問到 MySQL 的 next-key lock,我瞬間的反應(yīng)就是:
這都是啥啥啥???
這一個截圖我啥也看不出來呀?
仔細(xì)一看,好像似曾相識,這不是《MySQL 45 講》里面的內(nèi)容么?
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 鎖是索引記錄上的記錄鎖和索引記錄之前的間隙上的間隙鎖的組合。
先給自己來一串小問號???
既然啥都不懂,那只好從頭開始操作實踐一把了!
先看看看 《MySQL 45 講》中丁奇老師的結(jié)論:
看了這結(jié)論,應(yīng)該可以解答一大部分問題,不過有一句非常非常重點的話需要關(guān)注:MySQL 后面的版本可能會改變加鎖策略,所以這個規(guī)則只限于截止到現(xiàn)在的最新版本,即 5.x 系列=5.7.24,8.0 系列 =8.0.13
所以,以上的規(guī)則,對現(xiàn)在的版本并不一定適用,下面我以 MySQL 8.0.25
版本為例,進(jìn)行多角度驗證 next-key lock 加鎖范圍。
MySQL 版本:8.0.25
隔離級別:可重復(fù)讀(RR)
存儲引擎:InnoDB
mysql> select @@global.transaction_isolation,@@transaction_isolation\G mysql> show create table t\G
如何使用 Docker 安裝 MySQL,可以參考另一篇文章《使用 Docker 安裝并連接 MySQL》
首先來驗證主鍵索引的 next-key lock 的范圍
此時數(shù)據(jù)庫的數(shù)據(jù)如圖所示,對主鍵索引來說此時數(shù)據(jù)間隙如下:
主鍵等值查詢 —— 數(shù)據(jù)存在
mysql> begin; select * from t where id = 10 for update;
這條 SQL,對 id = 10
進(jìn)行加鎖,可以先思考一下加了什么鎖?鎖住了什么數(shù)據(jù)?
可以通過 data_locks
查看鎖信息,SQL 如下:
# mysql> select * from performance_schema.data_locks; mysql> select * from performance_schema.data_locks\G
具體字段含義可以參考 官方文檔
結(jié)果主要包含引擎、庫、表等信息,咱們需要重點關(guān)注以下幾個字段:
結(jié)果很明顯,這里是對表添加了一個 IX 鎖 并對主鍵索引 id = 10 的記錄,添加了一個 X,REC_NOT_GAP
鎖,表示只鎖定了記錄。
同樣 for share
是對表添加了一個 IS 鎖并對主鍵索引 id = 10 的記錄,添加了一個 S 鎖。
可以得出結(jié)論:
對主鍵等值加鎖,且值存在時,會對表添加意向鎖,同時會對主鍵索引添加行鎖。
mysql> select * from t where id = 11 for update;
如果是數(shù)據(jù)不存在的時候,會加什么鎖呢?鎖的范圍又是什么?
在驗證之前,分析一下數(shù)據(jù)的間隙。
id = 11
是肯定不存在的。但是加了 for update
,這時需要加 next-key lock,id = 11
所屬區(qū)間為 (10,15] 的等值查詢
,不需要鎖 id = 15
那條記錄,next-key lock 會退化為間隙鎖;使用 data_locks 分析一下鎖信息:
看下鎖的信息 X,GAP
表示加了間隙鎖,其中 LOCK_DATA = 15,表示鎖的是 主鍵索引 id = 15 之前的間隙。
此時在另一個 Session 執(zhí)行 SQL,答案顯而易見,是 id = 12 不可以插入,而 id = 15 是可以更新的。
可以得出結(jié)論,在數(shù)據(jù)不存在時,主鍵等值查詢,會鎖住該主鍵查詢條件所在的間隙。
mysql> begin; select * from t where id >= 10 and id 11 for update;
根據(jù) 《MySQL 45 講》分析得出下面結(jié)果:
id >= 10
定位到 10 所在的區(qū)間 (10,+∞);id 11
限定后續(xù)范圍,則根據(jù) 11 判斷下一個區(qū)間為 15 的先看下 data_locks
可以看到除了表鎖之外,還有 id = 10 的行鎖(X,REC_NOT_GAP
)以及主鍵索引 id = 15 之前的間隙鎖(X,GAP
)。
所以實際上 id = 15 是可以進(jìn)行更新的。也就是說前開后閉區(qū)間
出現(xiàn)了問題,個人認(rèn)為應(yīng)該是 id 11
這個條件判斷,導(dǎo)致不需要進(jìn)行了鎖 15 這個行鎖。
結(jié)果驗證也是正確的,id = 12 插入阻塞,id = 15 更新成功。
當(dāng)范圍的右側(cè)是包含等值查詢呢?
mysql> begin; select * from t where id > 10 and id = 15 for update;
來分析一下這個 SQL:
id > 10
定位到 10 所在的區(qū)間 (10,+∞);id = 15
定位是 (-∞, 15];結(jié)合起來則是 (10,15]。
同樣先看一下 data_locks
可以看出只添加了一個主鍵索引 id = 15 的 X 鎖。
驗證下 id = 15 是否可以更新?再驗證 id = 16 是否可以插入?
事實證明是沒有問題的!
當(dāng)然,這里有小伙伴會說,在 《MySQL 45 講》 里面說這里有一個 bug,會鎖住下一個 next-key。
事實證明,這個 bug 已經(jīng)被修復(fù)了。修復(fù)版本為 MySQL 8.0.18
。但是并沒有完全修復(fù)!??!
參考鏈接地址:
https://dev.mysql.com/doc/relnotes/mysql/8.0/en/news-8-0-18.html
搜索關(guān)鍵字:Bug #29508068)
咱們可以分別用 8.0.17 進(jìn)行復(fù)現(xiàn)一下:
在 8.0.17 中 id = 15
會將 id = 20 這條數(shù)據(jù)也鎖著,而在 8.0.25 版本中則不會。所以這個 bug 是被修復(fù)了的。
再來看下是前開后閉
還是前開后開
的問題,嚴(yán)謹(jǐn)一下,使用 8.0.17 和 8.0.18 做比較。
現(xiàn)在我估計大概率是在 8.0.18 版本修復(fù) Bug #29508068
的時候,把這個前開后閉
給優(yōu)化成了前開后開
了。
對比 data_locks 數(shù)據(jù):
注意紅色下劃線部分,在 8.0.17 版本中 id 17
時 LOCK_MODE 是 X
,而在 8.0.25 版本中則是 X,GAP
。
本文主要通過實際操作,對主鍵加鎖時的 next-key lock 范圍進(jìn)行了驗證,并查閱資料,對比版本得出不同的結(jié)論。
X,REC_NOT_GAP
;X,GAP
;=
查詢時,8.0.17 會鎖住下一個 next-key 的前開后閉區(qū)間,而 8.0.18 及以后版本,修復(fù)了這個 bug。優(yōu)化后,導(dǎo)致后開,這個不知道是因為優(yōu)化后,主鍵的區(qū)間會直接后開,還是因為是個 bug。具體小伙伴可以嘗試一下。
通過使用 select * from performance_schema.data_locks;
和操作實踐,可以看出 LOCK_MODE 和 LOCK_DATE 的關(guān)系:
LOCK_MODE | LOCK_DATA | 鎖范圍 |
---|---|---|
X,REC_NOT_GAP | 15 | 15 那條數(shù)據(jù)的行鎖 |
X,GAP | 15 | 15 那條數(shù)據(jù)之前的間隙,不包含 15 |
X | 15 | 15 那條數(shù)據(jù)的間隙,包含 15 |
LOCK_MODE = X
是前開后閉區(qū)間;X,GAP
是前開后開區(qū)間(間隙鎖);X,REC_NOT_GAP
行鎖。
基本已經(jīng)摸清主鍵的 next-key lock 范圍,注意版本使用的是 8.0.25。
文章篇幅有限,小伙伴可以先自己思考一下,盡量自己操作試一試,實踐出真知。至于具體答案,那就需要下一篇文章進(jìn)行驗證并總結(jié)結(jié)論了。
到此這篇關(guān)于淺談MySQL next-key lock 加鎖范圍 的文章就介紹到這了,更多相關(guān)MySQL next-key lock 加鎖范圍 內(nèi)容請搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家!
標(biāo)簽:自貢 龍巖 麗水 西寧 迪慶 南充 無錫 徐州
巨人網(wǎng)絡(luò)通訊聲明:本文標(biāo)題《淺談MySQL next-key lock 加鎖范圍》,本文關(guān)鍵詞 淺談,MySQL,next-key,lock,加鎖,;如發(fā)現(xiàn)本文內(nèi)容存在版權(quán)問題,煩請?zhí)峁┫嚓P(guān)信息告之我們,我們將及時溝通與處理。本站內(nèi)容系統(tǒng)采集于網(wǎng)絡(luò),涉及言論、版權(quán)與本站無關(guān)。