MySQL的死鎖系列之鎖的類型以及加鎖原理
本文轉(zhuǎn)載自微信公眾號「程序員歷小冰」，轉(zhuǎn)載本文請聯(lián)系程序員歷小冰公眾號。
疫情期間在家工作時(shí)，同事使用了 insert into on duplicate key update 語句進(jìn)行插入去重，但是在測試過程中發(fā)生了死鎖現(xiàn)象:
- ERROR 1213 (40001): Deadlock found when trying to get lock;
- try restarting transaction
由于開發(fā)任務(wù)緊急，只是暫時(shí)規(guī)避了一下，但是對觸發(fā)死鎖的原因和相關(guān)原理不甚了解，于是這幾天一直在查閱相關(guān)資料，總結(jié)出一個(gè)系列文章供大家參考，本篇是上篇，主要介紹 MySQL 加鎖原理和鎖的不同模式或類型的基本知識。后續(xù)會講解常見語句的加鎖情況和通過 MySQL 死鎖日志分析死鎖原因。
由于本篇文章涉及很多 MySQL 的基礎(chǔ)知識，大家可以自行閱讀我之前的 MySQL系列文章《MySQL探秘》(公眾號菜單處可進(jìn)入系列文章)中的對應(yīng)章節(jié)。
表鎖和行鎖
我們首先來了解一下表鎖和行鎖：表鎖是指對一整張表加鎖，一般是 DDL 處理時(shí)使用;而行鎖則是鎖定某一行或者某幾行，或者行與行之間的間隙。
表鎖由 MySQL Server 實(shí)現(xiàn)，行鎖則是存儲引擎實(shí)現(xiàn)，不同的引擎實(shí)現(xiàn)的不同。在 MySQL 的常用引擎中 InnoDB 支持行鎖，而 MyISAM 則只能使用 MySQL Server 提供的表鎖。
表鎖
表鎖由 MySQL Server 實(shí)現(xiàn)，一般在執(zhí)行 DDL 語句時(shí)會對整個(gè)表進(jìn)行加鎖，比如說 ALTER TABLE 等操作。在執(zhí)行 SQL 語句時(shí)，也可以明確指定對某個(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ù)，也就是說，在會話開始的地方使用 lock 命令將后續(xù)需要用到的表都加上鎖，在表釋放前，只能訪問這些加鎖的表，不能訪問其他表，直到最后通過 unlock tables 釋放所有表鎖。
除了使用 unlock tables 顯示釋放鎖之外，會話持有其他表鎖時(shí)執(zhí)行l(wèi)ock table 語句會釋放會話之前持有的鎖;會話持有其他表鎖時(shí)執(zhí)行 start transaction 或者 begin 開啟事務(wù)時(shí)，也會釋放之前持有的鎖。
行鎖
不同存儲引擎的行鎖實(shí)現(xiàn)不同，后續(xù)沒有特別說明，則行鎖特指 InnoDB 實(shí)現(xiàn)的行鎖。
在了解 InnoDB 的加鎖原理前，需要對其存儲結(jié)構(gòu)有一定的了解。InnoDB 是聚簇索引，也就是 B+樹的葉節(jié)點(diǎn)既存儲了主鍵索引也存儲了數(shù)據(jù)行。而 InnoDB 的二級索引的葉節(jié)點(diǎn)存儲的則是主鍵值，所以通過二級索引查詢數(shù)據(jù)時(shí)，還需要拿對應(yīng)的主鍵去聚簇索引中再次進(jìn)行查詢。關(guān)于 InnoDB 和 MyISAM 的索引的詳細(xì)知識可以閱讀《Mysql探索(一):B+Tree索引》一文。
下面以兩條 SQL 的執(zhí)行為例，講解一下 InnoDB 對于單行數(shù)據(jù)的加鎖原理。
- update user set age = 10 where id = 49;
- update user set age = 10 where name = 'Tom';
第一條 SQL 使用主鍵索引來查詢，則只需要在 id = 49 這個(gè)主鍵索引上加上寫鎖;第二條 SQL 則使用二級索引來查詢，則首先在 name = Tom 這個(gè)索引上加寫鎖，然后由于使用 InnoDB 二級索引還需再次根據(jù)主鍵索引查詢，所以還需要在 id = 49 這個(gè)主鍵索引上加寫鎖，如上圖所示。
也就是說使用主鍵索引需要加一把鎖，使用二級索引需要在二級索引和主鍵索引上各加一把鎖。
根據(jù)索引對單行數(shù)據(jù)進(jìn)行更新的加鎖原理了解了，那如果更新操作涉及多個(gè)行呢，比如下面 SQL 的執(zhí)行場景。
- update user set age = 10 where id > 49;
上述 SQL 的執(zhí)行過程如下圖所示。MySQL Server 會根據(jù) WHERE 條件讀取第一條滿足條件的記錄，然后 InnoDB 引擎會將第一條記錄返回并加鎖，接著 MySQL Server 發(fā)起更新改行記錄的 UPDATE 請求，更新這條記錄。一條記錄操作完成，再讀取下一條記錄，直至沒有匹配的記錄為止。
這種場景下的鎖的釋放較為復(fù)雜，有多種的優(yōu)化方式，我對這塊暫時(shí)還沒有了解，還請知道的小伙伴在下方留言解釋。
下面主要依次介紹 InnoDB 中鎖的模式和類型，鎖的類型是指鎖的粒度或者鎖具體加在什么地方;而鎖模式描述的是鎖的兼容性，也就是加的是什么鎖，比如寫鎖或者讀鎖。
內(nèi)容基本來自于 MySQL 的技術(shù)文檔 innodb-lock 一章，感興趣的同學(xué)可以直接去閱讀原文，原文地址為見文章末尾。
行鎖的模式
鎖的模式有：讀意向鎖，寫意向鎖，讀鎖，寫鎖和自增鎖(auto_inc)，下面我們依次來看。
讀寫鎖
讀鎖，又稱共享鎖(Share locks，簡稱 S 鎖)，加了讀鎖的記錄，所有的事務(wù)都可以讀取，但是不能修改，并且可同時(shí)有多個(gè)事務(wù)對記錄加讀鎖。
寫鎖，又稱排他鎖(Exclusive locks，簡稱 X 鎖)，或獨(dú)占鎖，對記錄加了排他鎖之后，只有擁有該鎖的事務(wù)可以讀取和修改，其他事務(wù)都不可以讀取和修改，并且同一時(shí)間只能有一個(gè)事務(wù)加寫鎖。
讀寫意向鎖
由于表鎖和行鎖雖然鎖定范圍不同，但是會相互沖突。所以當(dāng)你要加表鎖時(shí)，勢必要先遍歷該表的所有記錄，判斷是否加有排他鎖。這種遍歷檢查的方式顯然是一種低效的方式，MySQL 引入了意向鎖，來檢測表鎖和行鎖的沖突。
意向鎖也是表級鎖，也可分為讀意向鎖(IS 鎖)和寫意向鎖(IX 鎖)。當(dāng)事務(wù)要在記錄上加上讀鎖或?qū)戞i時(shí)，要首先在表上加上意向鎖。這樣判斷表中是否有記錄加鎖就很簡單了，只要看下表上是否有意向鎖就行了。
意向鎖之間是不會產(chǎn)生沖突的，也不和 AUTO_INC 表鎖沖突，它只會阻塞表級讀鎖或表級寫鎖，另外，意向鎖也不會和行鎖沖突，行鎖只會和行鎖沖突。
自增鎖
AUTOINC 鎖又叫自增鎖(一般簡寫成 AI 鎖)，是一種表鎖，當(dāng)表中有自增列(AUTOINCREMENT)時(shí)出現(xiàn)。當(dāng)插入表中有自增列時(shí)，數(shù)據(jù)庫需要自動生成自增值，它會先為該表加 AUTOINC 表鎖，阻塞其他事務(wù)的插入操作，這樣保證生成的自增值肯定是唯一的。AUTOINC 鎖具有如下特點(diǎn)：
- AUTO_INC 鎖互不兼容，也就是說同一張表同時(shí)只允許有一個(gè)自增鎖;
- 自增值一旦分配了就會 +1，如果事務(wù)回滾，自增值也不會減回去，所以自增值可能會出現(xiàn)中斷的情況。
顯然，AUTOINC 表鎖會導(dǎo)致并發(fā)插入的效率降低，為了提高插入的并發(fā)性，MySQL 從 5.1.22 版本開始，引入了一種可選的輕量級鎖(mutex)機(jī)制來代替 AUTOINC 鎖，可以通過參數(shù) innodbautoinclockmode 來靈活控制分配自增值時(shí)的并發(fā)策略。具體可以參考 MySQL 的 AUTOINCREMENT Handling in InnoDB 一文，鏈接在文末。
不同模式鎖的兼容矩陣
下面是各個(gè)表鎖之間的兼容矩陣。
總結(jié)起來有下面幾點(diǎn)：
- 意向鎖之間互不沖突;
- S 鎖只和 S/IS 鎖兼容，和其他鎖都沖突;
- X 鎖和其他所有鎖都沖突;
- AI 鎖只和意向鎖兼容;
行鎖的類型
根據(jù)鎖的粒度可以把鎖細(xì)分為表鎖和行鎖，行鎖根據(jù)場景的不同又可以進(jìn)一步細(xì)分，依次為 Next-Key Lock，Gap Lock 間隙鎖，Record Lock 記錄鎖和插入意向 GAP 鎖。
不同的鎖鎖定的位置是不同的，比如說記錄鎖只鎖住對應(yīng)的記錄，而間隙鎖鎖住記錄和記錄之間的間隔，Next-Key Lock 則所屬記錄和記錄之前的間隙。不同類型鎖的鎖定范圍大致如下圖所示。
下面我們來依次了解一下不同的類型的鎖。
記錄鎖
記錄鎖是最簡單的行鎖，并沒有什么好說的。上邊描述 InnoDB 加鎖原理中的鎖就是記錄鎖，只鎖住 id = 49 或者 name = 'Tom' 這一條記錄。
當(dāng) SQL 語句無法使用索引時(shí)，會進(jìn)行全表掃描，這個(gè)時(shí)候 MySQL 會給整張表的所有數(shù)據(jù)行加記錄鎖，再由 MySQL Server 層進(jìn)行過濾。但是，在 MySQL Server 層進(jìn)行過濾的時(shí)候，如果發(fā)現(xiàn)不滿足 WHERE 條件，會釋放對應(yīng)記錄的鎖。這樣做，保證了最后只會持有滿足條件記錄上的鎖，但是每條記錄的加鎖操作還是不能省略的。
所以更新操作必須要根據(jù)索引進(jìn)行操作，沒有索引時(shí)，不僅會消耗大量的鎖資源，增加數(shù)據(jù)庫的開銷，還會極大的降低了數(shù)據(jù)庫的并發(fā)性能。
間隙鎖
還是最開始更新用戶年齡的例子，如果 id = 49 這條記錄不存在，這個(gè) SQL 語句還會加鎖嗎?答案是可能有，這取決于數(shù)據(jù)庫的隔離級別。這種情況下，在 RC 隔離級別不會加任何鎖，在 RR 隔離級別會在 id = 49 前后兩個(gè)索引之間加上間隙鎖。
間隙鎖是一種加在兩個(gè)索引之間的鎖，或者加在第一個(gè)索引之前，或最后一個(gè)索引之后的間隙。這個(gè)間隙可以跨一個(gè)索引記錄，多個(gè)索引記錄，甚至是空的。使用間隙鎖可以防止其他事務(wù)在這個(gè)范圍內(nèi)插入或修改記錄，保證兩次讀取這個(gè)范圍內(nèi)的記錄不會變，從而不會出現(xiàn)幻讀現(xiàn)象。
值得注意的是，間隙鎖和間隙鎖之間是互不沖突的，間隙鎖唯一的作用就是為了防止其他事務(wù)的插入，所以加間隙 S 鎖和加間隙 X 鎖沒有任何區(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, +∞)
通常我們都用這種左開右閉區(qū)間來表示 Next-key 鎖，其中，圓括號表示不包含該記錄，方括號表示包含該記錄。前面四個(gè)都是 Next-key 鎖，最后一個(gè)為間隙鎖。和間隙鎖一樣，在 RC 隔離級別下沒有 Next-key 鎖，只有 RR 隔離級別才有。還是之前的例子，如果 id 不是主鍵，而是二級索引，且不是唯一索引，那么這個(gè) SQL 在 RR 隔離級別下就會加如下的 Next-key 鎖 (30, 49](49, 50)
此時(shí)如果插入一條 id = 31 的記錄將會阻塞住。之所以要把 id = 49 前后的間隙都鎖住，仍然是為了解決幻讀問題，因?yàn)?id 是非唯一索引，所以 id = 49 可能會有多條記錄，為了防止再插入一條 id = 49 的記錄。
插入意向鎖
插入意向鎖是一種特殊的間隙鎖(簡寫成 II GAP)表示插入的意向，只有在 INSERT 的時(shí)候才會有這個(gè)鎖。注意，這個(gè)鎖雖然也叫意向鎖，但是和上面介紹的表級意向鎖是兩個(gè)完全不同的概念，不要搞混了。
插入意向鎖和插入意向鎖之間互不沖突，所以可以在同一個(gè)間隙中有多個(gè)事務(wù)同時(shí)插入不同索引的記錄。譬如在上面的例子中，id = 30 和 id = 49 之間如果有兩個(gè)事務(wù)要同時(shí)分別插入 id = 32 和 id = 33 是沒問題的，雖然兩個(gè)事務(wù)都會在 id = 30 和 id = 50 之間加上插入意向鎖，但是不會沖突。
插入意向鎖只會和間隙鎖或 Next-key 鎖沖突，正如上面所說，間隙鎖唯一的作用就是防止其他事務(wù)插入記錄造成幻讀，正是由于在執(zhí)行 INSERT 語句時(shí)需要加插入意向鎖，而插入意向鎖和間隙鎖沖突，從而阻止了插入操作的執(zhí)行。
不同類型鎖的兼容矩陣
不同類型鎖的兼容下如下圖所示。
其中，第一行表示已有的鎖，第一列表示要加的鎖。插入意向鎖較為特殊，所以我們先對插入意向鎖做個(gè)總結(jié)，如下：
插入意向鎖不影響其他事務(wù)加其他任何鎖。也就是說，一個(gè)事務(wù)已經(jīng)獲取了插入意向鎖，對其他事務(wù)是沒有任何影響的;
插入意向鎖與間隙鎖和 Next-key 鎖沖突。也就是說，一個(gè)事務(wù)想要獲取插入意向鎖，如果有其他事務(wù)已經(jīng)加了間隙鎖或 Next-key 鎖，則會阻塞。
其他類型的鎖的規(guī)則較為簡單：
間隙鎖不和其他鎖(不包括插入意向鎖)沖突;
記錄鎖和記錄鎖沖突，Next-key 鎖和 Next-key 鎖沖突，記錄鎖和 Next-key 鎖沖突;
