加鎖規(guī)則如下洽洁，這個規(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：唯一索引上的范圍查詢會訪問到不滿足條件的第一個值為止署浩。

接下來，我們的討論還是基于下面這個表 t：

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);

不等號條件里的等值查詢

• 有同學(xué)對“等值查詢”提出了疑問：等值查詢和“遍歷”有什么區(qū)別扫尺？為什么我們文章的例子里面筋栋，where 條件是不等號，這個過程里也有等值查詢正驻？
• 我們一起來看下這個例子二汛，分析一下這條查詢語句的加鎖范圍：

begin;
select * from t where id>9 and id<12 order by id desc for update;

• 利用上面的加鎖規(guī)則，我們知道這個語句的加鎖范圍是主鍵索引上的 (0,5]拨拓、(5,10]和 (10, 15)肴颊。也就是說，id=15 這一行渣磷，并沒有被加上行鎖婿着。為什么呢？
• 我們說加鎖單位是 next-key lock醋界，都是前開后閉區(qū)間竟宋，但是這里用到了優(yōu)化 2，即索引上的等值查詢形纺，向右遍歷的時候 id=15 不滿足條件丘侠，所以 next-key lock 退化為了間隙鎖 (10, 15)。
• 但是逐样，我們的查詢語句中 where 條件是大于號和小于號蜗字，這里的“等值查詢”又是從哪里來的呢打肝？
• 要知道，加鎖動作是發(fā)生在語句執(zhí)行過程中的挪捕，所以你在分析加鎖行為的時候粗梭，要從索引上的數(shù)據(jù)結(jié)構(gòu)開始。這里级零，再把這個過程拆解一下断医。
• 如下圖所示，是這個表的索引 id 的示意圖奏纪。

索引id示意圖

1. 首先這個查詢語句的語義是 order by id desc鉴嗤，要拿到滿足條件的所有行，優(yōu)化器必須先找到“第一個 id<12 的值”序调。
2. 這個過程是通過索引樹的搜索過程得到的醉锅，在引擎內(nèi)部，其實是要找到 id=12 的這個值炕置，只是最終沒找到荣挨，但找到了 (10,15) 這個間隙男韧。
3. 然后向左遍歷朴摊，在遍歷過程中，就不是等值查詢了此虑，會掃描到 id=5 這一行甚纲，所以會加一個 next-key lock (0,5]。

• 也就是說朦前，在執(zhí)行過程中介杆，通過樹搜索的方式定位記錄的時候，用的是“等值查詢”的方法韭寸。

等值查詢的過程

• 一起思考下春哨，下面這個語句的加鎖范圍是什么？

begin;
select id from t where c in(5,20,10) lock in share mode;

• 這條查詢語句里用的是 in恩伺，我們先來看這條語句的 explain 結(jié)果赴背。

in語句的explain結(jié)果

• 可以看到，這條 in 語句使用了索引 c 并且 rows=3晶渠，說明這三個值都是通過 B+ 樹搜索定位的凰荚。
• 在查找 c=5 的時候，先鎖住了 (0,5]褒脯。但是因為 c 不是唯一索引便瑟，為了確認(rèn)還有沒有別的記錄 c=5，就要向右遍歷番川，找到 c=10 才確認(rèn)沒有了到涂，這個過程滿足優(yōu)化 2脊框，所以加了間隙鎖 (5,10)。
• 同樣的养盗，執(zhí)行 c=10 這個邏輯的時候缚陷，加鎖的范圍是 (5,10] 和 (10,15)；執(zhí)行 c=20 這個邏輯的時候往核，加鎖的范圍是 (15,20] 和 (20,25)箫爷。
• 通過這個分析，我們可以知道聂儒，這條語句在索引 c 上加的三個記錄鎖的順序是：先加 c=5 的記錄鎖虎锚，再加 c=10 的記錄鎖，最后加 c=20 的記錄鎖衩婚。
• 你可能會說窜护，這個加鎖范圍，不就是從 (5,25) 中去掉 c=15 的行鎖嗎非春？為什么這么麻煩地分段說呢柱徙？
  • 這些鎖是“在執(zhí)行過程中一個一個加的”，而不是一次性加上去的奇昙。
  • 理解了這個加鎖過程之后护侮，我們就可以來分析下面例子中的死鎖問題了。
• 如果同時有另外一個語句储耐，是這么寫的：

select id from t where c in(5,20,10) order by c desc for update;

• 此時的加鎖范圍羊初，又是什么呢？
  • 我們現(xiàn)在都知道間隙鎖是不互鎖的什湘，但是這兩條語句都會在索引 c 上的 c=5长赞、10、20 這三行記錄上加記錄鎖闽撤。
  • 這里你需要注意一下得哆，由于語句里面是 order by c desc， 這三個記錄鎖的加鎖順序哟旗，是先鎖 c=20贩据，然后 c=10，最后是 c=5热幔。
• 也就是說乐设，這兩條語句要加鎖相同的資源，但是加鎖順序相反绎巨。當(dāng)這兩條語句并發(fā)執(zhí)行的時候近尚，就可能出現(xiàn)死鎖。
• 關(guān)于死鎖的信息场勤，MySQL 只保留了最后一個死鎖的現(xiàn)場戈锻，但這個現(xiàn)場還是不完備的歼跟。接下來我們分析下這個死鎖場景

怎么看死鎖？

• 下圖是在出現(xiàn)死鎖后格遭，執(zhí)行 show engine innodb status 命令得到的部分輸出哈街。這個命令會輸出很多信息，有一節(jié) LATESTDETECTED DEADLOCK拒迅，就是記錄的最后一次死鎖信息骚秦。

死鎖現(xiàn)場

• 我們來看看這圖中的幾個關(guān)鍵信息。

1. 這個結(jié)果分成三部分：
   • (1) TRANSACTION璧微，是第一個事務(wù)的信息作箍；
   • (2) TRANSACTION，是第二個事務(wù)的信息前硫；
   • WE ROLL BACK TRANSACTION (1)胞得，是最終的處理結(jié)果，表示回滾了第一個事務(wù)屹电。
2. 第一個事務(wù)的信息中：
   • WAITING FOR THIS LOCK TO BE GRANTED阶剑，表示的是這個事務(wù)在等待的鎖信息；
   • index c of table test.t危号，說明在等的是表 t 的索引 c 上面的鎖牧愁；
   • lock mode S waiting 表示這個語句要自己加一個讀鎖，當(dāng)前的狀態(tài)是等待中葱色；
   • Record lock 說明這是一個記錄鎖递宅；
   • n_fields 2 表示這個記錄是兩列娘香，也就是字段 c 和主鍵字段 id苍狰；
   • 0: len 4; hex 0000000a; asc ;; 是第一個字段，也就是 c烘绽。值是十六進(jìn)制 a淋昭，也就是 10；
   • 1: len 4; hex 0000000a; asc ;; 是第二個字段安接，也就是主鍵 id翔忽，值也是 10；
   • 這兩行里面的 asc 表示的是盏檐，接下來要打印出值里面的“可打印字符”歇式，但 10 不是可打印字符，因此就顯示空格胡野。
   • 第一個事務(wù)信息就只顯示出了等鎖的狀態(tài)材失，在等待 (c=10,id=10) 這一行的鎖。
   • 當(dāng)然你是知道的硫豆，既然出現(xiàn)死鎖了龙巨，就表示這個事務(wù)也占有別的鎖笼呆，但是沒有顯示出來。別著急旨别，我們從第二個事務(wù)的信息中推導(dǎo)出來诗赌。
3. 第二個事務(wù)顯示的信息要多一些：
   • “ HOLDS THE LOCK(S)”用來顯示這個事務(wù)持有哪些鎖；
   • index c of table test.t 表示鎖是在表 t 的索引 c 上秸弛；
   • hex 0000000a 和 hex 00000014 表示這個事務(wù)持有 c=10 和 c=20 這兩個記錄鎖铭若；
   • WAITING FOR THIS LOCK TO BE GRANTED，表示在等 (c=5,id=5) 這個記錄鎖递览。

• 從上面這些信息中奥喻，我們就知道：
  • “l(fā)ock in share mode”的這條語句，持有 c=5 的記錄鎖非迹，在等 c=10 的鎖环鲤；
  • “for update”這個語句，持有 c=20 和 c=10 的記錄鎖憎兽，在等 c=5 的記錄鎖冷离。
• 因此導(dǎo)致了死鎖。這里纯命，我們可以得到兩個結(jié)論：
  • 由于鎖是一個個加的西剥，要避免死鎖，對同一組資源亿汞，要按照盡量相同的順序訪問瞭空；
  • 在發(fā)生死鎖的時刻，for update 這條語句占有的資源更多疗我，回滾成本更大咆畏，所以 InnoDB 選擇了回滾成本更小的 lock in share mode 語句，來回滾吴裤。

怎么看鎖等待旧找？

• 看完死鎖，我們再來看一個鎖等待的例子麦牺。

delete導(dǎo)致間隙變化

• 可以看到钮蛛，由于 session A 并沒有鎖住 c=10 這個記錄，所以 session B 刪除 id=10 這一行是可以的剖膳。但是之后魏颓，session B 再想 insert id=10 這一行回去就不行了。
• 現(xiàn)在我們一起看一下此時 show engine innodb status 的結(jié)果吱晒，看看能不能給我們一些提示甸饱。鎖信息是在這個命令輸出結(jié)果的 TRANSACTIONS 這一節(jié)。

鎖等待信息

• 我們來看幾個關(guān)鍵信息枕荞。

1. index PRIMARY of table test.t 柜候，表示這個語句被鎖住是因為表 t 主鍵上的某個鎖搞动。
2. lock_mode X locks gap before rec insert intention waiting 這里有幾個信息：
   • insert intention 表示當(dāng)前線程準(zhǔn)備插入一個記錄，這是一個插入意向鎖渣刷。為了便于理解鹦肿，你可以認(rèn)為它就是這個插入動作本身。
   • gap before rec 表示這是一個間隙鎖辅柴，而不是記錄鎖箩溃。
3. 那么這個 gap 是在哪個記錄之前的呢？接下來的 0~4 這 5 行的內(nèi)容就是這個記錄的信息碌嘀。
4. n_fields 5 也表示了涣旨，這一個記錄有 5 列：
   • 0: len 4; hex 0000000f; asc ;; 第一列是主鍵 id 字段，十六進(jìn)制 f 就是 id=15股冗。所以霹陡，這時我們就知道了，這個間隙就是 id=15 之前的止状，因為 id=10 已經(jīng)不存在了烹棉，它表示的就是 (5,15)栋艳。
   • 1: len 6; hex 000000000513; asc ;; 第二列是長度為 6 字節(jié)的事務(wù) id惨险，表示最后修改這一行的是 trx id 為 1299 的事務(wù)。
   • 2: len 7; hex b0000001250134; asc % 4;; 第三列長度為 7 字節(jié)的回滾段信息嘶摊〖停可以看到伏社，這里的 acs 后面有顯示內(nèi)容 (% 和 4)，這是因為剛好這個字節(jié)是可打印字符塔淤。
   • 后面兩列是 c 和 d 的值摘昌，都是 15。

• 因此凯沪，我們就知道了第焰，由于 delete 操作把 id=10 這一行刪掉了买优，原來的兩個間隙 (5,10)妨马、(10,15）變成了一個 (5,15)。
• 說到這里杀赢，你可以聯(lián)合起來再思考一下這兩個現(xiàn)象之間的關(guān)聯(lián)：
  • session A 執(zhí)行完 select 語句后烘跺，什么都沒做，但它加鎖的范圍突然“變大”了脂崔；
  • 舉例說明滤淳，我們執(zhí)行 select * from t where c>=15 and c<=20 order by c desc lock in share mode; 向左掃描到 c=10 的時候，要把 (5, 10]鎖起來砌左。
  • 也就是說脖咐，所謂“間隙”铺敌，其實根本就是由“這個間隙右邊的那個記錄”定義的。

update 的例子

• 看過了 insert 和 delete 的加鎖例子屁擅，我們再來看一個 update 語句的案例

update的例子

• 你可以自己分析一下偿凭，session A 的加鎖范圍是索引 c 上的 (5,10]、(10,15]派歌、(15,20]弯囊、(20,25]和 (25,supremum]。

注意：根據(jù) c>5 查到的第一個記錄是 c=10胶果，因此不會加 (0,5]這個 next-key lock匾嘱。

• 之后 session B 的第一個 update 語句，要把 c=5 改成 c=1早抠，你可以理解為兩步：
  1. 插入 (c=1, id=5) 這個記錄霎烙；
  2. 刪除 (c=5, id=5) 這個記錄。
• 按照我們上一節(jié)說的蕊连，索引 c 上 (5,10) 間隙是由這個間隙右邊的記錄吼过，也就是 c=10 定義的。所以通過這個操作咪奖，session A 的加鎖范圍變成了圖 7 所示的樣子：

session 修改后盗忱，session A的加鎖范圍

• 好，接下來 session B 要執(zhí)行 update t set c = 5 where c = 1 這個語句了羊赵，一樣地可以拆成兩步：
  1. 插入 (c=5, id=5) 這個記錄趟佃；
  2. 刪除 (c=1, id=5) 這個記錄。
• 第一步試圖在已經(jīng)加了間隙鎖的 (1,10) 中插入數(shù)據(jù)昧捷，所以就被堵住了闲昭。

小結(jié)

• 再次跟你復(fù)習(xí)了加鎖規(guī)則。并且靡挥，重點說明了序矩，分析加鎖范圍時，一定要配合語句執(zhí)行邏輯來進(jìn)行跋破。
• 每個想認(rèn)真了解 MySQL 原理的同學(xué)簸淀，應(yīng)該都要能夠做到：通過 explain 的結(jié)果，就能夠腦補出一個 SQL 語句的執(zhí)行流程毒返。達(dá)到這樣的程度租幕，才算是對索引組織表、索引拧簸、鎖的概念有了比較清晰的認(rèn)識劲绪。你同樣也可以用這個方法，來驗證自己對這些知識點的掌握程度。
• 在分析這些加鎖規(guī)則的過程中贾富，這里順便跟你介紹了怎么看 show engine innodb status 輸出結(jié)果中的事務(wù)信息和死鎖信息歉眷，希望這些內(nèi)容對你以后分析現(xiàn)場能有所幫助。