文章來源：https://mp.weixin.qq.com/s/PPfXH9a3jlNyxXpwB_DZDg
作者：小冰
疫情期間在家工作時，同事使用了 insert into on duplicate key update 語句進行插入去重，但是在測試過程中發生了死鎖現象：
由於開發任務緊急，只是暫時規避了一下，但是對觸發死鎖的原因和相關原理不甚了解，於是這幾天一直在查閱相關資料，總結出一個系列文章供大家參考
我們首先來了解一下表鎖和行鎖：表鎖是指對一整張表加鎖，一般是 DDL 處理時使用；而行鎖則是鎖定某一行或者某幾行，或者行與行之間的間隙。
表鎖由 MySQL Server 實現，行鎖則是存儲引擎實現，不同的引擎實現的不同。在 MySQL 的常用引擎中 InnoDB 支持行鎖，而 MyISAM 則只能使用 MySQL Server 提供的表鎖。
表鎖由 MySQL Server 實現，一般在執行 DDL 語句時會對整個表進行加鎖，比如說 ALTER TABLE 等操作。在執行 SQL 語句時，也可以明確指定對某個表進行加鎖。
表鎖使用的是一次性鎖技術，也就是說，在會話開始的地方使用 lock 命令將後續需要用到的表都加上鎖，在表釋放前，只能訪問這些加鎖的表，不能訪問其他表，直到最後通過 unlock tables 釋放所有表鎖。
除了使用 unlock tables 顯示釋放鎖之外，會話持有其他表鎖時執行lock table 語句會釋放會話之前持有的鎖；會話持有其他表鎖時執行 start transaction 或者 begin 開啟事務時，也會釋放之前持有的鎖。
不同存儲引擎的行鎖實現不同，後續沒有特別說明，則行鎖特指 InnoDB 實現的行鎖。
在了解 InnoDB 的加鎖原理前，需要對其存儲結構有一定的了解。InnoDB 是聚簇索引，也就是 B+樹的葉節點既存儲了主鍵索引也存儲了數據行。
而 InnoDB 的二級索引的葉節點存儲的則是主鍵值，所以通過二級索引查詢數據時，還需要拿對應的主鍵去聚簇索引中再次進行查詢。關於 InnoDB 和 MyISAM 的索引的詳細知識可以閱讀《Mysql探索(一):B+Tree索引》一文。
下面以兩條 SQL 的執行為例，講解一下 InnoDB 對於單行數據的加鎖原理。
第一條 SQL 使用主鍵索引來查詢，則只需要在 id = 49 這個主鍵索引上加上寫鎖；第二條 SQL 則使用二級索引來查詢，則首先在 name = Tom 這個索引上加寫鎖，然後由於使用 InnoDB 二級索引還需再次根據主鍵索引查詢，所以還需要在 id = 49 這個主鍵索引上加寫鎖，如上圖所示。
也就是說使用主鍵索引需要加一把鎖，使用二級索引需要在二級索引和主鍵索引上各加一把鎖。
根據索引對單行數據進行更新的加鎖原理了解了，那如果更新操作涉及多個行呢，比如下面 SQL 的執行場景。
上述 SQL 的執行過程如下圖所示。MySQL Server 會根據 WHERE 條件讀取第一條滿足條件的記錄，然後 InnoDB 引擎會將第一條記錄返回並加鎖，接著 MySQL Server 發起更新改行記錄的 UPDATE 請求，更新這條記錄。一條記錄操作完成，再讀取下一條記錄，直至沒有匹配的記錄為止。
這種場景下的鎖的釋放較為複雜，有多種的優化方式，我對這塊暫時還沒有了解
下面主要依次介紹 InnoDB 中鎖的模式和類型，鎖的類型是指鎖的粒度或者鎖具體加在什麼地方；而鎖模式描述的是鎖的兼容性，也就是加的是什麼鎖，比如寫鎖或者讀鎖。
鎖的模式有：讀意向鎖，寫意向鎖，讀鎖，寫鎖和自增鎖(auto_inc)，下面我們依次來看。
讀鎖，又稱共享鎖（Share locks，簡稱 S 鎖），加了讀鎖的記錄，所有的事務都可以讀取，但是不能修改，並且可同時有多個事務對記錄加讀鎖。
寫鎖，又稱排他鎖（Exclusive locks，簡稱 X 鎖），或獨占鎖，對記錄加了排他鎖之後，只有擁有該鎖的事務可以讀取和修改，其他事務都不可以讀取和修改，並且同一時間只能有一個事務加寫鎖。
由於表鎖和行鎖雖然鎖定範圍不同，但是會相互衝突。所以當你要加表鎖時，勢必要先遍歷該表的所有記錄，判斷是否加有排他鎖。這種遍歷檢查的方式顯然是一種低效的方式，MySQL 引入了意向鎖，來檢測表鎖和行鎖的衝突。
意向鎖也是表級鎖，也可分為讀意向鎖（IS 鎖）和寫意向鎖（IX 鎖）。當事務要在記錄上加上讀鎖或寫鎖時，要首先在表上加上意向鎖。這樣判斷表中是否有記錄加鎖就很簡單了，只要看下錶上是否有意向鎖就行了。
意向鎖之間是不會產生衝突的，也不和 AUTO_INC 表鎖衝突，它只會阻塞表級讀鎖或表級寫鎖，另外，意向鎖也不會和行鎖衝突，行鎖只會和行鎖衝突。
AUTOINC 鎖又叫自增鎖（一般簡寫成 AI 鎖），是一種表鎖，當表中有自增列（AUTOINCREMENT）時出現。
當插入表中有自增列時，資料庫需要自動生成自增值，它會先為該表加 AUTOINC 表鎖，阻塞其他事務的插入操作，這樣保證生成的自增值肯定是唯一的。AUTOINC 鎖具有如下特點：
顯然，AUTOINC 表鎖會導致並發插入的效率降低，為了提高插入的並發性，MySQL 從 5.1.22 版本開始，引入了一種可選的輕量級鎖（mutex）機制來代替 AUTOINC 鎖，可以通過參數 innodbautoinclockmode 來靈活控制分配自增值時的並發策略。具體可以參考 MySQL 的 AUTOINCREMENT Handling in InnoDB 一文
下面是各個表鎖之間的兼容矩陣。
總結起來有下面幾點：
根據鎖的粒度可以把鎖細分為表鎖和行鎖，行鎖根據場景的不同又可以進一步細分，依次為 Next-Key Lock，Gap Lock 間隙鎖，Record Lock 記錄鎖和插入意向 GAP 鎖。
不同的鎖鎖定的位置是不同的，比如說記錄鎖只鎖住對應的記錄，而間隙鎖鎖住記錄和記錄之間的間隔，Next-Key Lock 則所屬記錄和記錄之前的間隙。不同類型鎖的鎖定範圍大致如下圖所示。
下面我們來依次了解一下不同的類型的鎖。
記錄鎖是最簡單的行鎖，並沒有什麼好說的。上邊描述 InnoDB 加鎖原理中的鎖就是記錄鎖，只鎖住 id = 49 或者 name = 'Tom' 這一條記錄。
當 SQL 語句無法使用索引時，會進行全表掃描，這個時候 MySQL 會給整張表的所有數據行加記錄鎖，再由 MySQL Server 層進行過濾。
但是，在 MySQL Server 層進行過濾的時候，如果發現不滿足 WHERE 條件，會釋放對應記錄的鎖。這樣做，保證了最後只會持有滿足條件記錄上的鎖，但是每條記錄的加鎖操作還是不能省略的。
所以更新操作必須要根據索引進行操作，沒有索引時，不僅會消耗大量的鎖資源，增加資料庫的開銷，還會極大的降低了資料庫的並發性能。
還是最開始更新用戶年齡的例子，如果 id = 49 這條記錄不存在，這個 SQL 語句還會加鎖嗎？答案是可能有，這取決於資料庫的隔離級別。這種情況下，在 RC 隔離級別不會加任何鎖，在 RR 隔離級別會在 id = 49 前後兩個索引之間加上間隙鎖。
間隙鎖是一種加在兩個索引之間的鎖，或者加在第一個索引之前，或最後一個索引之後的間隙。這個間隙可以跨一個索引記錄，多個索引記錄，甚至是空的。使用間隙鎖可以防止其他事務在這個範圍內插入或修改記錄，保證兩次讀取這個範圍內的記錄不會變，從而不會出現幻讀現象。
值得注意的是，間隙鎖和間隙鎖之間是互不衝突的，間隙鎖唯一的作用就是為了防止其他事務的插入，所以加間隙 S 鎖和加間隙 X 鎖沒有任何區別。
Next-key鎖是記錄鎖和間隙鎖的組合，它指的是加在某條記錄以及這條記錄前面間隙上的鎖。假設一個索引包含 15、18、20 ，30，49，50 這幾個值，可能的 Next-key 鎖如下：
通常我們都用這種左開右閉區間來表示 Next-key 鎖，其中，圓括號表示不包含該記錄，方括號表示包含該記錄。前面四個都是 Next-key 鎖，最後一個為間隙鎖。和間隙鎖一樣，在 RC 隔離級別下沒有 Next-key 鎖，只有 RR 隔離級別才有。
還是之前的例子，如果 id 不是主鍵，而是二級索引，且不是唯一索引，那麼這個 SQL 在 RR 隔離級別下就會加如下的 Next-key 鎖 (30, 49](49, 50)
此時如果插入一條 id = 31 的記錄將會阻塞住。之所以要把 id = 49 前後的間隙都鎖住，仍然是為了解決幻讀問題，因為 id 是非唯一索引，所以 id = 49 可能會有多條記錄，為了防止再插入一條 id = 49 的記錄。
插入意向鎖是一種特殊的間隙鎖（簡寫成 II GAP）表示插入的意向，只有在 INSERT 的時候才會有這個鎖。
注意，這個鎖雖然也叫意向鎖，但是和上面介紹的表級意向鎖是兩個完全不同的概念，不要搞混了。
插入意向鎖和插入意向鎖之間互不衝突，所以可以在同一個間隙中有多個事務同時插入不同索引的記錄。譬如在上面的例子中，id = 30 和 id = 49 之間如果有兩個事務要同時分別插入 id = 32 和 id = 33 是沒問題的，雖然兩個事務都會在 id = 30 和 id = 50 之間加上插入意向鎖，但是不會衝突。
插入意向鎖只會和間隙鎖或 Next-key 鎖衝突，正如上面所說，間隙鎖唯一的作用就是防止其他事務插入記錄造成幻讀，正是由於在執行 INSERT 語句時需要加插入意向鎖，而插入意向鎖和間隙鎖衝突，從而阻止了插入操作的執行。
不同類型鎖的兼容下如下圖所示。
其中，第一行表示已有的鎖，第一列表示要加的鎖。插入意向鎖較為特殊，所以我們先對插入意向鎖做個總結，如下：
其他類型的鎖的規則較為簡單：