字節二面，兩個事務執行 SQL 語句的過程中，導致死鎖
大家好，我是小林。
之前收到讀者面試字節時，被問到一個關于 MySQL 的問題。
如果對 MySQL 加鎖機制比較熟悉的同學，應該一眼就能看出會發生死鎖。
但是具體加了什么鎖而導致死鎖，是需要我們具體分析的。
接下來，就跟聊聊上面兩個事務執行 SQL 語句的過程中，加了什么鎖，從而導致死鎖的。
準備工作
先創建一張 t_student 表，假設除了 id 字段，其他字段都是普通字段。
CREATE TABLE `t_student` (
`id` int NOT NULL,
`no` varchar(255) DEFAULT NULL,
`name` varchar(255) DEFAULT NULL,
`age` int DEFAULT NULL,
`score` int DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
然后，插入相關的數據后，t_student 表中的記錄如下：
開始實驗
在實驗開始前，先說明下實驗環境：
- MySQL 版本：8.0.26
- 隔離級別：可重復讀（RR）
啟動兩個事務，按照題目的 SQL 執行順序，過程如下表格：
可以看到，事務 A 和 事務 B 都在執行 insert 語句后，都陷入了等待狀態（前提沒有打開死鎖檢測），也就是發生了死鎖，因為都在相互等待對方釋放鎖。
為什么會發生死鎖？
我們可以通過 select * from performance_schema.data_locks\G; 這條語句，查看事務執行 SQL 過程中加了什么鎖。
接下來，針對每一條 SQL 語句分析具體加了什么鎖。
Time 1 階段加鎖分析
Time 1 階段，事務 A 執行以下語句：
# 事務 A
mysql> begin;
Query OK, 0 rows affected (0.00 sec)
mysql> update t_student set score = 100 where id = 25;
Query OK, 0 rows affected (0.01 sec)
Rows matched: 0 Changed: 0 Warnings: 0
然后執行 select * from performance_schema.data_locks\G; 這條語句，查看事務 A 此時加了什么鎖。
從上圖可以看到，共加了兩個鎖，分別是：
- 表鎖：X 類型的意向鎖；
- 行鎖：X 類型的間隙鎖；
這里我們重點關注行鎖，圖中 LOCK_TYPE 中的 RECORD 表示行級鎖，而不是記錄鎖的意思，通過 LOCK_MODE 可以確認是 next-key 鎖，還是間隙鎖，還是記錄鎖：
- 如果 LOCK_MODE 為X，說明是 next-key 鎖；
- 如果 LOCK_MODE 為X, REC_NOT_GAP，說明是記錄鎖；
- 如果 LOCK_MODE 為X, GAP，說明是間隙鎖；
因此，此時事務 A 在主鍵索引（INDEX_NAME : PRIMARY）上加的是間隙鎖，鎖范圍是(20, 30)。
Time 2 階段加鎖分析
Time 2 階段，事務 B 執行以下語句：
# 事務 B
mysql> begin;
Query OK, 0 rows affected (0.00 sec)
mysql> update t_student set score = 100 where id = 26;
Query OK, 0 rows affected (0.01 sec)
Rows matched: 0 Changed: 0 Warnings: 0
然后執行 select * from performance_schema.data_locks\G; 這條語句，查看事務 B 此時加了什么鎖。
從上圖可以看到，共加了兩個鎖，分別是：
- 表鎖：X 類型的意向鎖；
- 行鎖：X 類型的間隙鎖；
因此，此時事務 B 在主鍵索引（INDEX_NAME : PRIMARY）上加的是間隙鎖，鎖范圍是(20, 30)。
事務 A 和 事務 B 的間隙鎖范圍都是一樣的，為什么不會沖突？
兩個事務的間隙鎖之間是相互兼容的，不會產生沖突。
在MySQL官網上還有一段非常關鍵的描述：
Gap locks in InnoDB are “purely inhibitive”, which means that their only purpose is to prevent other transactions from Inserting to the gap. Gap locks can co-exist. A gap lock taken by one transaction does not prevent another transaction from taking a gap lock on the same gap. There is no difference between shared and exclusive gap locks. They do not conflict with each other, and they perform the same function.
間隙鎖的意義只在于阻止區間被插入，因此是可以共存的。一個事務獲取的間隙鎖不會阻止另一個事務獲取同一個間隙范圍的間隙鎖，共享和排他的間隙鎖是沒有區別的，他們相互不沖突，且功能相同。
Time 3 階段加鎖分析
Time 3，事務 A 插入了一條記錄：
# Time 3 階段，事務 A 插入了一條記錄
mysql> insert into t_student(id, no, name, age,score) value (25, 'S0025', 'sony', 28, 90);
/// 阻塞等待......
此時，事務 A 就陷入了等待狀態。
然后執行 select * from performance_schema.data_locks\G; 這條語句，查看事務 A 在獲取什么鎖而導致被阻塞。
可以看到，事務 A 的狀態為等待狀態（LOCK_STATUS: WAITING），因為向事務 B 生成的間隙鎖（范圍 (20, 30)?）中插入了一條記錄，所以事務 A 的插入操作生成了一個插入意向鎖（LOCK_MODE:INSERT_INTENTION）。
插入意向鎖是什么？
注意！插入意向鎖名字里雖然有意向鎖這三個字，但是它并不是意向鎖，它屬于行級鎖，是一種特殊的間隙鎖。
在MySQL的官方文檔中有以下重要描述：
An Insert intention lock is a type of gap lock set by Insert operations prior to row Insertion. This lock signals the intent to Insert in such a way that multiple transactions Inserting into the same index gap need not wait for each other if they are not Inserting at the same position within the gap. Suppose that there are index records with values of 4 and 7. Separate transactions that attempt to Insert values of 5 and 6, respectively, each lock the gap between 4 and 7 with Insert intention locks prior to obtaining the exclusive lock on the Inserted row, but do not block each other because the rows are nonconflicting.
這段話表明盡管插入意向鎖是一種特殊的間隙鎖，但不同于間隙鎖的是，該鎖只用于并發插入操作。
如果說間隙鎖鎖住的是一個區間，那么「插入意向鎖」鎖住的就是一個點。因而從這個角度來說，插入意向鎖確實是一種特殊的間隙鎖。
插入意向鎖與間隙鎖的另一個非常重要的差別是：盡管「插入意向鎖」也屬于間隙鎖，但兩個事務卻不能在同一時間內，一個擁有間隙鎖，另一個擁有該間隙區間內的插入意向鎖（當然，插入意向鎖如果不在間隙鎖區間內則是可以的）。所以，插入意向鎖和間隙鎖之間是沖突的。
另外，我補充一點，插入意向鎖的生成時機：
每插入一條新記錄，都需要看一下待插入記錄的下一條記錄上是否已經被加了間隙鎖，如果已加間隙鎖，那 Insert 語句會被阻塞，并生成一個插入意向鎖 。
Time 4 階段加鎖分析
Time 4，事務 B 插入了一條記錄：
# Time 4 階段，事務 B 插入了一條記錄
mysql> insert into t_student(id, no, name, age,score) value (26, 'S0026', 'ace', 28, 90);
/// 阻塞等待......
此時，事務 B 就陷入了等待狀態。
然后執行 select * from performance_schema.data_locks\G; 這條語句，查看事務 B 在獲取什么鎖而導致被阻塞。
可以看到，事務 B 在生成插入意向鎖時而導致被阻塞，這是因為事務 B 向事務 A 生成的間隙鎖（范圍 (20, 30)）中插入了一條記錄，而插入意向鎖和間隙鎖是沖突的，所以事務 B 在獲取插入意向鎖時就陷入了等待狀態。
最后回答，為什么會發生死鎖？
本次案例中，事務 A 和事務 B 在執行完后 update 語句后都持有范圍為(20, 30）的間隙鎖，而接下來的插入操作為了獲取到插入意向鎖，都在等待對方事務的間隙鎖釋放，于是就造成了循環等待，滿足了死鎖的四個條件：互斥、占有且等待、不可強占用、循環等待，因此發生了死鎖。
總結
兩個事務即使生成的間隙鎖的范圍是一樣的，也不會發生沖突，因為間隙鎖目的是為了防止其他事務插入數據，因此間隙鎖與間隙鎖之間是相互兼容的。
在執行插入語句時，如果插入的記錄在其他事務持有間隙鎖范圍內，插入語句就會被阻塞，因為插入語句在碰到間隙鎖時，會生成一個插入意向鎖，然后插入意向鎖和間隙鎖之間是互斥的關系。
如果兩個事務分別向對方持有的間隙鎖范圍內插入一條記錄，而插入操作為了獲取到插入意向鎖，都在等待對方事務的間隙鎖釋放，于是就造成了循環等待，滿足了死鎖的四個條件：互斥、占有且等待、不可強占用、循環等待，因此發生了死鎖。
