聊聊MySQL的加鎖規(guī)則《死磕MySQL系列 十五》

大家好，我是咔咔 不期速成，日拱一卒

本期來聊聊Mysql的加鎖規(guī)則，知道這些規(guī)則后可以判斷SQL語句的加鎖范圍，同時(shí)也可以寫出更好的SQL語句，防止幻讀問題的產(chǎn)生，在能力范圍內(nèi)最大程度的提升Mysql并發(fā)處理事務(wù)能力。

現(xiàn)在你應(yīng)該知道了MVCC解決了快照讀下的幻讀問題，但當(dāng)前讀的幻讀問題還是基于鎖解決的，也就是next-key lock。

最新文章

死磕MySQL系列總目錄

為什么MySQL字符串不加引號(hào)索引失效？《死磕MySQL系列 十一》

打開order by的大門，一探究竟《死磕MySQL系列 十二》

重重封鎖，讓你一條數(shù)據(jù)都拿不到《死磕MySQL系列 十三》

闖禍了，生成環(huán)境執(zhí)行了DDL操作《死磕MySQL系列 十四》

一、了解next-key lock

在文章幻讀：聽說有人認(rèn)為我是被MVCC干掉的這期文章中，詳細(xì)說明了幻讀在當(dāng)前讀、快照讀下的解決方式。

快照讀簡(jiǎn)單來說就是簡(jiǎn)單的select操作，沒有加任何鎖，在Innodb存儲(chǔ)引擎下執(zhí)行簡(jiǎn)單的select操作時(shí)，會(huì)記錄下當(dāng)前的快照讀數(shù)據(jù)，之后的select會(huì)沿用第一次快照讀的數(shù)據(jù)，即使有其它事務(wù)提交也不會(huì)影響當(dāng)前的select結(jié)果，因此通過快照讀查詢的數(shù)據(jù)雖然是一致的，但有可能不是最新的數(shù)據(jù)，而是歷史數(shù)據(jù)。

這個(gè)是從官方文檔中獲取的資料，解釋在當(dāng)前讀下Innodb使用next-key lock鎖來解決幻讀問題。

To prevent phantoms, InnoDB uses an algorithm called next-key locking that combines index-row locking with gap locking. InnoDB performs row-level locking in such a way that when it searches or scans a table index, it sets shared or exclusive locks on the index records it encounters. Thus, the row-level locks are actually index-record locks. In addition, a next-key lock on an index record also affects the “gap” before that index record. That is, a next-key lock is an index-record lock plus a gap lock on the gap preceding the index record. If one session has a shared or exclusive lock on record R in an index, another session cannot insert a new index record in the gap immediately before R in the index order.

大致意思，為了防止幻讀，Innodb使用next-key lock算法，將行鎖(record lock)和間隙鎖(gap lock)結(jié)合在一起。Innodb行鎖在搜索或者掃描表索引時(shí)，會(huì)在遇到的索引記錄上設(shè)置共享鎖或者排它鎖，因此行鎖實(shí)際是索引記錄鎖。另外， 在索引記錄上設(shè)置的鎖同樣會(huì)影響索引記錄之前的“間隙(gap)”。即next-key lock是索引記錄行加上索引記錄之前的“gap”上的間隙鎖定。

二、next-key lock 加鎖規(guī)則

加鎖規(guī)則總結(jié)為以下幾點(diǎn)，不同MySQL版本會(huì)有微小的差異

查詢過程中只要訪問的數(shù)據(jù)都會(huì)加鎖，加鎖的基本單位是next-key lock，左開右閉

唯一索引等值查詢，next-key lock退化為行鎖

索引等值查詢，需要訪問到第一個(gè)不滿足條件的值，此時(shí)的next-key lock會(huì)退化為間隙鎖

索引范圍查詢需要訪問到不滿足條件的第一個(gè)值為止

之前看過丁老師的文章說是在唯一索引下，范圍查詢會(huì)訪問到不滿足條件的第一個(gè)值為止，這個(gè)問題在MySQL8.0.18已經(jīng)修復(fù)了

目前咔咔使用的MySQL版本是 8.0.26 ，接下來根據(jù)這幾條規(guī)則設(shè)計(jì)幾條SQL，一起來看看都鎖了那些數(shù)據(jù)。

創(chuàng)建next_key_lock表，建表的初始化語句如下。

CREATE TABLE `next_key_lock` ( `id` int(11) NOT NULL AUTO_INCREMENT, `class` tinyint(4) NOT NULL, `name` varchar(255) DEFAULT NULL, PRIMARY KEY (`id`) USING BTREE, KEY `idx_class` (`class`) USING BTREE ) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8; INSERT INTO next_key_lock (`class`,`name`) VALUES (1,'咔咔'),(3,'小劉'),(8,'小張'), (15,'小李'),(20,'張但'),(25,'王五'),(25,'李四');

1

2

3

4

5

6

7

8

9

10

三、唯一索引等值查詢

下圖是SQL的執(zhí)行流程，分為了三個(gè)終端，按照終端順序來執(zhí)行SQL

分析這條SQL滿足那些規(guī)則

規(guī)則一：查詢過程中只要訪問到的數(shù)據(jù)都會(huì)加鎖，加鎖的基本單位是next-key lock，左開右閉狀態(tài)。

規(guī)則二：唯一索引等值查詢，next-key lock退化為行鎖。

規(guī)則三：索引等值查詢，需要訪問到第一個(gè)不滿足條件的值，此時(shí)的next-key lock會(huì)退化為間隙鎖

根據(jù)規(guī)則一，加鎖范圍為(7,∞]

根據(jù)規(guī)則二，退化為行鎖，但明顯此條SQL不滿足條件，因?yàn)楸砝镞吘筒淮嬖趇d=9的這條記錄，所以此條規(guī)則不生效

根據(jù)規(guī)則三，next-key lock退化為間隙鎖，加鎖范圍為(7,∞)

結(jié)論

得知唯一索引等值查詢時(shí)，行數(shù)據(jù)存在的時(shí)候是行鎖，行數(shù)據(jù)不存在，那就是間隙鎖。

因此終端2的語句會(huì)一直處于等待狀態(tài)，直到終端1執(zhí)行完成。

四、普通索引等值查詢

分析這條SQL滿足那些規(guī)則

規(guī)則一：查詢過程中只要訪問到的數(shù)據(jù)都會(huì)加鎖，加鎖的基本單位是next-key lock，左開右閉狀態(tài)。

規(guī)則二：索引等值查詢，需要訪問到第一個(gè)不滿足條件的值，此時(shí)的next-key lock會(huì)退化為間隙鎖

根據(jù)規(guī)則一，加鎖范圍是(3,8]

根據(jù)規(guī)則二，需要訪問到第一個(gè)不滿足的值，加鎖范圍(8,15]，又因?yàn)闀?huì)退化為間隙鎖，加鎖范圍變?yōu)?8,15)

結(jié)論

三條SQL執(zhí)行后，你看到的現(xiàn)象是MySQL2執(zhí)行成功，MySQL3SQL等待

MySQL3要加入的值是9，在鎖范圍內(nèi)所以需要等MySQL1提交事務(wù)后才可執(zhí)行成功。

為什么MySQL2為什么會(huì)執(zhí)行成功

總結(jié)的加鎖規(guī)則中，查詢過程中訪問到的數(shù)據(jù)都會(huì)加鎖，但MySQL2使用的覆蓋索引，所以并不需要回表查詢主鍵索引，所以主鍵索引上是沒有加任何鎖的。

你要理解這塊就需要知道主鍵索引、普通索引的索引結(jié)構(gòu)，在B+tree中主鍵索引葉子節(jié)點(diǎn)存儲(chǔ)的是整行數(shù)據(jù)，而普通索引葉子節(jié)點(diǎn)存儲(chǔ)的是主鍵的值。

擴(kuò)展

現(xiàn)在你知道了在這個(gè)例子中，lock in share mode值鎖覆蓋索引，但是如果是for update就會(huì)給主鍵索引上滿足條件的行加上行鎖。所以你也知道了使用了覆蓋索引是避免不了數(shù)據(jù)被更新的，若想實(shí)現(xiàn)數(shù)據(jù)避免更新就需要繞過覆蓋索引的優(yōu)化。

現(xiàn)在你應(yīng)該知道使用for update會(huì)給主鍵索引加鎖，如果查詢條件為普通索引但值是存在多個(gè)相同數(shù)據(jù)的，此時(shí)的加鎖就會(huì)根據(jù)主鍵索引加鎖。

五、主鍵索引范圍鎖

從上圖得知MySQL2和MySQL3都處于等待MySQL1中

分析這條SQL滿足那些規(guī)則

規(guī)則一：訪問到的數(shù)據(jù)都會(huì)加鎖

規(guī)則二：唯一索引等值查詢，next_key_lock退化為行鎖

規(guī)則三：索引范圍查詢需要訪問到不滿足條件的第一個(gè)值為止

根據(jù)規(guī)則一，加鎖范圍(7,8]

根據(jù)規(guī)則二，退化為行鎖，加鎖范圍只是id=8這一行（后邊解釋）

根據(jù)規(guī)則三，范圍查詢就往后繼續(xù)找，加鎖范圍(8,∞]

結(jié)論

此條SQL加鎖范圍，行鎖id=8，next_key lock(8,∞]

問題：為什么從next-key lock退化為行鎖

首先你需要明白所謂的等值判斷和范圍判斷，指的是這一行數(shù)據(jù)被查詢選中的時(shí)候走的判斷條件是通過 a=b 還是 a>b或a

從SQL返回結(jié)果可得知數(shù)據(jù)是根據(jù)id=8來的，因此next-key lock會(huì)退化為行鎖。

六、普通索引范圍鎖

執(zhí)行SQL為

select * from next_key_lock where class >= 8 and class<10 for update;

1

可以看到這個(gè)SQL跟第五案例的MySQL1的唯一區(qū)別是普通索引沒有退化行鎖的規(guī)則。

分析這條SQL滿足那些規(guī)則

規(guī)則一：索引等值查詢需要訪問到第一個(gè)不滿足的值，next_key lock 退化為間隙鎖

規(guī)則二：索引范圍查詢需要訪問到不滿足條件的第一個(gè)值為止

根據(jù)規(guī)則一，加鎖范圍(7,8]

根據(jù)規(guī)則二，加鎖范圍(8,15]

結(jié)論

加鎖范圍為(7,8]、(8,15]

問題：為什么沒有退化為間隙鎖

仔細(xì)看規(guī)則，索引等值查詢需要訪問到不滿足的值才會(huì)退化為間隙鎖，此時(shí)是可以訪問到8這個(gè)數(shù)據(jù)的，因此不會(huì)退化為間隙鎖。

七、普通索引倒敘范圍鎖

在以上的所有案例中都是默認(rèn)正序規(guī)則，接下來看下倒敘時(shí)的加鎖規(guī)則是怎么樣的

執(zhí)行SQL為

select * from next_key_lock where class >= 15 and class<=20 order by desc lock in share mode;

1

由于SQL加上了order by ，因此第一個(gè)要定位class索引最右邊的值，也就是class=20，因?yàn)閏lass是普通索引等值查詢，因此會(huì)加上next-key lock 左開右閉(15,20]，普通索引等值查詢會(huì)訪問到不滿足條件的值為止，所以還會(huì)繼續(xù)掃描，直到遇到25，又會(huì)加上一個(gè)next-key lock (20,25]，又因?yàn)?5不滿足查詢條件，因此會(huì)退化為間隙鎖(20,25)

還有一個(gè)條件是class >= 15，向左掃描到class = 8才會(huì)停下來知道了是小于15了，加鎖單位是next-key loc ，左開右閉范圍是(3,8]

又因?yàn)椴樵兪?，繞過了覆蓋索引，需要回表查詢，因此給主鍵ID也會(huì)加鎖，加鎖為id=4，id=5兩個(gè)行鎖。

結(jié)論

因此這條SQL加鎖范圍在索引class是(3,25)，主鍵索引上id=4,5兩個(gè)行鎖。

八、總結(jié)

本期文章帶大家了解next_key lock的加鎖范圍，并且給大家總結(jié)了四條加鎖規(guī)則，經(jīng)過五個(gè)實(shí)戰(zhàn)案例給再給大家說幾個(gè)注意點(diǎn)。

唯一索引等值查詢時(shí)next-key lock退化為行鎖，這里指查詢到數(shù)據(jù)，若沒有查到數(shù)據(jù)則依然是間隙鎖

普通索引等值查詢next-key lock退化為間隙鎖

最后一點(diǎn)當(dāng)SQL加上排序時(shí)加鎖規(guī)則會(huì)有一定的變化，在后期文章中咔咔也會(huì)不斷的提供很多案例供大家查看。

堅(jiān)持學(xué)習(xí)、堅(jiān)持寫作、堅(jiān)持分享是咔咔從業(yè)以來所秉持的信念。愿文章在偌大的互聯(lián)網(wǎng)上能給你帶來一點(diǎn)幫助，我是咔咔，下期見。

MySQL SQL

版權(quán)聲明：本文內(nèi)容由網(wǎng)絡(luò)用戶投稿，版權(quán)歸原作者所有，本站不擁有其著作權(quán)，亦不承擔(dān)相應(yīng)法律責(zé)任。如果您發(fā)現(xiàn)本站中有涉嫌抄襲或描述失實(shí)的內(nèi)容，請(qǐng)聯(lián)系我們jiasou666@gmail.com 處理，核實(shí)后本網(wǎng)站將在24小時(shí)內(nèi)刪除侵權(quán)內(nèi)容。

版權(quán)聲明：本文內(nèi)容由網(wǎng)絡(luò)用戶投稿，版權(quán)歸原作者所有，本站不擁有其著作權(quán)，亦不承擔(dān)相應(yīng)法律責(zé)任。如果您發(fā)現(xiàn)本站中有涉嫌抄襲或描述失實(shí)的內(nèi)容，請(qǐng)聯(lián)系我們jiasou666@gmail.com 處理，核實(shí)后本網(wǎng)站將在24小時(shí)內(nèi)刪除侵權(quán)內(nèi)容。