大家好，我是咔咔 不期速成，日拱一卒
本期來聊聊MySQL的加鎖規則，知道這些規則後可以判斷SQL語句的加鎖範圍，同時也可以寫出更好的SQL語句，防止幻讀問題的產生，在能力範圍內最大程度的提升MySQL併發處理事務能力。
現在你應該知道了MVCC解決了快照讀下的幻讀問題，但當前讀的幻讀問題還是基於鎖解決的，也就是next-key lock。
最新文章
為什麼MySQL字串不加引號索引失效？《死磕MySQL系列 十一》
開啟order by的大門，一探究竟《死磕MySQL系列 十二》
闖禍了，生成環境執行了DDL操作《死磕MySQL系列 十四》
一、瞭解next-key lock
在文章幻讀：聽說有人認為我是被MVCC幹掉的這期文章中，詳細說明了幻讀在當前讀、快照讀下的解決方式。
快照讀簡單來說就是簡單的select操作，沒有加任何鎖，在Innodb儲存引擎下執行簡單的select操作時，會記錄下當前的快照讀資料，之後的select會沿用第一次快照讀的資料，即使有其它事務提交也不會影響當前的select結果，因此通過快照讀查詢的資料雖然事一致的，但有可能不是最新的資料，而是歷史資料。
這個是從官方文件中獲取的資料，解釋在當前讀下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)結合在一起。Innodb行鎖在搜尋或者掃描表索引時，會在遇到的索引記錄上設定共享鎖或者排它鎖，因此行鎖實際是索引記錄鎖。另外， 在索引記錄上設定的鎖同樣會影響索引記錄之前的“間隙(gap)”。即next-key lock是索引記錄行加上索引記錄之前的“gap”上的間隙鎖定。
”
二、next-key lock 加鎖規則
加鎖規則總結為以下幾點，不同MySQL版本會有微小的差異
查詢過程中只要訪問的資料都會加鎖，加鎖的基本單位是next-key lock，左開右閉 唯一索引等值查詢，next-key lock退化為行鎖 索引等值查詢，需要訪問到第一個不滿足條件的值，此時的next-key lock會退化為間隙鎖 索引範圍查詢需要訪問到不滿足條件的第一個值為止
之前看過丁老師的文章說是在唯一索引下，範圍查詢會訪問到不滿足條件的第一個值為止，這個問題在MySQL8.0.18已經修復了
目前咔咔使用的MySQL版本是 8.0.26 ，接下來根據這幾條規則設計幾條SQL，一起來看看都鎖了那些資料。
建立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,'李四');
三、唯一索引等值查詢
下圖是SQL的執行流程，分為了三個終端，按照終端順序來執行SQL
分析這條SQL滿足那些規則
規則一：查詢過程中只要訪問到的資料都會加鎖，加鎖的基本單位是next-key lock，左開右閉狀態。
規則二：唯一索引等值查詢，next-key lock退化為行鎖。
規則三：索引等值查詢，需要訪問到第一個不滿足條件的值，此時的next-key lock會退化為間隙鎖
根據規則一，加鎖範圍為(7,∞]
根據規則二，退化為行鎖，但明顯此條SQL不滿足條件，因為表裡邊就不存在id=9的這條記錄，所以此條規則不生效
根據規則三，next-key lock退化為間隙鎖，加鎖範圍為(7,∞)
結論
得知唯一索引等值查詢時，行資料存在的時候是行鎖，行資料不存在，那就是間隙鎖。
因此終端2的語句會一直處於等待狀態，直到終端1執行完成。
四、普通索引等值查詢
分析這條SQL滿足那些規則
規則一：查詢過程中只要訪問到的資料都會加鎖，加鎖的基本單位是next-key lock，左開右閉狀態。
規則二：索引等值查詢，需要訪問到第一個不滿足條件的值，此時的next-key lock會退化為間隙鎖
根據規則一，加鎖範圍是(3,8]
根據規則二，需要訪問到第一個不滿足的值，加鎖範圍(8,15]，有因為會退化為間隙鎖，加鎖範圍變為(8,15)
結論
三條SQL執行後，你看到的現象是MySQL2執行成功，MySQL3SQL等待
MySQL3要加入的值是9，在鎖範圍內所以需要等MySQL1提交事務後才可執行成功。
為什麼MySQL2為什麼會執行成功
總結的加鎖規則中，查詢過程中訪問到的資料都會加鎖，但MySQL2使用的覆蓋索引，所以並不需要回表查詢主鍵索引，所以主鍵索引上是沒有加任何鎖的。
你要理解這塊就需要知道主鍵索引、普通索引的索引結構，在B+tree中主鍵索引葉子節點儲存的是整行資料，而普通索引葉子節點儲存的是主鍵的值。
擴充套件
現在你知道了在這個例子中，lock in share mode值鎖覆蓋索引，但是如果是for update就會給主鍵索引上滿足條件的行加上行鎖。所以你也知道了使用了覆蓋索引是避免不了資料被更新的，若想實現資料避免更新就需要繞過覆蓋索引的優化。
現在你應該知道使用for update會給主鍵索引加鎖，如果查詢條件為普通索引但值是存在多個相同資料的，此時的加鎖就會根據主鍵索引加鎖。
五、主鍵索引範圍鎖
從上圖得知MySQL2和MySQL3都處於等待MySQL1中
分析這條SQL滿足那些規則
規則一：訪問到的資料都會加鎖
規則二：唯一索引等值查詢，next_key_lock退化為行鎖
規則三：索引範圍查詢需要訪問到不滿足條件的第一個值為止
根據規則一，加鎖範圍(7,8]
根據規則二，退化為行鎖，加鎖範圍只是id=8這一行（後邊解釋）
根據規則三，範圍查詢就往後繼續找，加鎖範圍(8,∞]
結論
此條SQL加鎖範圍，行鎖id=8，next_key lock(8,∞]
問題：為什麼從next-key lock退化為行鎖
首先你需要明白所謂的等值判斷和範圍判斷，指的是這一行資料被查詢選中的時候走的判斷條件是通過 a=b 還是 a>b或a<b 來確定的，直白點就是這行資料是通過等值來的還是範圍查詢來的。
從SQL返回結果可得知資料是根據id=8來的，因此next-key lock會退化為行鎖。
六、普通索引範圍鎖
執行SQL為
select * from next_key_lock where class >= 8 and class<10 for update;
可以看到這個SQL跟第五案例的MySQL1的唯一區別是普通索引沒有退化行鎖的規則。
分析這條SQL滿足那些規則
規則一：索引等值查詢需要訪問到第一個不滿足的值，next_key lock 退化為間隙鎖
規則二：索引範圍查詢需要訪問到不滿足條件的第一個值為止
根據規則一，加鎖範圍(7,8]
根據規則二，加鎖範圍(8,15]
結論
加鎖範圍為(7,8]、(8,15]
問題：為什麼沒有退化為間隙鎖
仔細看規則，索引等值查詢需要訪問到不滿足的值才會退化為間隙鎖，此時是可以訪問到8這個資料的，因此不會退化為間隙鎖。
七、普通索引倒敘範圍鎖
在以上的所有案例中都是預設正序規則，接下來看下倒敘時的加鎖規則是怎麼樣的
執行SQL為
select * from next_key_lock where class >= 15 and class<=20 order by desc lock in share mode;
由於SQL加上了order by ，因此第一個要定位class索引最右邊的值，也就是class=20，因為class是普通索引等值查詢，因此會加上next-key lock 左開右閉(15,20]，普通索引等值查詢會訪問到不滿足條件的值為止，所以還會繼續掃描，直到遇到25，又會加上一個next-key lock (20,25]，又因為25不滿足查詢條件，因此會退化為間隙鎖(20,25)
還有一個條件是class >= 15，向左掃描到class = 8才會停下來知道了是小於15了，加鎖單位是next-key loc ，左開右閉範圍是(3,8]
又因為查詢是*，繞過了覆蓋索引，需要回表查詢，因此給主鍵ID也會加鎖，加鎖為id=4，id=5兩個行鎖。
結論
因此這條SQL加鎖範圍在索引class是(3,25)，主鍵索引上id=4,5兩個行鎖。
八、總結
本期文章帶大家瞭解next_key lock的加鎖範圍，並且給大家總結了四條加鎖規則，經過五個實戰案例給再給大家說幾個注意點。
唯一索引等值查詢時next-key lock退化為行鎖，這裡指查詢到資料，若沒有查到資料則依然是間隙鎖
普通索引等值查詢next-key lock退化為間隙鎖
最後一點當SQL加上排序時加鎖規則會有一定的變化，在後期文章中咔咔也會不斷的提供很多案例供大家檢視。
“堅持學習、堅持寫作、堅持分享是咔咔從業以來所秉持的信念。願文章在偌大的網際網路上能給你帶來一點幫助，我是咔咔，下期見。
”