上文我們介紹了間隙鎖和 next-key lock 的概念，但是并沒有說明加鎖規(guī)則罢猪。間隙鎖的概念理解起來確實有點兒難师抄，尤其在配合上行鎖以后谐区，很容易在判斷是否會出現(xiàn)鎖等待的問題上犯錯湖蜕。
本文所講的加鎖規(guī)則有以下前提說明：
- MySQL 后面的版本可能會改變加鎖策略，所以這個規(guī)則只限于截止到現(xiàn)在的最新版本宋列，即 5.x 系列 <=5.7.24昭抒，8.0 系列 <=8.0.13。
- 因為間隙鎖在可重復(fù)讀隔離級別下才有效炼杖，所以本篇文章接下來的描述灭返，若沒有特殊說明，默認(rèn)是可重復(fù)讀隔離級別坤邪。
加鎖規(guī)則：兩個“原則”熙含、兩個“優(yōu)化”和一個“bug”
- 原則 1：加鎖的基本單位是 next-key lock。希望你還記得艇纺，next-key lock 是前開后閉區(qū)間婆芦。
- 原則 2：查找過程中訪問到的對象才會加鎖。
- 優(yōu)化 1：索引上的等值查詢喂饥，給唯一索引加鎖的時候，next-key lock 退化為行鎖肠鲫。
- 優(yōu)化 2：索引上的等值查詢员帮，向右遍歷時且最后一個值不滿足等值條件的時候，next-key lock 退化為間隙鎖导饲。
- 一個 bug：唯一索引上的范圍查詢會訪問到不滿足條件的第一個值為止捞高。
- 表的建表語句和初始化語句如下：
CREATE TABLE `t` (
`id` int(11) NOT NULL,
`c` int(11) DEFAULT NULL,
`d` int(11) DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `c` (`c`)
) ENGINE=InnoDB;
insert into t values(0,0,0),(5,5,5),
(10,10,10),(15,15,15),(20,20,20),(25,25,25);
案例一：等值查詢間隙鎖
等值查詢間隙鎖
- 由于表 t 中沒有 id=7 的記錄，所以用我們上面提到的加鎖規(guī)則判斷一下的話：
- 根據(jù)原則 1渣锦，加鎖單位是 next-key lock硝岗，session A 加鎖范圍就是 (5,10]；
- 同時根據(jù)優(yōu)化 2袋毙，這是一個等值查詢 (id=7)型檀，而 id=10 不滿足查詢條件，next-key lock 退化成間隙鎖听盖，因此最終加鎖的范圍是 (5,10)胀溺。
- 所以，session B 要往這個間隙里面插入 id=8 的記錄會被鎖住皆看，但是 session C 修改 id=10 這行是可以的仓坞。
案例二：非唯一索引等值鎖
只加在非唯一索引上的鎖
- 是不是有一種“該鎖的不鎖，不該鎖的亂鎖”的感覺腰吟？我們來分析一下吧无埃。
這里 session A 要給索引 c 上 c=5 的這一行加上讀鎖。- 根據(jù)原則 1，加鎖單位是 next-key lock嫉称，因此會給 (0,5]加上 next-key lock长已。
要注意 c 是普通索引，因此僅訪問 c=5 這一條記錄是不能馬上停下來的乱陡，需要向右遍歷溶弟，查到 c=10 才放棄。根據(jù)原則 2蒲稳，訪問到的都要加鎖氮趋，因此要給 (5,10]加 next-key lock。 - 但是同時這個符合優(yōu)化 2：等值判斷江耀，向右遍歷剩胁，最后一個值不滿足 c=5 這個等值條件，因此退化成間隙鎖 (5,10)祥国。
- 根據(jù)原則 2 昵观，只有訪問到的對象才會加鎖，這個查詢使用覆蓋索引舌稀，并不需要訪問主鍵索引啊犬，所以主鍵索引上沒有加任何鎖，這就是為什么 session B 的 update 語句可以執(zhí)行完成壁查。
- 但 session C 要插入一個 (7,7,7) 的記錄觉至，就會被 session A 的間隙鎖 (5,10) 鎖住。
- 根據(jù)原則 1，加鎖單位是 next-key lock嫉称，因此會給 (0,5]加上 next-key lock长已。
- 需要注意睡腿，在這個例子中语御，lock in share mode 只鎖覆蓋索引，但是如果是 for update 就不一樣了席怪。 執(zhí)行 for update 時应闯，系統(tǒng)會認(rèn)為你接下來要更新數(shù)據(jù)，因此會順便給主鍵索引上滿足條件的行加上行鎖挂捻。
- 這個例子說明碉纺，鎖是加在索引上的；同時刻撒，它給我們的指導(dǎo)是惜辑，如果你要用 lock in share mode 來給行加讀鎖避免數(shù)據(jù)被更新的話，就必須得繞過覆蓋索引的優(yōu)化疫赎，在查詢字段中加入索引中不存在的字段盛撑。
案例三：主鍵索引范圍鎖
- 你可以先思考一下這個問題：對于我們這個表 t，下面這兩條查詢語句捧搞，加鎖范圍相同嗎抵卫？
mysql> select * from t where id=10 for update;
mysql> select * from t where id>=10 and id<11 for update;
- 你可能會想狮荔，id 定義為 int 類型，這兩個語句就是等價的吧介粘？其實殖氏，它們并不完全等價。
- 在邏輯上姻采，這兩條查語句肯定是等價的雅采，但是它們的加鎖規(guī)則不太一樣。現(xiàn)在慨亲，我們就讓 session A 執(zhí)行第二個查詢語句婚瓜，來看看加鎖效果。
主鍵索引上范圍鎖
- 現(xiàn)在我們就用前面提到的加鎖規(guī)則刑棵，來分析一下 session A 會加什么鎖呢巴刻？
- 開始執(zhí)行的時候，要找到第一個 id=10 的行蛉签，因此本該是 next-key lock(5,10]胡陪。 根據(jù)優(yōu)化 1， 主鍵 id 上的等值條件碍舍，退化成行鎖柠座，只加了 id=10 這一行的行鎖。
- 范圍查找就往后繼續(xù)找片橡，找到 id=15 這一行停下來妈经，因此需要加 next-key lock(10,15]。
- 所以锻全，session A 這時候鎖的范圍就是主鍵索引上，行鎖 id=10 和 next-key lock(10,15]录煤。這樣鳄厌，session B 和 session C 的結(jié)果你就能理解了。
- 這里你需要注意一點妈踊，首次 session A 定位查找 id=10 的行的時候了嚎，是當(dāng)做等值查詢來判斷的，而向右掃描到 id=15 的時候廊营，用的是范圍查詢判斷歪泳。
案例四：非唯一索引范圍鎖
- 需要注意的是，與案例三不同的是露筒，案例四中查詢語句的 where 部分用的是字段 c呐伞。
非唯一索引范圍鎖
- 這次 session A 用字段 c 來判斷，加鎖規(guī)則跟案例三唯一的不同是：在第一次用 c=10 定位記錄的時候慎式，索引 c 上加了 (5,10]這個 next-key lock 后伶氢，由于索引 c 是非唯一索引趟径，沒有優(yōu)化規(guī)則，也就是說不會蛻變?yōu)樾墟i癣防，因此最終 sesion A 加的鎖是蜗巧，索引 c 上的 (5,10] 和 (10,15] 這兩個 next-key lock。
- 所以從結(jié)果上來看蕾盯，sesson B 要插入（8,8,8) 的這個 insert 語句時就被堵住了幕屹。
- 這里需要掃描到 c=15 才停止掃描，是合理的级遭，因為 InnoDB 要掃到 c=15望拖，才知道不需要繼續(xù)往后找了。
案例五：唯一索引范圍鎖 bug
加鎖bug
- session A 是一個范圍查詢装畅，按照原則 1 的話靠娱，應(yīng)該是索引 id 上只加 (10,15]這個 next-key lock，并且因為 id 是唯一鍵掠兄，所以循環(huán)判斷到 id=15 這一行就應(yīng)該停止了像云。
- 但是實現(xiàn)上，InnoDB 會往前掃描到第一個不滿足條件的行為止蚂夕，也就是 id=20迅诬。而且由于這是個范圍掃描，因此索引 id 上的 (15,20]這個 next-key lock 也會被鎖上婿牍。
- 所以你看到了侈贷，session B 要更新 id=20 這一行，是會被鎖住的等脂。同樣地俏蛮，session C 要插入 id=16 的一行，也會被鎖住上遥。
- 照理說搏屑，這里鎖住 id=20 這一行的行為，其實是沒有必要的粉楚。因為掃描到 id=15辣恋，就可以確定不用往后再找了。但實現(xiàn)上還是這么做了模软，因此我認(rèn)為這是個 bug伟骨。
- 官方 bug 系統(tǒng)上也有提到，但是并未被 verified燃异。
案例六：非唯一索引上存在"等值"的例子
- 接下來的例子携狭，是為了更好地說明“間隙”這個概念。這里回俐，給表 t 插入一條新記錄暑中。
mysql> insert into t values(30,10,30);
- 新插入的這一行 c=10壹瘟，也就是說現(xiàn)在表里有兩個 c=10 的行。那么鳄逾，這時候索引 c 上的間隙是什么狀態(tài)了呢稻轨？你要知道，由于非唯一索引上包含主鍵的值雕凹，所以是不可能存在“相同”的兩行的殴俱。
非唯一索引等值
- 可以看到，雖然有兩個 c=10枚抵，但是它們的主鍵值 id 是不同的（分別是 10 和 30）线欲，因此這兩個 c=10 的記錄之間，也是有間隙的汽摹。
- 圖中畫出了索引 c 上的主鍵 id李丰。為了跟間隙鎖的開區(qū)間形式進(jìn)行區(qū)別，用 (c=10,id=30) 這樣的形式逼泣，來表示索引上的一行趴泌。
- 這次我們用 delete 語句來驗證。注意拉庶，delete 語句加鎖的邏輯嗜憔，其實跟 select ... for update 是類似的，也就是我在文章開始總結(jié)的兩個“原則”氏仗、兩個“優(yōu)化”和一個“bug”吉捶。
delete示例
- 這時，session A 在遍歷的時候皆尔，先訪問第一個 c=10 的記錄呐舔。同樣地，根據(jù)原則 1慷蠕，這里加的是 (c=5,id=5) 到 (c=10,id=10) 這個 next-key lock珊拼。
- 然后，session A 向右查找砌们，直到碰到 (c=15,id=15) 這一行杆麸，循環(huán)才結(jié)束搁进。根據(jù)優(yōu)化 2浪感，這是一個等值查詢，向右查找到了不滿足條件的行饼问，所以會退化成 (c=10,id=10) 到 (c=15,id=15) 的間隙鎖影兽。
- 也就是說，這個 delete 語句在索引 c 上的加鎖范圍莱革，就是下圖中藍(lán)色區(qū)域覆蓋的部分峻堰。
delete加鎖示例
- 這個藍(lán)色區(qū)域左右兩邊都是虛線讹开，表示開區(qū)間，即 (c=5,id=5) 和 (c=15,id=15) 這兩行上都沒有鎖捐名。
案例七：limit 語句加鎖
limit語句加鎖
- 這個例子里旦万，session A 的 delete 語句加了 limit 2。你知道表 t 里 c=10 的記錄其實只有兩條镶蹋，因此加不加 limit 2成艘，刪除的效果都是一樣的，但是加鎖的效果卻不同贺归∠剑可以看到，session B 的 insert 語句執(zhí)行通過了拂酣，跟案例六的結(jié)果不同秋冰。
- 這是因為，案例七里的 delete 語句明確加了 limit 2 的限制婶熬，因此在遍歷到 (c=10, id=30) 這一行之后剑勾，滿足條件的語句已經(jīng)有兩條，循環(huán)就結(jié)束了尸诽。
- 因此甥材，索引 c 上的加鎖范圍就變成了從（c=5,id=5) 到（c=10,id=30) 這個前開后閉區(qū)間，如下圖所示：
帶limit加鎖效果
- 可以看到性含，(c=10,id=30）之后的這個間隙并沒有在加鎖范圍里洲赵，因此 insert 語句插入 c=12 是可以執(zhí)行成功的。
- 這個例子對我們實踐的指導(dǎo)意義就是商蕴，在刪除數(shù)據(jù)的時候盡量加 limit叠萍。這樣不僅可以控制刪除數(shù)據(jù)的條數(shù)，讓操作更安全绪商，還可以減小加鎖的范圍苛谷。
案例八：一個死鎖的例子
- 前面的例子中，我們在分析的時候格郁，是按照 next-key lock 的邏輯來分析的腹殿，因為這樣分析比較方便。最后我們再看一個案例例书，目的是說明：next-key lock 實際上是間隙鎖和行鎖加起來的結(jié)果锣尉。
你一定會疑惑，這個概念不是一開始就說了嗎决采？不要著急自沧，我們先來看下面這個例子：
操作序列示意圖
- 現(xiàn)在，我們按時間順序來分析一下為什么是這樣的結(jié)果树瞭。
- session A 啟動事務(wù)后執(zhí)行查詢語句加 lock in share mode拇厢，在索引 c 上加了 next-key lock(5,10] 和間隙鎖 (10,15)爱谁；
- session B 的 update 語句也要在索引 c 上加 next-key lock(5,10] ，進(jìn)入鎖等待孝偎；
- 然后 session A 要再插入 (8,8,8) 這一行访敌，被 session B 的間隙鎖鎖住。由于出現(xiàn)了死鎖衣盾，InnoDB 讓 session B 回滾捐顷。
- 你可能會問，session B 的 next-key lock 不是還沒申請成功嗎雨效？
- 其實是這樣的迅涮，session B 的“加 next-key lock(5,10] ”操作，實際上分成了兩步徽龟，先是加 (5,10) 的間隙鎖叮姑，加鎖成功；然后加 c=10 的行鎖据悔，這時候才被鎖住的传透。
- 也就是說，我們在分析加鎖規(guī)則的時候可以用 next-key lock 來分析极颓。但是要知道朱盐，具體執(zhí)行的時候，是要分成間隙鎖和行鎖兩段來執(zhí)行的菠隆。
小結(jié)
- 這里再次說明一下兵琳，我們上面的所有案例都是在可重復(fù)讀隔離級別 (repeatable-read) 下驗證的。同時骇径，可重復(fù)讀隔離級別遵守兩階段鎖協(xié)議躯肌，所有加鎖的資源，都是在事務(wù)提交或者回滾的時候才釋放的破衔。
- 在最后的案例中清女，你可以清楚地知道 next-key lock 實際上是由間隙鎖加行鎖實現(xiàn)的。如果切換到讀提交隔離級別 (read-committed) 的話晰筛，就好理解了嫡丙，過程中去掉間隙鎖的部分，也就是只剩下行鎖的部分读第。
- 另外曙博，在讀提交隔離級別下還有一個優(yōu)化，即：語句執(zhí)行過程中加上的行鎖卦方，在語句執(zhí)行完成后羊瘩，就要把“不滿足條件的行”上的行鎖直接釋放了泰佳，不需要等到事務(wù)提交盼砍。
- 也就是說尘吗，讀提交隔離級別下，鎖的范圍更小浇坐，鎖的時間更短睬捶，這也是不少業(yè)務(wù)都默認(rèn)使用讀提交隔離級別的原因。
- 不過近刘，希望看完本文后擒贸，大家可以對 next-key lock 的概念有更清晰的認(rèn)識，并且會用加鎖規(guī)則去判斷語句的加鎖范圍觉渴。
在業(yè)務(wù)需要使用可重復(fù)讀隔離級別的時候介劫，能夠更細(xì)致地設(shè)計操作數(shù)據(jù)庫的語句，解決幻讀問題的同時案淋，最大限度地提升系統(tǒng)并行處理事務(wù)的能力座韵。
