大家好雀彼，我是咔咔 不期速成莺丑，日拱一卒
本期來聊聊MySQL的加鎖規(guī)則炕横，知道這些規(guī)則后可以判斷SQL語句的加鎖范圍卿嘲，同時(shí)也可以寫出更好的SQL語句，防止幻讀問題的產(chǎn)生肺缕，在能力范圍內(nèi)最大程度的提升MySQL并發(fā)處理事務(wù)能力黔寇。
現(xiàn)在你應(yīng)該知道了MVCC解決了快照讀下的幻讀問題榛做，但當(dāng)前讀的幻讀問題還是基于鎖解決的，也就是next-key lock倒脓。
最新文章
為什么MySQL字符串不加引號(hào)索引失效腾窝？《死磕MySQL系列 十一》
打開order by的大門咒彤，一探究竟《死磕MySQL系列 十二》
重重封鎖故觅，讓你一條數(shù)據(jù)都拿不到《死磕MySQL系列 十三》
闖禍了盗迟，生成環(huán)境執(zhí)行了DDL操作《死磕MySQL系列 十四》
一盟劫、了解next-key lock
在文章幻讀：聽說有人認(rèn)為我是被MVCC干掉的這期文章中猴娩，詳細(xì)說明了幻讀在當(dāng)前讀徙瓶、快照讀下的解決方式闹炉。
快照讀簡單來說就是簡單的select操作，沒有加任何鎖语御，在Innodb存儲(chǔ)引擎下執(zhí)行簡單的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表，建表的初始化語句如下枫虏。
<pre class="custom" data-tool="mdnice編輯器" style="margin-top: 10px; margin-bottom: 10px; border-radius: 5px; box-shadow: rgba(0, 0, 0, 0.55) 0px 2px 10px;">CREATE TABLEnext_key_lock(idint(11) NOT NULL AUTO_INCREMENT,classtinyint(4) NOT NULL,namevarchar(255) DEFAULT NULL, PRIMARY KEY (id) USING BTREE, KEYidx_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,'李四');` </pre>
三辣恋、唯一索引等值查詢
下圖是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)?code>(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<b 來確定的剑勾，直白點(diǎn)就是這行數(shù)據(jù)是通過等值來的還是范圍查詢來的。
從SQL返回結(jié)果可得知數(shù)據(jù)是根據(jù)id=8來的赵颅，因此next-key lock會(huì)退化為行鎖虽另。
六、普通索引范圍鎖
執(zhí)行SQL為
<pre class="custom" data-tool="mdnice編輯器" style="margin-top: 10px; margin-bottom: 10px; border-radius: 5px; box-shadow: rgba(0, 0, 0, 0.55) 0px 2px 10px;">select * from next_key_lock where class >= 8 and class<10 for update; </pre>
可以看到這個(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為
<pre class="custom" data-tool="mdnice編輯器" style="margin-top: 10px; margin-bottom: 10px; border-radius: 5px; box-shadow: rgba(0, 0, 0, 0.55) 0px 2px 10px;">select * from next_key_lock where class >= 15 and class<=20 order by desc lock in share mode; </pre>
由于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)幫助湖员，我是咔咔，下期見者春。
”
