這篇文章將對(duì)一些常見(jiàn)的 SQL 語(yǔ)句進(jìn)行加鎖分析，看看我們平時(shí)執(zhí)行的那些 SQL 都會(huì)加什么鎖。只有對(duì)我們所寫(xiě)的 SQL 語(yǔ)句加鎖過(guò)程了如指掌，才能在遇到死鎖問(wèn)題時(shí)倒推出是什么鎖導(dǎo)致的問(wèn)題。在前面的博客中我們已經(jīng)學(xué)習(xí)了 MySQL 下不同的鎖模式和鎖類(lèi)型，我們要特別注意它們的兼容矩陣，熟悉哪些鎖是不兼容的，這些不兼容的鎖往往就是導(dǎo)致死鎖的罪魁禍?zhǔn)?。總體來(lái)說(shuō)，MySQL 中的鎖可以分成兩個(gè)粒度：表鎖和行鎖，表鎖有：表級(jí)讀鎖，表級(jí)寫(xiě)鎖，讀意向鎖，寫(xiě)意向鎖，自增鎖；行鎖有：讀記錄鎖，寫(xiě)記錄鎖，間隙鎖，Next-key 鎖，插入意向鎖。不出意外，絕大多數(shù)的死鎖問(wèn)題都是由這些鎖之間的沖突導(dǎo)致的。
我們知道，不同的隔離級(jí)別加鎖也是不一樣的，譬如 RR 隔離級(jí)別下有間隙鎖和 Next-key 鎖，這在 RC 隔離級(jí)別下是沒(méi)有的（也有例外），所以在對(duì) SQL 進(jìn)行加鎖分析時(shí)，必須得知道數(shù)據(jù)庫(kù)的隔離級(jí)別。由于 RR 和 RC 用的比較多，所以這篇博客只對(duì)這兩種隔離級(jí)別做分析。
一、基本的加鎖規(guī)則
雖然 MySQL 的鎖各式各樣，但是有些基本的加鎖原則是保持不變的，譬如：快照讀是不加鎖的，更新語(yǔ)句肯定是加排它鎖的，RC 隔離級(jí)別是沒(méi)有間隙鎖的等等。這些規(guī)則整理如下，后面就不再重復(fù)介紹了：
常見(jiàn)語(yǔ)句的加鎖
SELECT ... 語(yǔ)句正常情況下為快照讀，不加鎖；
SELECT ... LOCK IN SHARE MODE 語(yǔ)句為當(dāng)前讀，加 S 鎖；
SELECT ... FOR UPDATE 語(yǔ)句為當(dāng)前讀，加 X 鎖；
常見(jiàn)的 DML 語(yǔ)句（如 INSERT、DELETE、UPDATE）為當(dāng)前讀，加 X 鎖；
常見(jiàn)的 DDL 語(yǔ)句（如 ALTER、CREATE 等）加表級(jí)鎖，且這些語(yǔ)句為隱式提交，不能回滾；
表鎖
表鎖（分 S 鎖和 X 鎖）
意向鎖（分 IS 鎖和 IX 鎖）
自增鎖（一般見(jiàn)不到，只有在 innodb_autoinc_lock_mode = 0 或者 Bulk inserts 時(shí)才可能有）
行鎖
記錄鎖（分 S 鎖和 X 鎖）
間隙鎖（分 S 鎖和 X 鎖）
Next-key 鎖（分 S 鎖和 X 鎖）
插入意向鎖
行鎖分析
行鎖都是加在索引上的，最終都會(huì)落在聚簇索引上；
加行鎖的過(guò)程是一條一條記錄加的；
鎖沖突
S 鎖和 S 鎖兼容，X 鎖和 X 鎖沖突，X 鎖和 S 鎖沖突；
表鎖和行鎖的沖突矩陣參見(jiàn)前面的博客 了解常見(jiàn)的鎖類(lèi)型；
不同隔離級(jí)別下的鎖
上面說(shuō) SELECT ... 語(yǔ)句正常情況下為快照讀，不加鎖；但是在 Serializable 隔離級(jí)別下為當(dāng)前讀，加 S 鎖；
RC 隔離級(jí)別下沒(méi)有間隙鎖和 Next-key 鎖（特殊情況下也會(huì)有：purge + unique key）；
不同隔離級(jí)別下鎖的區(qū)別，參見(jiàn)前面的博客 學(xué)習(xí)事務(wù)與隔離級(jí)別；
二、簡(jiǎn)單 SQL 的加鎖分析
何登成前輩在他的博客《MySQL 加鎖處理分析》中對(duì)一些常見(jiàn)的 SQL 加鎖進(jìn)行了細(xì)致的分析，這篇博客可以說(shuō)是網(wǎng)上介紹 MySQL 加鎖分析的一個(gè)范本，網(wǎng)上幾乎所有關(guān)于加鎖分析的博客都是參考了這篇博客，勘稱(chēng)經(jīng)典，強(qiáng)烈推薦。我這里也不例外，只是在他的基礎(chǔ)上進(jìn)行了一些整理和總結(jié)。
我們使用下面這張 students 表作為實(shí)例，其中 id 為主鍵，no（學(xué)號(hào)）為二級(jí)唯一索引，name（姓名）和 age（年齡）為二級(jí)非唯一索引，score（學(xué)分）無(wú)索引。
這一節(jié)我們只分析最簡(jiǎn)單的一種 SQL，它只包含一個(gè) WHERE 條件，等值查詢(xún)或范圍查詢(xún)。雖然 SQL 非常簡(jiǎn)單，但是針對(duì)不同類(lèi)型的列，我們還是會(huì)面對(duì)各種情況：
聚簇索引，查詢(xún)命中：UPDATE students SET score = 100 WHERE id = 15;
聚簇索引，查詢(xún)未命中：UPDATE students SET score = 100 WHERE id = 16;
二級(jí)唯一索引，查詢(xún)命中：UPDATE students SET score = 100 WHERE no = 'S0003';
二級(jí)唯一索引，查詢(xún)未命中：UPDATE students SET score = 100 WHERE no = 'S0008';
二級(jí)非唯一索引，查詢(xún)命中：UPDATE students SET score = 100 WHERE name = 'Tom';
二級(jí)非唯一索引，查詢(xún)未命中：UPDATE students SET score = 100 WHERE name = 'John';
無(wú)索引：UPDATE students SET score = 100 WHERE score = 22;
聚簇索引，范圍查詢(xún)：UPDATE students SET score = 100 WHERE id <= 20;
二級(jí)索引，范圍查詢(xún)：UPDATE students SET score = 100 WHERE age <= 23;
修改索引值：UPDATE students SET name = 'John' WHERE id = 15;
2.1 聚簇索引，查詢(xún)命中
語(yǔ)句 UPDATE students SET score = 100 WHERE id = 15 在 RC 和 RR 隔離級(jí)別下加鎖情況一樣，都是對(duì) id 這個(gè)聚簇索引加 X 鎖，如下：
2.2 聚簇索引，查詢(xún)未命中
如果查詢(xún)未命中紀(jì)錄，在 RC 和 RR 隔離級(jí)別下加鎖是不一樣的，因?yàn)?RR 有 GAP 鎖。語(yǔ)句 UPDATE students SET score = 100 WHERE id = 16 在 RC 和 RR 隔離級(jí)別下的加鎖情況如下（RC 不加鎖）：
2.3 二級(jí)唯一索引，查詢(xún)命中
語(yǔ)句 UPDATE students SET score = 100 WHERE no = 'S0003' 命中二級(jí)唯一索引，上一篇博客中我們介紹了索引的結(jié)構(gòu)，我們知道二級(jí)索引的葉子節(jié)點(diǎn)中保存了主鍵索引的位置，在給二級(jí)索引加鎖的時(shí)候，主鍵索引也會(huì)一并加鎖。這個(gè)在 RC 和 RR 兩種隔離級(jí)別下沒(méi)有區(qū)別：
那么，為什么主鍵索引上的記錄也要加鎖呢？因?yàn)橛锌赡芷渌聞?wù)會(huì)根據(jù)主鍵對(duì) students 表進(jìn)行更新，如：UPDATE students SET score = 100 WHERE id = 20，試想一下，如果主鍵索引沒(méi)有加鎖，那么顯然會(huì)存在并發(fā)問(wèn)題。
2.4 二級(jí)唯一索引，查詢(xún)未命中
如果查詢(xún)未命中紀(jì)錄，和 2.2 情況一樣，RR 隔離級(jí)別會(huì)加 GAP 鎖，RC 無(wú)鎖。語(yǔ)句 UPDATE students SET score = 100 WHERE no = 'S0008' 加鎖情況如下：
這種情況下只會(huì)在二級(jí)索引加鎖，不會(huì)在聚簇索引上加鎖。
2.5 二級(jí)非唯一索引，查詢(xún)命中
如果查詢(xún)命中的是二級(jí)非唯一索引，在 RR 隔離級(jí)別下，還會(huì)加 GAP 鎖。語(yǔ)句 UPDATE students SET score = 100 WHERE name = 'Tom' 加鎖如下：
為什么非唯一索引會(huì)加 GAP 鎖，而唯一索引不用加 GAP 鎖呢？原因很簡(jiǎn)單，GAP 鎖的作用是為了解決幻讀，防止其他事務(wù)插入相同索引值的記錄，而唯一索引和主鍵約束都已經(jīng)保證了該索引值肯定只有一條記錄，所以無(wú)需加 GAP 鎖。
這里還有一點(diǎn)要注意一下，數(shù)一數(shù)右圖中的鎖你可能會(huì)覺(jué)得一共加了 7 把鎖，實(shí)際情況不是，要注意的是 (Tom, 37) 上的記錄鎖和它前面的 GAP 鎖合起來(lái)是一個(gè) Next-key 鎖，這個(gè)鎖加在 (Tom, 37) 這個(gè)索引上，另外 (Tom, 49) 上也有一把 Next-key 鎖。那么最右邊的 GAP 鎖加在哪呢？右邊已經(jīng)沒(méi)有任何記錄了啊。其實(shí)，在 InnoDb 存儲(chǔ)引擎里，每個(gè)數(shù)據(jù)頁(yè)中都會(huì)有兩個(gè)虛擬的行記錄，用來(lái)限定記錄的邊界，分別是：Infimum Record 和 Supremum Record，Infimum 是比該頁(yè)中任何記錄都要小的值，而 Supremum 比該頁(yè)中最大的記錄值還要大，這兩條記錄在創(chuàng)建頁(yè)的時(shí)候就有了，并且不會(huì)刪除。上面右邊的 GAP 鎖就是加在 Supremum Record 上。所以說(shuō)，上面右圖中共有 2 把 Next-key 鎖，1 把 GAP 鎖，2 把記錄鎖，一共 5 把鎖。
2.6 二級(jí)非唯一索引，查詢(xún)未命中
如果查詢(xún)未命中紀(jì)錄，和 2.2、2.4 情況一樣，RR 隔離級(jí)別會(huì)加 GAP 鎖，RC 無(wú)鎖。語(yǔ)句 UPDATE students SET score = 100 WHERE name = 'John' 加鎖情況如下：
[圖片上傳失敗...(image-617e92-1600263853053)]
2.7 無(wú)索引
如果 WHERE 條件不能走索引，MySQL 會(huì)如何加鎖呢？有的人說(shuō)會(huì)在表上加 X 鎖，也有人說(shuō)會(huì)根據(jù) WHERE 條件將篩選出來(lái)的記錄在聚簇索引上加上 X 鎖，那么究竟如何，我們看下圖：
在沒(méi)有索引的時(shí)候，只能走聚簇索引，對(duì)表中的記錄進(jìn)行全表掃描。在 RC 隔離級(jí)別下會(huì)給所有記錄加行鎖，在 RR 隔離級(jí)別下，不僅會(huì)給所有記錄加行鎖，所有聚簇索引和聚簇索引之間還會(huì)加上 GAP 鎖。
語(yǔ)句 UPDATE students SET score = 100 WHERE score = 22 滿(mǎn)足條件的雖然只有 1 條記錄，但是聚簇索引上所有的記錄，都被加上了 X 鎖。那么，為什么不是只在滿(mǎn)足條件的記錄上加鎖呢？這是由于 MySQL 的實(shí)現(xiàn)決定的。如果一個(gè)條件無(wú)法通過(guò)索引快速過(guò)濾，那么存儲(chǔ)引擎層面就會(huì)將所有記錄加鎖后返回，然后由 MySQL Server 層進(jìn)行過(guò)濾，因此也就把所有的記錄都鎖上了。
不過(guò)在實(shí)際的實(shí)現(xiàn)中，MySQL 有一些改進(jìn)，如果是 RC 隔離級(jí)別，在 MySQL Server 過(guò)濾條件發(fā)現(xiàn)不滿(mǎn)足后，會(huì)調(diào)用 unlock_row 方法，把不滿(mǎn)足條件的記錄鎖釋放掉（違背了 2PL 的約束）。這樣做可以保證最后只會(huì)持有滿(mǎn)足條件記錄上的鎖，但是每條記錄的加鎖操作還是不能省略的。如果是 RR 隔離級(jí)別，一般情況下 MySQL 是不能這樣優(yōu)化的，除非設(shè)置了 innodb_locks_unsafe_for_binlog 參數(shù)，這時(shí)也會(huì)提前釋放鎖，并且不加 GAP 鎖，這就是所謂的 semi-consistent read。
2.8 聚簇索引，范圍查詢(xún)
上面所介紹的各種情況其實(shí)都是非常常見(jiàn)的 SQL，它們有一個(gè)特點(diǎn)：全部都只有一個(gè) WHERE 條件，并且都是等值查詢(xún)。那么問(wèn)題來(lái)了，如果不是等值查詢(xún)而是范圍查詢(xún)，加鎖情況會(huì)怎么樣呢？有人可能會(huì)覺(jué)得這很簡(jiǎn)單，根據(jù)上面的加鎖經(jīng)驗(yàn)，我們只要給查詢(xún)范圍內(nèi)的所有記錄加上鎖即可，如果隔離級(jí)別是 RR，所有記錄之間再加上間隙鎖。事實(shí)究竟如何，我們看下面的圖：
SQL 語(yǔ)句為 UPDATE students SET score = 100 WHERE id <= 20，按理說(shuō)我們只需要將 id = 20、18、15 三條記錄鎖住即可，但是看右邊的圖，在 RR 隔離級(jí)別下，我們還把 id = 30 這條記錄以及 (20, 30] 之間的間隙也鎖起來(lái)了，很顯然這是一個(gè) Next-key 鎖。如果 WHERE 條件是 id < 20，則會(huì)把 id = 20 這條記錄鎖住。為什么會(huì)這樣我也不清楚，網(wǎng)上搜了很久，有人說(shuō)是為了防止幻讀，但 id 是唯一主鍵，(20, 30] 之間是不可能再插入一條 id = 20 的，所以具體的原因還需要再分析下，如果你知道，還請(qǐng)不吝賜教。
所以對(duì)于范圍查詢(xún)，如果 WHERE 條件是 id <= N，那么 N 后一條記錄也會(huì)被加上 Next-key 鎖；如果條件是 id < N，那么 N 這條記錄會(huì)被加上 Next-key 鎖。另外，如果 WHERE 條件是 id >= N，只會(huì)給 N 加上記錄鎖，以及給比 N 大的記錄加鎖，不會(huì)給 N 前一條記錄加鎖；如果條件是 id > N，也不會(huì)鎖前一條記錄，連 N 這條記錄都不會(huì)鎖。
====================== 11月26號(hào)補(bǔ)充 =========================
我在做實(shí)驗(yàn)的時(shí)候發(fā)現(xiàn)，在 RR 隔離級(jí)別，條件是 id >= 20，有時(shí)會(huì)對(duì) id < 20 的記錄加鎖，有時(shí)候又不加，感覺(jué)找不到任何規(guī)律，請(qǐng)以實(shí)際情況為準(zhǔn)。我對(duì)范圍查詢(xún)的加鎖原理還不是很明白，后面有時(shí)間再仔細(xì)研究下，也歡迎有興趣的同學(xué)一起討論下。
下面是我做的一個(gè)簡(jiǎn)單的實(shí)驗(yàn)，表很簡(jiǎn)單，只有一列主鍵 id：
mysql> show create table t1;
+-------+--------------------------------------------+
| Table | Create Table |
+-------+--------------------------------------------+
| t1 | CREATE TABLE `t1` ( |
| | `id` int(11) NOT NULL AUTO_INCREMENT, |
| | PRIMARY KEY (`id`) |
| | ) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
+-------+--------------------------------------------+
表里一共三條數(shù)據(jù)：
mysql> select * from t1;
+----+
| id |
+----+
| 2 |
| 4 |
| 6 |
+----+
3 rows in set (0.00 sec)
執(zhí)行 delete from t1 where id > 2 時(shí)加鎖情況是：(2, 4], (4, 6], (6, +∞) 執(zhí)行 select * from t1 where id > 2 for update 時(shí)加鎖情況是：(-∞, 2], (2, 4], (4, 6], (6, +∞) 可見(jiàn) select for update 和 delete 的加鎖還是有所區(qū)別的，至于 select for update 為什么加 (-∞, 2] 這個(gè)鎖，我還是百思不得其解。后來(lái)無(wú)意中給表 t1 加了一個(gè)字段 a int(11) NOT NULL，竟然發(fā)現(xiàn) select * from t1 where id > 2 for update 就不會(huì)給 (-∞, 2] 加鎖了，真的非常奇怪。
經(jīng)過(guò)幾天的搜索，終于找到了一個(gè)像樣的解釋?zhuān)ǖ缓萌プC實(shí)）：當(dāng)數(shù)據(jù)表中數(shù)據(jù)非常少時(shí)，譬如上面那個(gè)的例子，select ... [lock in share mode | for update] 語(yǔ)句會(huì)走全表掃描，這樣表中所有記錄都會(huì)被鎖住，這就是 (-∞, 2] 被鎖的原因。而 delete 語(yǔ)句并不會(huì)走全表掃描。
2.9 二級(jí)索引，范圍查詢(xún)
然后我們把范圍查詢(xún)應(yīng)用到二級(jí)非唯一索引上來(lái)，SQL 語(yǔ)句為：UPDATE students SET score = 100 WHERE age <= 23，加鎖情況如下圖所示：
可以看出和聚簇索引的范圍查詢(xún)一樣，除了 WHERE 條件范圍內(nèi)的記錄加鎖之外，后面一條記錄也會(huì)加上 Next-key 鎖，這里有意思的一點(diǎn)是，盡管滿(mǎn)足 age = 24 的記錄有兩條，但只有第一條被加鎖，第二條沒(méi)有加鎖，并且第一條和第二條之間也沒(méi)有加鎖。
2.10 修改索引值
這種情況比較容易理解，WHERE 部分的索引加鎖原則和上面介紹的一樣，多的是 SET 部分的加鎖。譬如 UPDATE students SET name = 'John' WHERE id = 15 不僅在 id = 15 記錄上加鎖之外，還會(huì)在 name = 'Bob'（原值）和 name = 'John'（新值） 上加鎖。示意圖如下（<span style='color:red;'>此處理解有誤，參見(jiàn)下面的評(píng)論區(qū)</span>）：
RC 和 RR 沒(méi)有區(qū)別。
三、復(fù)雜條件加鎖分析
前面的例子都是非常簡(jiǎn)單的 SQL，只包含一個(gè) WHERE 條件，并且是等值查詢(xún)，當(dāng) SQL 語(yǔ)句中包含多個(gè)條件時(shí)，對(duì)索引的分析就相當(dāng)重要了。因?yàn)槲覀冎佬墟i最終都是加在索引上的，如果我們連執(zhí)行 SQL 語(yǔ)句時(shí)會(huì)使用哪個(gè)索引都不知道，又怎么去分析這個(gè) SQL 所加的鎖呢？
MySQL 的索引是一個(gè)很復(fù)雜的話(huà)題，甚至可以寫(xiě)一本書(shū)出來(lái)了。這里就只是學(xué)習(xí)一下在對(duì)復(fù)雜 SQL 加鎖分析之前如何先對(duì)索引進(jìn)行分析。譬如下面這樣的 SQL：
1
mysql> DELETE FROM students WHERE name = 'Tom' AND age = 22;
其中 name 和 age 兩個(gè)字段都是索引，那么該如何加鎖？這其實(shí)取決于 MySQL 用哪個(gè)索引?？梢杂?EXPLAIN 命令分析 MySQL 是如何執(zhí)行這條 SQL 的，通過(guò)這個(gè)命令可以知道 MySQL 會(huì)使用哪些索引以及怎么用索引來(lái)執(zhí)行 SQL 的，只有執(zhí)行會(huì)用到的索引才有可能被加鎖，沒(méi)有使用的索引是不加鎖的，這里有一篇 EXPLAIN 的博客可以參考。也可以使用 MySQL 的 optimizer_trace 功能 來(lái)對(duì) SQL 進(jìn)行分析，它支持將執(zhí)行的 SQL 的查詢(xún)計(jì)劃樹(shù)記錄下來(lái)，這個(gè)稍微有點(diǎn)難度，有興趣的同學(xué)可以研究下。那么 MySQL 是如何選擇合適的索引呢？其實(shí) MySQL 會(huì)給每一個(gè)索引一個(gè)指標(biāo)，叫做索引的選擇性，這個(gè)值越高表示使用這個(gè)索引能最大程度的過(guò)濾更多的記錄，關(guān)于這個(gè)，又是另一個(gè)話(huà)題了。
當(dāng)然，從兩個(gè)索引中選擇一個(gè)索引來(lái)用，這種情況的加鎖分析和我們上一節(jié)討論的情形并沒(méi)有本質(zhì)的區(qū)別，只需要將那個(gè)沒(méi)有用索引的 WHERE 條件當(dāng)成普通的過(guò)濾條件就好了。這里我們會(huì)把用到的索引稱(chēng)為 Index Key，而另一個(gè)條件稱(chēng)為 Table Filter。譬如這里如果用到的索引為 age，那么 age 就是 Index Key，而 name = 'Tom' 就是 Table Filter。Index Key 又分為 First Key 和 Last Key，如果 Index Key 是范圍查詢(xún)的話(huà)，如下面的例子：
1
mysql> DELETE FROM students WHERE name = 'Tom' AND age > 22 AND age < 25;
其中 First Key 為 age > 22，Last Key 為 age < 25。
所以我們?cè)诩渔i分析時(shí)，只需要確定 Index Key 即可，鎖是加在 First Key 和 Last Key 之間的記錄上的，如果隔離級(jí)別為 RR，同樣會(huì)有間隙鎖。要注意的是，當(dāng)索引為復(fù)合索引時(shí)，Index Key 可能會(huì)有多個(gè)，何登成的這篇博客《SQL中的where條件，在數(shù)據(jù)庫(kù)中提取與應(yīng)用淺析》 詳細(xì)介紹了如何從一個(gè)復(fù)雜的 WHERE 條件中提取出 Index Key，推薦一讀。這里 也有一篇博客介紹了 MySQL 是如何利用索引的。
當(dāng)索引為復(fù)合索引時(shí)，不僅可能有多個(gè) Index Key，而且還可能有 Index Filter。所謂 Index Filter，就是復(fù)合索引中除 Index Key 之外的其他可用于過(guò)濾的條件。如果 MySQL 是 5.6 之前的版本，Index Filter 和 Table Filter 沒(méi)有區(qū)別，統(tǒng)統(tǒng)將 Index First Key 與 Index Last Key 范圍內(nèi)的索引記錄，回表讀取完整記錄，然后返回給 MySQL Server 層進(jìn)行過(guò)濾。而在 MySQL 5.6 之后，Index Filter 與 Table Filter 分離，Index Filter 下降到 InnoDB 的索引層面進(jìn)行過(guò)濾，減少了回表與返回 MySQL Server 層的記錄交互開(kāi)銷(xiāo)，提高了SQL的執(zhí)行效率，這就是傳說(shuō)中的 ICP（Index Condition Pushdown），使用 Index Filter 過(guò)濾不滿(mǎn)足條件的記錄，無(wú)需加鎖。
這里引用何登成前輩博客中的一個(gè)例子：
可以看到 pubtime > 1 and pubtime < 20 為 Index First Key 和 Index Last Key，MySQL 會(huì)在這個(gè)范圍內(nèi)加上記錄鎖和間隙鎖；userid = 'hdc' 為 Index Filter，這個(gè)過(guò)濾條件可以在索引層面就可以過(guò)濾掉一條記錄，因此如果數(shù)據(jù)庫(kù)支持 ICP 的話(huà)，(4, yyy, 3) 這條記錄就不會(huì)加鎖；comment is not NULL 為 Table Filter，雖然這個(gè)條件也可以過(guò)濾一條記錄，但是它不能在索引層面過(guò)濾，而是在根據(jù)索引讀取了整條記錄之后才過(guò)濾的，因此加鎖并不能省略。
四、DELETE 語(yǔ)句加鎖分析
一般來(lái)說(shuō)，DELETE 的加鎖和 SELECT FOR UPDATE 或 UPDATE 并沒(méi)有太大的差異，DELETE 語(yǔ)句一樣會(huì)有下面這些情況：
聚簇索引，查詢(xún)命中：DELETE FROM students WHERE id = 15;
聚簇索引，查詢(xún)未命中：DELETE FROM students WHERE id = 16;
二級(jí)唯一索引，查詢(xún)命中：DELETE FROM students WHERE no = 'S0003';
二級(jí)唯一索引，查詢(xún)未命中：DELETE FROM students WHERE no = 'S0008';
二級(jí)非唯一索引，查詢(xún)命中：DELETE FROM students WHERE name = 'Tom';
二級(jí)非唯一索引，查詢(xún)未命中：DELETE FROM students WHERE name = 'John';
無(wú)索引：DELETE FROM students WHERE score = 22;
聚簇索引，范圍查詢(xún)：DELETE FROM students WHERE id <= 20;
二級(jí)索引，范圍查詢(xún)：DELETE FROM students WHERE age <= 23;
針對(duì)這些情況的加鎖分析和上文一致，這里不再贅述。
那么 DELETE 語(yǔ)句和 UPDATE 語(yǔ)句的加鎖到底會(huì)有什么不同呢？我們知道，在 MySQL 數(shù)據(jù)庫(kù)中，執(zhí)行 DELETE 語(yǔ)句其實(shí)并沒(méi)有直接刪除記錄，而是在記錄上打上一個(gè)刪除標(biāo)記，然后通過(guò)后臺(tái)的一個(gè)叫做 purge 的線(xiàn)程來(lái)清理。從這一點(diǎn)來(lái)看，DELETE 和 UPDATE 確實(shí)是非常相像。事實(shí)上，DELETE 和 UPDATE 的加鎖也幾乎是一樣的，這里要單獨(dú)加一節(jié)來(lái)說(shuō)明 DELETE 語(yǔ)句的加鎖分析，其實(shí)并不是因?yàn)?DELETE 語(yǔ)句的加鎖和其他語(yǔ)句有所不同，而是因?yàn)?DELETE 語(yǔ)句導(dǎo)致多了一種特殊類(lèi)型的記錄：標(biāo)記為刪除的記錄，對(duì)于這種類(lèi)型記錄，它的加鎖和其他記錄的加鎖機(jī)制不一樣。所以這一節(jié)的標(biāo)題叫做 標(biāo)記為刪除的記錄的加鎖分析 可能更合適。
那么問(wèn)題又來(lái)了：什么情況下會(huì)對(duì)已標(biāo)記為刪除的記錄加鎖呢？我總結(jié)下來(lái)會(huì)有兩種情況：阻塞后加鎖和 快照讀后加鎖（自己取得名字），下面分別介紹。
阻塞后加鎖 如下圖所示，事務(wù) A 刪除 id = 18 這條記錄，同時(shí)事務(wù) B 也刪除 id = 18 這條記錄，很顯然，id 為主鍵，DELETE 語(yǔ)句需要獲取 X 記錄鎖，事務(wù) B 阻塞。事務(wù) A 提交之后，id = 18 這條記錄被標(biāo)記為刪除，此時(shí)事務(wù) B 就需要對(duì)已刪除記錄進(jìn)行加鎖。
快照讀后加鎖 如下圖所示，事務(wù) A 刪除 id = 18 這條記錄，并提交。事務(wù) B 在事務(wù) A 提交之前有一次 id = 18 的快照讀，所以在后面刪除 id = 18 這條記錄的時(shí)候就需要對(duì)已刪除記錄加鎖了。如果沒(méi)有事務(wù)開(kāi)頭的這個(gè)快照讀，DELETE 語(yǔ)句就只是簡(jiǎn)單的刪除一條不存在的記錄。
注意，上面的事務(wù) B 不限于 DELETE 語(yǔ)句，換成 UPDATE 或 SELECT FOR UPDATE 同樣適用。網(wǎng)上對(duì)這種刪除記錄的加鎖分析并不多，我通過(guò)自己做的實(shí)驗(yàn)，得到了下面這些結(jié)論，如有不正確的地方，歡迎斧正。（實(shí)驗(yàn)環(huán)境，MySQL 版本：5.7，隔離級(jí)別：RR）
刪除記錄為聚簇索引
阻塞后加鎖：在刪除記錄上加 X 記錄鎖（rec but not gap），并在刪除的后一條記錄上加間隙鎖（gap before rec）
快照讀后加鎖：在刪除記錄上加 X 記錄鎖（rec but not gap）
刪除記錄為二級(jí)索引（唯一索引和非唯一索引都適用）
阻塞后加鎖：在刪除記錄上加 Next-key 鎖，并在刪除的后一條記錄上加間隙鎖
快照讀后加鎖：在刪除記錄上加 Next-key 鎖，并在刪除的后一條記錄上加間隙鎖
要注意的是，這里的隔離級(jí)別為 RR，如果在 RC 隔離級(jí)別下，加鎖過(guò)程應(yīng)該會(huì)不一樣，感興趣的同學(xué)可以自行實(shí)驗(yàn)。關(guān)于 DELETE 語(yǔ)句的加鎖，何登成前輩在他的博客：《一個(gè)最不可思議的MySQL死鎖分析》里面有詳細(xì)的分析，并介紹了頁(yè)面鎖的相關(guān)概念，還原了僅僅只有一條 DELETE 語(yǔ)句也會(huì)造成死鎖的整個(gè)過(guò)程，講的很精彩。
五、INSERT 語(yǔ)句加鎖分析
上面所提到的加鎖分析，都是針對(duì) SELECT FOR UPDATE、UPDATE、DELETE 等進(jìn)行的，那么針對(duì) INSERT 加鎖過(guò)程又是怎樣的呢？我們下面繼續(xù)探索。
還是用 students 表來(lái)實(shí)驗(yàn)，譬如我們執(zhí)行下面的 SQL：
mysql> insert into students(no, name, age, score) value('S0008', 'John', 26, 87);
然后我們用 show engine innodb status\G 查詢(xún)事務(wù)的鎖情況：
---TRANSACTION 3774, ACTIVE 2 sec
1 lock struct(s), heap size 1136, 0 row lock(s), undo log entries 1
MySQL thread id 150, OS thread handle 10420, query id 3125 localhost ::1 root
TABLE LOCK table `sys`.`t3` trx id 3774 lock mode IX
我們發(fā)現(xiàn)除了一個(gè) IX 的 TABLE LOCK 之外，就沒(méi)有其他的鎖了，難道 INSERT 不加鎖？一般來(lái)說(shuō)，加鎖都是對(duì)表中已有的記錄進(jìn)行加鎖，而 INSERT 語(yǔ)句是插入一條新的紀(jì)錄，這條記錄表中本來(lái)就沒(méi)有，那是不是就不需要加鎖了？顯然不是，至少有兩個(gè)原因可以說(shuō)明 INSERT 加了鎖：
為了防止幻讀，如果記錄之間加有 GAP 鎖，此時(shí)不能 INSERT；
如果 INSERT 的記錄和已有記錄造成唯一鍵沖突，此時(shí)不能 INSERT；
要解決這兩個(gè)問(wèn)題，都是靠鎖來(lái)解決的（第一個(gè)加插入意向鎖，第二個(gè)加 S 鎖進(jìn)行當(dāng)前讀），只是在 INSERT 的時(shí)候如果沒(méi)有出現(xiàn)這兩種情況，那么鎖就是隱式的，只是我們看不到而已。這里我們不得不提一個(gè)概念叫 隱式鎖（Implicit Lock），它對(duì)我們分析 INSERT 語(yǔ)句的加鎖過(guò)程至關(guān)重要。
關(guān)于隱式鎖，這篇文章《MySQL數(shù)據(jù)庫(kù)InnoDB存儲(chǔ)引擎中的鎖機(jī)制》對(duì)其做了詳細(xì)的說(shuō)明，講的非常清楚，推薦一讀?？梢詤⒖忌弦黄榻B的悲觀鎖和樂(lè)觀鎖。
鎖是一種悲觀的順序化機(jī)制，它假設(shè)很可能發(fā)生沖突，因此在操作數(shù)據(jù)時(shí)，就加鎖，如果沖突的可能性很小，多數(shù)的鎖都是不必要的。Innodb 實(shí)現(xiàn)了一個(gè)延遲加鎖的機(jī)制來(lái)減少加鎖的數(shù)量，這被稱(chēng)為隱式鎖。 隱式鎖中有個(gè)重要的元素：事務(wù)ID（trx_id）。隱式鎖的邏輯過(guò)程如下： A. InnoDB 的每條記錄中都有一個(gè)隱含的 trx_id 字段，這個(gè)字段存在于簇索引的 B+Tree 中; B. 在操作一條記錄前，首先根據(jù)記錄中的 trx_id 檢查該事務(wù)是否是活動(dòng)的事務(wù)（未提交或回滾），如果是活動(dòng)的事務(wù)，首先將隱式鎖轉(zhuǎn)換為顯式鎖（就是為該事務(wù)添加一個(gè)鎖）； C. 檢查是否有鎖沖突，如果有沖突，創(chuàng)建鎖，并設(shè)置為 waiting 狀態(tài)；如果沒(méi)有沖突不加鎖，跳到 E； D. 等待加鎖成功，被喚醒，或者超時(shí)； E. 寫(xiě)數(shù)據(jù)，并將自己的 trx_id 寫(xiě)入 trx_id 字段。 隱式鎖的特點(diǎn)是只有在可能發(fā)生沖突時(shí)才加鎖，減少了鎖的數(shù)量。另外，隱式鎖是針對(duì)被修改的 B+Tree 記錄，因此都是 Record 類(lèi)型的鎖，不可能是 Gap 或 Next-Key 類(lèi)型。 INSERT 操作只加隱式鎖，不需要顯示加鎖；UPDATE、DELETE 在查詢(xún)時(shí)，直接對(duì)查詢(xún)用的 Index 和主鍵使用顯示鎖，其他索引上使用隱式鎖。 理論上說(shuō)，可以對(duì)主鍵使用隱式鎖的。提前使用顯示鎖應(yīng)該是為了減少死鎖的可能性。INSERT，UPDATE，DELETE 對(duì) B+Tree 們的操作都是從主鍵的 B+Tree 開(kāi)始，因此對(duì)主鍵加鎖可以有效的阻止死鎖。
INSERT 加鎖流程如下（參考）：
首先對(duì)插入的間隙加插入意向鎖（Insert Intension Locks）
如果該間隙已被加上了 GAP 鎖或 Next-Key 鎖，則加鎖失敗進(jìn)入等待；
如果沒(méi)有，則加鎖成功，表示可以插入；
然后判斷插入記錄是否有唯一鍵，如果有，則進(jìn)行唯一性約束檢查
如果不存在相同鍵值，則完成插入
如果存在相同鍵值，則判斷該鍵值是否加鎖
如果沒(méi)有鎖， 判斷該記錄是否被標(biāo)記為刪除
如果標(biāo)記為刪除，說(shuō)明事務(wù)已經(jīng)提交，還沒(méi)來(lái)得及 purge，這時(shí)加 S 鎖等待；
如果沒(méi)有標(biāo)記刪除，則報(bào) 1062 duplicate key 錯(cuò)誤；
如果有鎖，說(shuō)明該記錄正在處理（新增、刪除或更新），且事務(wù)還未提交，加 S 鎖等待；
插入記錄并對(duì)記錄加 X 記錄鎖；
這里的表述其實(shí)并不準(zhǔn)確，有興趣的同學(xué)可以去閱讀 InnoDb 的源碼分析 INSERT 語(yǔ)句具體的加鎖過(guò)程，我在 《讀 MySQL 源碼再看 INSERT 加鎖流程》 這篇博客中有詳細(xì)的介紹。
參考
何登成的技術(shù)博客 - MySQL 加鎖處理分析
何登成的技術(shù)博客 - MySQL+InnoDB semi-consitent read原理及實(shí)現(xiàn)分析
何登成的技術(shù)博客 - SQL中的where條件，在數(shù)據(jù)庫(kù)中提取與應(yīng)用淺析
何登成的技術(shù)博客 - 一個(gè)最不可思議的MySQL死鎖分析
Yilun Fan's Blog - MySQL加鎖分析
10分鐘讓你明白MySQL是如何利用索引的
MySQL innodb中各種SQL語(yǔ)句加鎖分析
MySQL的并發(fā)控制與加鎖分析
克魯斯卡爾的博客 - InnoDB 鎖
MySQL數(shù)據(jù)庫(kù)InnoDB存儲(chǔ)引擎中的鎖機(jī)制
MySQL DELETE 刪除語(yǔ)句加鎖分析
MySQL鎖系列（七）之 鎖算法詳解
[MySQL 5.6] 初識(shí)5.6的optimizer trace
[MySQL學(xué)習(xí)] Innodb鎖系統(tǒng)(4) Insert/Delete 鎖處理及死鎖示例分析
