[[330970]]

疫情期間在家工作時(shí)，同事使用了 insert into on duplicate key update 語(yǔ)句進(jìn)行插入去重，但是在測(cè)試過(guò)程中發(fā)生了死鎖現(xiàn)象:

ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction 

由于開(kāi)發(fā)任務(wù)緊急，只是暫時(shí)規(guī)避了一下，但是對(duì)觸發(fā)死鎖的原因和相關(guān)原理不甚了解，于是這幾天一直在查閱相關(guān)資料，總結(jié)出一個(gè)系列文章供大家參考，本篇是上篇，主要介紹 MySQL 加鎖原理和鎖的不同模式或類型的基本知識(shí)。后續(xù)會(huì)講解常見(jiàn)語(yǔ)句的加鎖情況和通過(guò) MySQL 死鎖日志分析死鎖原因。

由于本篇文章涉及很多 MySQL 的基礎(chǔ)知識(shí)，大家可以自行閱讀我之前的 MySQL系列文章 《MySQL探秘》(公眾號(hào)菜單處可進(jìn)入系列文章)中的對(duì)應(yīng)章節(jié)。

表鎖和行鎖

我們首先來(lái)了解一下表鎖和行鎖：表鎖是指對(duì)一整張表加鎖，一般是 DDL 處理時(shí)使用;而行鎖則是鎖定某一行或者某幾行，或者行與行之間的間隙。

表鎖由 MySQL Server 實(shí)現(xiàn)，行鎖則是存儲(chǔ)引擎實(shí)現(xiàn)，不同的引擎實(shí)現(xiàn)的不同。在 MySQL 的常用引擎中 InnoDB 支持行鎖，而 MyISAM 則只能使用 MySQL Server 提供的表鎖。

 

 

表鎖

表鎖由 MySQL Server 實(shí)現(xiàn)，一般在執(zhí)行 DDL 語(yǔ)句時(shí)會(huì)對(duì)整個(gè)表進(jìn)行加鎖，比如說(shuō) ALTER TABLE 等操作。在執(zhí)行 SQL 語(yǔ)句時(shí)，也可以明確指定對(duì)某個(gè)表進(jìn)行加鎖。

mysql> lock table user read(write); # 分為讀鎖和寫鎖Query OK, 0 rows affected (0.00 sec)mysql> select * from user where id = 100; # 成功mysql> select * from role where id = 100; # 失敗，未提前獲取該 role的讀表鎖mysql> update user  set name = 'Tom' where id = 100; # 失敗，未提前獲得user的寫表鎖mysql> unlock tables; # 顯示釋放表鎖Query OK, 0 rows affected (0.00 sec) 

表鎖使用的是一次性鎖技術(shù)，也就是說(shuō)，在會(huì)話開(kāi)始的地方使用 lock 命令將后續(xù)需要用到的表都加上鎖，在表釋放前，只能訪問(wèn)這些加鎖的表，不能訪問(wèn)其他表，直到最后通過(guò) unlock tables 釋放所有表鎖。

除了使用 unlock tables 顯示釋放鎖之外，會(huì)話持有其他表鎖時(shí)執(zhí)行l(wèi)ock table 語(yǔ)句會(huì)釋放會(huì)話之前持有的鎖;會(huì)話持有其他表鎖時(shí)執(zhí)行 start transaction 或者 begin 開(kāi)啟事務(wù)時(shí)，也會(huì)釋放之前持有的鎖。

行鎖

不同存儲(chǔ)引擎的行鎖實(shí)現(xiàn)不同，后續(xù)沒(méi)有特別說(shuō)明，則行鎖特指 InnoDB 實(shí)現(xiàn)的行鎖。

在了解 InnoDB 的加鎖原理前，需要對(duì)其存儲(chǔ)結(jié)構(gòu)有一定的了解。InnoDB 是聚簇索引，也就是 B+樹(shù)的葉節(jié)點(diǎn)既存儲(chǔ)了主鍵索引也存儲(chǔ)了數(shù)據(jù)行。而 InnoDB 的二級(jí)索引的葉節(jié)點(diǎn)存儲(chǔ)的則是主鍵值，所以通過(guò)二級(jí)索引查詢數(shù)據(jù)時(shí)，還需要拿對(duì)應(yīng)的主鍵去聚簇索引中再次進(jìn)行查詢。關(guān)于 InnoDB 和 MyISAM 的索引的詳細(xì)知識(shí)可以閱讀《Mysql探索(一):B+Tree索引》一文。

 

 

下面以兩條 SQL 的執(zhí)行為例，講解一下 InnoDB 對(duì)于單行數(shù)據(jù)的加鎖原理。

update user set age = 10 where id = 49;update user set age = 10 where name = 'Tom'; 

第一條 SQL 使用主鍵索引來(lái)查詢，則只需要在 id = 49 這個(gè)主鍵索引上加上寫鎖;第二條 SQL 則使用二級(jí)索引來(lái)查詢，則首先在 name = Tom 這個(gè)索引上加寫鎖，然后由于使用 InnoDB 二級(jí)索引還需再次根據(jù)主鍵索引查詢，所以還需要在 id = 49 這個(gè)主鍵索引上加寫鎖，如上圖所示。

也就是說(shuō)使用主鍵索引需要加一把鎖，使用二級(jí)索引需要在二級(jí)索引和主鍵索引上各加一把鎖。

根據(jù)索引對(duì)單行數(shù)據(jù)進(jìn)行更新的加鎖原理了解了，那如果更新操作涉及多個(gè)行呢，比如下面 SQL 的執(zhí)行場(chǎng)景。

update user set age = 10 where id > 49; 

上述 SQL 的執(zhí)行過(guò)程如下圖所示。MySQL Server 會(huì)根據(jù) WHERE 條件讀取第一條滿足條件的記錄，然后 InnoDB 引擎會(huì)將第一條記錄返回并加鎖，接著 MySQL Server 發(fā)起更新改行記錄的 UPDATE 請(qǐng)求，更新這條記錄。一條記錄操作完成，再讀取下一條記錄，直至沒(méi)有匹配的記錄為止。

 

 

這種場(chǎng)景下的鎖的釋放較為復(fù)雜，有多種的優(yōu)化方式，我對(duì)這塊暫時(shí)還沒(méi)有了解，還請(qǐng)知道的小伙伴在下方留言解釋。

下面主要依次介紹 InnoDB 中鎖的模式和類型，鎖的類型是指鎖的粒度或者鎖具體加在什么地方;而鎖模式描述的是鎖的兼容性，也就是加的是什么鎖，比如寫鎖或者讀鎖。

內(nèi)容基本來(lái)自于 MySQL 的技術(shù)文檔 innodb-lock 一章，感興趣的同學(xué)可以直接去閱讀原文，原文地址為見(jiàn)文章末尾。

行鎖的模式

鎖的模式有：讀意向鎖，寫意向鎖，讀鎖，寫鎖和自增鎖(auto_inc)，下面我們依次來(lái)看。

讀寫鎖

讀鎖，又稱共享鎖(Share locks，簡(jiǎn)稱 S 鎖)，加了讀鎖的記錄，所有的事務(wù)都可以讀取，但是不能修改，并且可同時(shí)有多個(gè)事務(wù)對(duì)記錄加讀鎖。

寫鎖，又稱排他鎖(Exclusive locks，簡(jiǎn)稱 X 鎖)，或獨(dú)占鎖，對(duì)記錄加了排他鎖之后，只有擁有該鎖的事務(wù)可以讀取和修改，其他事務(wù)都不可以讀取和修改，并且同一時(shí)間只能有一個(gè)事務(wù)加寫鎖。

讀寫意向鎖

由于表鎖和行鎖雖然鎖定范圍不同，但是會(huì)相互沖突。所以當(dāng)你要加表鎖時(shí)，勢(shì)必要先遍歷該表的所有記錄，判斷是否加有排他鎖。這種遍歷檢查的方式顯然是一種低效的方式，MySQL 引入了意向鎖，來(lái)檢測(cè)表鎖和行鎖的沖突。

意向鎖也是表級(jí)鎖，也可分為讀意向鎖(IS 鎖)和寫意向鎖(IX 鎖)。當(dāng)事務(wù)要在記錄上加上讀鎖或?qū)戞i時(shí)，要首先在表上加上意向鎖。這樣判斷表中是否有記錄加鎖就很簡(jiǎn)單了，只要看下表上是否有意向鎖就行了。

意向鎖之間是不會(huì)產(chǎn)生沖突的，也不和 AUTO_INC 表鎖沖突，它只會(huì)阻塞表級(jí)讀鎖或表級(jí)寫鎖，另外，意向鎖也不會(huì)和行鎖沖突，行鎖只會(huì)和行鎖沖突。

自增鎖

AUTOINC 鎖又叫自增鎖(一般簡(jiǎn)寫成 AI 鎖)，是一種表鎖，當(dāng)表中有自增列(AUTOINCREMENT)時(shí)出現(xiàn)。當(dāng)插入表中有自增列時(shí)，數(shù)據(jù)庫(kù)需要自動(dòng)生成自增值，它會(huì)先為該表加 AUTOINC 表鎖，阻塞其他事務(wù)的插入操作，這樣保證生成的自增值肯定是唯一的。AUTOINC 鎖具有如下特點(diǎn)：

AUTO_INC 鎖互不兼容，也就是說(shuō)同一張表同時(shí)只允許有一個(gè)自增鎖;

自增值一旦分配了就會(huì) +1，如果事務(wù)回滾，自增值也不會(huì)減回去，所以自增值可能會(huì)出現(xiàn)中斷的情況。

顯然，AUTOINC 表鎖會(huì)導(dǎo)致并發(fā)插入的效率降低，為了提高插入的并發(fā)性，MySQL 從 5.1.22 版本開(kāi)始，引入了一種可選的輕量級(jí)鎖(mutex)機(jī)制來(lái)代替 AUTOINC 鎖，可以通過(guò)參數(shù) innodbautoinclockmode 來(lái)靈活控制分配自增值時(shí)的并發(fā)策略。具體可以參考 MySQL 的 AUTOINCREMENT Handling in InnoDB 一文，鏈接在文末。

不同模式鎖的兼容矩陣

下面是各個(gè)表鎖之間的兼容矩陣。

 

 

總結(jié)起來(lái)有下面幾點(diǎn)：

• 意向鎖之間互不沖突;
• S 鎖只和 S/IS 鎖兼容，和其他鎖都沖突;
• X 鎖和其他所有鎖都沖突;
• AI 鎖只和意向鎖兼容;

行鎖的類型

根據(jù)鎖的粒度可以把鎖細(xì)分為表鎖和行鎖，行鎖根據(jù)場(chǎng)景的不同又可以進(jìn)一步細(xì)分，依次為 Next-Key Lock，Gap Lock 間隙鎖，Record Lock 記錄鎖和插入意向 GAP 鎖。

不同的鎖鎖定的位置是不同的，比如說(shuō)記錄鎖只鎖住對(duì)應(yīng)的記錄，而間隙鎖鎖住記錄和記錄之間的間隔，Next-Key Lock 則所屬記錄和記錄之前的間隙。不同類型鎖的鎖定范圍大致如下圖所示。

 

 

下面我們來(lái)依次了解一下不同的類型的鎖。

記錄鎖

記錄鎖是最簡(jiǎn)單的行鎖，并沒(méi)有什么好說(shuō)的。上邊描述 InnoDB 加鎖原理中的鎖就是記錄鎖，只鎖住 id = 49 或者 name = 'Tom' 這一條記錄。

當(dāng) SQL 語(yǔ)句無(wú)法使用索引時(shí)，會(huì)進(jìn)行全表掃描，這個(gè)時(shí)候 MySQL 會(huì)給整張表的所有數(shù)據(jù)行加記錄鎖，再由 MySQL Server 層進(jìn)行過(guò)濾。但是，在 MySQL Server 層進(jìn)行過(guò)濾的時(shí)候，如果發(fā)現(xiàn)不滿足 WHERE 條件，會(huì)釋放對(duì)應(yīng)記錄的鎖。這樣做，保證了最后只會(huì)持有滿足條件記錄上的鎖，但是每條記錄的加鎖操作還是不能省略的。

所以更新操作必須要根據(jù)索引進(jìn)行操作，沒(méi)有索引時(shí)，不僅會(huì)消耗大量的鎖資源，增加數(shù)據(jù)庫(kù)的開(kāi)銷，還會(huì)極大的降低了數(shù)據(jù)庫(kù)的并發(fā)性能。

間隙鎖

還是最開(kāi)始更新用戶年齡的例子，如果 id = 49 這條記錄不存在，這個(gè) SQL 語(yǔ)句還會(huì)加鎖嗎?答案是可能有，這取決于數(shù)據(jù)庫(kù)的隔離級(jí)別。這種情況下，在 RC 隔離級(jí)別不會(huì)加任何鎖，在 RR 隔離級(jí)別會(huì)在 id = 49 前后兩個(gè)索引之間加上間隙鎖。

間隙鎖是一種加在兩個(gè)索引之間的鎖，或者加在第一個(gè)索引之前，或最后一個(gè)索引之后的間隙。這個(gè)間隙可以跨一個(gè)索引記錄，多個(gè)索引記錄，甚至是空的。使用間隙鎖可以防止其他事務(wù)在這個(gè)范圍內(nèi)插入或修改記錄，保證兩次讀取這個(gè)范圍內(nèi)的記錄不會(huì)變，從而不會(huì)出現(xiàn)幻讀現(xiàn)象。

值得注意的是，間隙鎖和間隙鎖之間是互不沖突的，間隙鎖唯一的作用就是為了防止其他事務(wù)的插入，所以加間隙 S 鎖和加間隙 X 鎖沒(méi)有任何區(qū)別。

Next-Key 鎖

Next-key鎖是記錄鎖和間隙鎖的組合，它指的是加在某條記錄以及這條記錄前面間隙上的鎖。假設(shè)一個(gè)索引包含 15、18、20 ，30，49，50 這幾個(gè)值，可能的 Next-key 鎖如下：

(-∞, 15]，(15, 18]，(18, 20]，(20, 30]，(30, 49]，(49, 50]，(50, +∞) 

通常我們都用這種左開(kāi)右閉區(qū)間來(lái)表示 Next-key 鎖，其中，圓括號(hào)表示不包含該記錄，方括號(hào)表示包含該記錄。前面四個(gè)都是 Next-key 鎖，最后一個(gè)為間隙鎖。和間隙鎖一樣，在 RC 隔離級(jí)別下沒(méi)有 Next-key 鎖，只有 RR 隔離級(jí)別才有。還是之前的例子，如果 id 不是主鍵，而是二級(jí)索引，且不是唯一索引，那么這個(gè) SQL 在 RR 隔離級(jí)別下就會(huì)加如下的 Next-key 鎖 (30, 49](49, 50)

此時(shí)如果插入一條 id = 31 的記錄將會(huì)阻塞住。之所以要把 id = 49 前后的間隙都鎖住，仍然是為了解決幻讀問(wèn)題，因?yàn)?id 是非唯一索引，所以 id = 49 可能會(huì)有多條記錄，為了防止再插入一條 id = 49 的記錄。

插入意向鎖

插入意向鎖是一種特殊的間隙鎖(簡(jiǎn)寫成 II GAP)表示插入的意向，只有在 INSERT 的時(shí)候才會(huì)有這個(gè)鎖。注意，這個(gè)鎖雖然也叫意向鎖，但是和上面介紹的表級(jí)意向鎖是兩個(gè)完全不同的概念，不要搞混了。

插入意向鎖和插入意向鎖之間互不沖突，所以可以在同一個(gè)間隙中有多個(gè)事務(wù)同時(shí)插入不同索引的記錄。譬如在上面的例子中，id = 30 和 id = 49 之間如果有兩個(gè)事務(wù)要同時(shí)分別插入 id = 32 和 id = 33 是沒(méi)問(wèn)題的，雖然兩個(gè)事務(wù)都會(huì)在 id = 30 和 id = 50 之間加上插入意向鎖，但是不會(huì)沖突。

插入意向鎖只會(huì)和間隙鎖或 Next-key 鎖沖突，正如上面所說(shuō)，間隙鎖唯一的作用就是防止其他事務(wù)插入記錄造成幻讀，正是由于在執(zhí)行 INSERT 語(yǔ)句時(shí)需要加插入意向鎖，而插入意向鎖和間隙鎖沖突，從而阻止了插入操作的執(zhí)行。

不同類型鎖的兼容矩陣

不同類型鎖的兼容下如下圖所示。

 

 

其中，第一行表示已有的鎖，第一列表示要加的鎖。插入意向鎖較為特殊，所以我們先對(duì)插入意向鎖做個(gè)總結(jié)，如下：

• 插入意向鎖不影響其他事務(wù)加其他任何鎖。也就是說(shuō)，一個(gè)事務(wù)已經(jīng)獲取了插入意向鎖，對(duì)其他事務(wù)是沒(méi)有任何影響的;
• 插入意向鎖與間隙鎖和 Next-key 鎖沖突。也就是說(shuō)，一個(gè)事務(wù)想要獲取插入意向鎖，如果有其他事務(wù)已經(jīng)加了間隙鎖或 Next-key 鎖，則會(huì)阻塞。

其他類型的鎖的規(guī)則較為簡(jiǎn)單：

• 間隙鎖不和其他鎖(不包括插入意向鎖)沖突;
• 記錄鎖和記錄鎖沖突，Next-key 鎖和 Next-key 鎖沖突，記錄鎖和 Next-key 鎖沖突;

常見(jiàn)加鎖場(chǎng)景分析

今天我們就從原理走向?qū)崙?zhàn)，分析常見(jiàn) SQL 語(yǔ)句的加鎖場(chǎng)景。了解了這幾種場(chǎng)景，相信小伙伴們也能舉一反三，靈活地分析真實(shí)開(kāi)發(fā)過(guò)程中遇到的加鎖問(wèn)題。

如下圖所示，數(shù)據(jù)庫(kù)的隔離等級(jí)，SQL 語(yǔ)句和當(dāng)前數(shù)據(jù)庫(kù)數(shù)據(jù)會(huì)共同影響該條 SQL 執(zhí)行時(shí)數(shù)據(jù)庫(kù)生成的鎖模式，鎖類型和鎖數(shù)量。

 

 

下面，我們會(huì)首先講解一下隔離等級(jí)、不同 SQL 語(yǔ)句 和 當(dāng)前數(shù)據(jù)庫(kù)數(shù)據(jù)對(duì)生成鎖影響的基本規(guī)則，然后再依次具體 SQL 的加鎖場(chǎng)景。

隔離等級(jí)對(duì)加鎖的影響

MySQL 的隔離等級(jí)對(duì)加鎖有影響，所以在分析具體加鎖場(chǎng)景時(shí)，首先要確定當(dāng)前的隔離等級(jí)。

• 讀未提交(Read Uncommitted 后續(xù)簡(jiǎn)稱 RU)：可以讀到未提交的讀，基本上不會(huì)使用該隔離等級(jí)，所以暫時(shí)忽略。
• 讀已提交(Read Committed 后續(xù)簡(jiǎn)稱 RC)：存在幻讀問(wèn)題，對(duì)當(dāng)前讀獲取的數(shù)據(jù)加記錄鎖。
• 可重復(fù)讀(Repeatable Read 后續(xù)簡(jiǎn)稱 RR)：不存在幻讀問(wèn)題，對(duì)當(dāng)前讀獲取的數(shù)據(jù)加記錄鎖，同時(shí)對(duì)涉及的范圍加間隙鎖，防止新的數(shù)據(jù)插入，導(dǎo)致幻讀。
• 序列化(Serializable)：從 MVCC 并發(fā)控制退化到基于鎖的并發(fā)控制，不存在快照讀，都是當(dāng)前讀，并發(fā)效率急劇下降，不建議使用。

這里說(shuō)明一下，RC 總是讀取記錄的最新版本，而 RR 是讀取該記錄事務(wù)開(kāi)始時(shí)的那個(gè)版本，雖然這兩種讀取的版本不同，但是都是快照數(shù)據(jù)，并不會(huì)被寫操作阻塞，所以這種讀操作稱為 快照讀(Snapshot Read)

MySQL 還提供了另一種讀取方式叫當(dāng)前讀(Current Read)，它讀的不再是數(shù)據(jù)的快照版本，而是數(shù)據(jù)的最新版本，并會(huì)對(duì)數(shù)據(jù)加鎖，根據(jù)語(yǔ)句和加鎖的不同，又分成三種情況：

• SELECT ... LOCK IN SHARE MODE：加共享(S)鎖
• SELECT ... FOR UPDATE：加排他(X)鎖
• INSERT / UPDATE / DELETE：加排他(X)鎖

當(dāng)前讀在 RR 和 RC 兩種隔離級(jí)別下的實(shí)現(xiàn)也是不一樣的：RC 只加記錄鎖，RR 除了加記錄鎖，還會(huì)加間隙鎖，用于解決幻讀問(wèn)題。

不同 SQL 語(yǔ)句對(duì)加鎖的影響

不同的 SQL 語(yǔ)句當(dāng)然會(huì)加不同的鎖，總結(jié)起來(lái)主要分為五種情況：

• 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ǔ)句為隱式提交，不能回滾。

其中，當(dāng)前讀的 SQL 語(yǔ)句的 where 從句的不同也會(huì)影響加鎖，包括是否使用索引，索引是否是唯一索引等等。

當(dāng)前數(shù)據(jù)對(duì)加鎖的影響

SQL 語(yǔ)句執(zhí)行時(shí)數(shù)據(jù)庫(kù)中的數(shù)據(jù)也會(huì)對(duì)加鎖產(chǎn)生影響。

比如一條最簡(jiǎn)單的根據(jù)主鍵進(jìn)行更新的 SQL 語(yǔ)句，如果主鍵存在，則只需要對(duì)其加記錄鎖，如果不存在，則需要在加間隙鎖。

至于其他非唯一性索引更新或者插入時(shí)的加鎖也都不同程度的受到現(xiàn)存數(shù)據(jù)的影響，后續(xù)我們會(huì)一一說(shuō)明。

具體場(chǎng)景分析

具體 SQL 場(chǎng)景分析主要借鑒何登成前輩的《MySQL 加鎖處理分析》文章和 aneasystone 的系列文章，在他們的基礎(chǔ)上進(jìn)行了總結(jié)和整理。

我們使用下面這張 book 表作為實(shí)例，其中 id 為主鍵，ISBN(書號(hào))為二級(jí)唯一索引，Author(作者)為二級(jí)非唯一索引，score(評(píng)分)無(wú)索引。

 

 

UPDATE 語(yǔ)句加鎖分析

下面，我們先來(lái)分析 UPDATE 相關(guān) SQL 在使用較為簡(jiǎn)單 where 從句情況下加鎖情況。其中的分析原則也適用于 UPDATE,DELETE 和 SELECT ... FOR UPDATE等當(dāng)前讀的語(yǔ)句。

聚簇索引，查詢命中

聚簇索引就是 InnoDB 存儲(chǔ)引擎下的主鍵索引，具體可參考《MySQL索引》。

下圖展示了使用 UPDATE book SET score = 9.2 WHERE ID = 10 語(yǔ)句命中的情況下在 RC 和 RR 隔離等級(jí)下的加鎖，兩種隔離等級(jí)下沒(méi)有任何區(qū)別，都是對(duì) ID = 10 這個(gè)索引加排他記錄鎖。

 

 

聚簇索引，查詢未命中

下圖展示了 UPDATE book SET score = 9.2 WHERE ID = 16 語(yǔ)句未命中時(shí) RR 隔離級(jí)別下的加鎖情況。

在 RC 隔離等級(jí)下，不需要加鎖;而在 RR 隔離級(jí)別會(huì)在 ID = 16 前后兩個(gè)索引之間加上間隙鎖。

 

 

值得注意的是，間隙鎖和間隙鎖之間是互不沖突的，間隙鎖唯一的作用就是為了防止其他事務(wù)的插入新行，導(dǎo)致幻讀，所以加間隙 S 鎖和加間隙 X 鎖沒(méi)有任何區(qū)別。

二級(jí)唯一索引，查詢命中

下圖展示了 UPDATE book SET score = 9.2 WHERE ISBN = 'N0003' 在 RC 和 RR 隔離等級(jí)下命中時(shí)的加鎖情況。

在 InnoDB 存儲(chǔ)引擎中，二級(jí)索引的葉子節(jié)點(diǎn)保存著主鍵索引的值，然后再拿主鍵索引去獲取真正的數(shù)據(jù)行，所以在這種情況下，二級(jí)索引和主鍵索引都會(huì)加排他記錄鎖。

 

 

二級(jí)唯一索引，查詢未命中

下圖展示了 UPDATE book SET score = 9.2 WHERE ISBN = 'N0008' 語(yǔ)句在 RR 隔離等級(jí)下未命中時(shí)的加鎖情況，RC 隔離等級(jí)下該語(yǔ)句未命中不會(huì)加鎖。

因?yàn)?N0008 大于 N0007，所以要鎖住 (N0007,正無(wú)窮)這段區(qū)間，而 InnoDB 的索引一般都使用 Suprenum Record 和 Infimum Record 來(lái)分別表示記錄的上下邊界。Infimum 是比該頁(yè)中任何記錄都要小的值，而 Supremum 比該頁(yè)中最大的記錄值還要大，這兩條記錄在創(chuàng)建頁(yè)的時(shí)候就有了，并且不會(huì)刪除。

所以，在 N0007 和 Suprenum Record 之間加了間隙鎖。

 

 

為什么不在主鍵上也加 GAP 鎖呢?歡迎留言說(shuō)出你的想法。

二級(jí)非唯一索引，查詢命中

下圖展示了 UPDATE book SET score = 9.2 WHERE Author = 'Tom' 語(yǔ)句在 RC 隔離等級(jí)下命中時(shí)的加鎖情況。

我們可以看到，在 RC 等級(jí)下，二級(jí)唯一索引和二級(jí)非唯一索引的加鎖情況是一致的，都是在涉及的二級(jí)索引和對(duì)應(yīng)的主鍵索引上加上排他記錄鎖。

 

 

但是在 RR 隔離等級(jí)下，加鎖的情況產(chǎn)生了變化，它不僅對(duì)涉及的二級(jí)索引和主鍵索引加了排他記錄鎖，還在非唯一二級(jí)索引上加了三個(gè)間隙鎖，鎖住了兩個(gè) Tom 索引值相關(guān)的三個(gè)范圍。

那為什么唯一索引不需要加間隙鎖呢?間隙鎖的作用是為了解決幻讀，防止其他事務(wù)插入相同索引值的記錄，而唯一索引和主鍵約束都已經(jīng)保證了該索引值肯定只有一條記錄，所以無(wú)需加間隙鎖。

 

 

需要注意的是，上圖雖然畫著 4 個(gè)記錄鎖，三個(gè)間隙鎖，但是實(shí)際上間隙鎖和它右側(cè)的記錄鎖會(huì)合并成 Next-Key 鎖。

所以實(shí)際情況有兩個(gè) Next-Key 鎖，一個(gè)間隙鎖(Tom60,正無(wú)窮)和兩個(gè)記錄鎖。

二級(jí)非唯一索引，查詢未命中

下圖展示了 UPDATE book SET score = 9.2 WHERE Author = 'Sarah' 在 RR 隔離等級(jí)下未命中的加鎖情況，它會(huì)在二級(jí)索引 Rose 和 Tom 之間加間隙鎖。而 RC 隔離等級(jí)下不需要加鎖。

 

 

無(wú)索引

當(dāng) Where 從句的條件并不使用索引時(shí)，則會(huì)對(duì)全表進(jìn)行掃描，在 RC 隔離等級(jí)下對(duì)所有的數(shù)據(jù)加排他記錄鎖。在RR 隔離等級(jí)下，除了給記錄加鎖，還會(huì)對(duì)記錄和記錄之間加間隙鎖。和上邊一樣，間隙鎖會(huì)和左側(cè)的記錄鎖合并成 Next-Key 鎖。

下圖就是 UPDATE book SET score = 9.2 WHERE score = 22 語(yǔ)句在兩種隔離等級(jí)下的加鎖情況。

 

 

聚簇索引，范圍查詢

上面介紹的場(chǎng)景都是 where 從句的等值查詢，而范圍查詢的加鎖又是怎么樣的呢?我們慢慢來(lái)看。

下圖是 UPDATE book SET score = 9.2 WHERE ID <= 25 在 RC 和 RR 隔離等級(jí)下的加鎖情況。

RC 場(chǎng)景下與等值查詢類似，只會(huì)在涉及的 ID = 10，ID = 18 和 ID = 25 索引上加排他記錄鎖。

 

 

而在 RR 隔離等級(jí)下則有所不同，它會(huì)加上間隙鎖，和對(duì)應(yīng)的記錄鎖合并稱為 Next-Key 鎖。除此之外，它還會(huì)在(25, 30] 上分別加 Next-Key 鎖。這一點(diǎn)是十分特殊的，具體原因還需要再探究。

二級(jí)索引，范圍查詢

下圖展示了 UPDATE book SET ISBN = N0001 WHERE score <= 7.9 在 RR 級(jí)別下的加鎖情況。

 

 

修改索引值

UPDATE 語(yǔ)句修改索引值的情況可以分開(kāi)分析，首先 Where 從句的加鎖分析如上文所述，多了一步 Set 部分的加鎖。

下圖展示了 UPDATE book SET Author = 'John' WHERE ID = 10 在 RC 和 RR 隔離等級(jí)下的加鎖情況。除了在主鍵 ID 上進(jìn)行加鎖，還會(huì)對(duì)二級(jí)索引上的 Bob(就值) 和 John(新值) 上進(jìn)行加鎖。

 

 

DELETE 語(yǔ)句加鎖分析

一般來(lái)說(shuō)，DELETE 的加鎖和 SELECT FOR UPDATE 或 UPDATE 并沒(méi)有太大的差異。

因?yàn)椋?MySQL 數(shù)據(jù)庫(kù)中，執(zhí)行 DELETE 語(yǔ)句其實(shí)并沒(méi)有直接刪除記錄，而是在記錄上打上一個(gè)刪除標(biāo)記，然后通過(guò)后臺(tái)的一個(gè)叫做 purge 的線程來(lái)清理。從這一點(diǎn)來(lái)看，DELETE 和 UPDATE 確實(shí)是非常相像。事實(shí)上，DELETE 和 UPDATE 的加鎖也幾乎是一樣的。

INSERT 語(yǔ)句加鎖分析

接下來(lái)，我們來(lái)看一下 Insert 語(yǔ)句的加鎖情況。

Insert 語(yǔ)句在兩種情況下會(huì)加鎖：

• 為了防止幻讀，如果記錄之間加有間隙鎖，此時(shí)不能 Insert;
• 如果 Insert 的記錄和已有記錄造成唯一鍵沖突，此時(shí)不能 Insert;

除了上述情況，Insert 語(yǔ)句的鎖都是隱式鎖。隱式鎖是 InnoDB 實(shí)現(xiàn)的一種延遲加鎖的機(jī)制來(lái)減少加鎖的數(shù)量。

隱式鎖的特點(diǎn)是只有在可能發(fā)生沖突時(shí)才加鎖，減少了鎖的數(shù)量。另外，隱式鎖是針對(duì)被修改的 B+Tree 記錄，因此都是記錄類型的鎖，不可能是間隙鎖或 Next-Key 類型。

具體 Insert 語(yǔ)句的加鎖流程如下：

• 首先對(duì)插入的間隙加插入意向鎖(Insert Intension Locks)如果該間隙已被加上了間隙鎖或 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) duplicate key 錯(cuò)誤;如果有鎖，說(shuō)明該記錄正在處理(新增、刪除或更新)，且事務(wù)還未提交，加 S 鎖等待;
• 插入記錄并對(duì)記錄加 X 記錄鎖;

后記

本文中講解的 SQL 語(yǔ)句都是十分簡(jiǎn)單的，當(dāng) SQL 語(yǔ)句包含多個(gè)查詢條件時(shí)，加鎖的分析過(guò)程就往往更加復(fù)雜。我們需要使用 MySQL 相關(guān)的工具進(jìn)行分析，并且有時(shí)甚至需要查詢 MySQL 相關(guān)的日志信息來(lái)了解到底語(yǔ)句加了什么鎖或者為什么產(chǎn)生死鎖，下篇文章中我們就主要了解一下這些內(nèi)容，請(qǐng)大家持續(xù)關(guān)注。