手把手教你分析 MySQL 死鎖問題

幫一位朋友分析了一個死鎖問題，所以有了這篇圖文詳細的博文，非常詳細，建議收藏起來慢慢研究：

發生死鎖了，如何排查和解決呢？本文將跟你一起探討這個問題

• 準備好數據環境

• 模擬死鎖案發

• 分析死鎖日誌

• 分析死鎖結果

環境準備

數據庫隔離級別：

mysql> select @@tx_isolation;
+-----------------+
| @@tx_isolation  |
+-----------------+
| REPEATABLE-READ |
+-----------------+
1 row in set, 1 warning (0.00 sec)

自動提交關閉：

mysql> set autocommit=0;
Query OK, 0 rows affected (0.00 sec)
mysql> select @@autocommit;
+--------------+
| @@autocommit |
+--------------+
|            0 |
+--------------+
1 row in set (0.00 sec)

表結構:

//id是自增主鍵，name是非唯一索引，balance普通字段
CREATE TABLE `account` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) DEFAULT NULL,
  `balance` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_name` (`name`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8;

表中的數據：

模擬併發

開啓兩個終端模擬事務併發情況，執行順序以及實驗現象如下：

1）事務 A 執行更新操作，更新成功

mysql> update  account  set balance =1000 where name ='Wei';
Query OK, 1 row affected (0.01 sec)

2）事務 B 執行更新操作，更新成功

mysql> update  account  set balance =1000 where name ='Eason';
Query OK, 1 row affected (0.01 sec)

3）事務 A 執行插入操作，陷入阻塞~

mysql
>
 insert 
into
 account values
(
null
,
'Jay'
,
100
);

這時候可以用 select*frominformation_schema.innodb_locks;查看鎖情況：

4）事務 B 執行插入操作，插入成功，同時事務 A 的插入由阻塞變爲死鎖 error。

mysql> insert into account values(null,'Yan',100);
Query OK, 1 row affected (0.01 sec)

鎖介紹

在分析死鎖日誌前，先做一下鎖介紹，哈哈~

主要介紹一下兼容性以及鎖模式類型的鎖：

共享鎖與排他鎖

InnoDB 實現了標準的行級鎖，包括兩種：共享鎖（簡稱 s 鎖）、排它鎖（簡稱 x 鎖）。

• 共享鎖（S 鎖）：允許持鎖事務讀取一行。

• 排他鎖（X 鎖）：允許持鎖事務更新或者刪除一行。

如果事務 T1 持有行 r 的 s 鎖，那麼另一個事務 T2 請求 r 的鎖時，會做如下處理：

• T2 請求 s 鎖立即被允許，結果 T1 T2 都持有 r 行的 s 鎖

• T2 請求 x 鎖不能被立即允許

如果 T1 持有 r 的 x 鎖，那麼 T2 請求 r 的 x、s 鎖都不能被立即允許，T2 必須等待 T1 釋放 x 鎖纔可以，因爲 X 鎖與任何的鎖都不兼容。

意向鎖

• 意向共享鎖 (IS 鎖)：事務想要獲得一張表中某幾行的共享鎖

• 意向排他鎖 (IX 鎖)：事務想要獲得一張表中某幾行的排他鎖

比如：事務 1 在表 1 上加了 S 鎖後，事務 2 想要更改某行記錄，需要添加 IX 鎖，由於不兼容，所以需要等待 S 鎖釋放；如果事務 1 在表 1 上加了 IS 鎖，事務 2 添加的 IX 鎖與 IS 鎖兼容，就可以操作，這就實現了更細粒度的加鎖。

InnoDB 存儲引擎中鎖的兼容性如下表：

記錄鎖（Record Locks）

• 記錄鎖是最簡單的行鎖，僅僅鎖住一行。如： SELECT c1 FROM t WHERE c1=10FOR UPDATE

• 記錄鎖永遠都是加在索引上的，即使一個表沒有索引，InnoDB 也會隱式的創建一個索引，並使用這個索引實施記錄鎖。

• 會阻塞其他事務對其插入、更新、刪除

記錄鎖的事務數據（關鍵詞：lock_mode X locks rec butnotgap），記錄如下：

RECORD LOCKS space id 58 page no 3 n bits 72 index `PRIMARY` of table `test`.`t` 
trx id 10078 lock_mode X locks rec but not gap
Record lock, heap no 2 PHYSICAL RECORD: n_fields 3; compact format; info bits 0
 0: len 4; hex 8000000a; asc     ;;
 1: len 6; hex 00000000274f; asc     'O;;
 2: len 7; hex b60000019d0110; asc        ;;

間隙鎖（Gap Locks）

• 間隙鎖是一種加在兩個索引之間的鎖，或者加在第一個索引之前，或最後一個索引之後的間隙。

• 使用間隙鎖鎖住的是一個區間，而不僅僅是這個區間中的每一條數據。

• 間隙鎖只阻止其他事務插入到間隙中，他們不阻止其他事務在同一個間隙上獲得間隙鎖，所以 gap x lock 和 gap s lock 有相同的作用。

間隙鎖的事務數據（關鍵詞：gap before rec），記錄如下：

RECORD LOCKS space id 177 page no 4 n bits 80 index idx_name of table `test2`.`account` 
trx id 38049 lock_mode X locks gap before rec
Record lock, heap no 6 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
 0: len 3; hex 576569; asc Wei;;
 1: len 4; hex 80000002; asc     ;;

Next-Key Locks

• Next-key 鎖是記錄鎖和間隙鎖的組合，它指的是加在某條記錄以及這條記錄前面間隙上的鎖。

插入意向鎖（Insert Intention）

• 插入意向鎖是在插入一行記錄操作之前設置的一種間隙鎖，這個鎖釋放了一種插入方式的信號，亦即多個事務在相同的索引間隙插入時如果不是插入間隙中相同的位置就不需要互相等待。

• 假設有索引值 4、7，幾個不同的事務準備插入 5、6，每個鎖都在獲得插入行的獨佔鎖之前用插入意向鎖各自鎖住了 4、7 之間的間隙，但是不阻塞對方因爲插入行不衝突。

事務數據類似於下面：

RECORD LOCKS space id 31 page no 3 n bits 72 index `PRIMARY` of table `test`.`child`
trx id 8731 lock_mode X locks gap before rec insert intention waiting
Record lock, heap no 3 PHYSICAL RECORD: n_fields 3; compact format; info bits 0
 0: len 4; hex 80000066; asc    f;;
 1: len 6; hex 000000002215; asc     " ;;
 2: len 7; hex 9000000172011c; asc     r  ;;...

鎖模式兼容矩陣（橫向是已持有鎖，縱向是正在請求的鎖）：

如何讀懂死鎖日誌？

show engine innodb status

可以用 show engine innodb status，查看最近一次死鎖日誌哈~，執行後，死鎖日誌如下：

2020-04-11 00:35:55 0x243c
*** (1) TRANSACTION:
TRANSACTION 38048, ACTIVE 92 sec inserting
mysql tables in use 1, locked 1
LOCK WAIT 4 lock struct(s), heap size 1136, 4 row lock(s), undo log entries 2
MySQL thread id 53, OS thread handle 2300, query id 2362 localhost ::1 root update
insert into account values(null,'Jay',100)
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 177 page no 4 n bits 80 index idx_name of table `test2`.`account` 
trx id 38048 lock_mode X locks gap before rec insert intention waiting
Record lock, heap no 6 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
 0: len 3; hex 576569; asc Wei;;
 1: len 4; hex 80000002; asc     ;;
*** (2) TRANSACTION:
TRANSACTION 38049, ACTIVE 72 sec inserting, thread declared inside InnoDB 5000
mysql tables in use 1, locked 1
5 lock struct(s), heap size 1136, 4 row lock(s), undo log entries 2
MySQL thread id 52, OS thread handle 9276, query id 2363 localhost ::1 root update
insert into account  values(null,'Yan',100)
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 177 page no 4 n bits 80 index idx_name of table `test2`.`account` 
trx id 38049 lock_mode X locks gap before rec
Record lock, heap no 6 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
 0: len 3; hex 576569; asc Wei;;
 1: len 4; hex 80000002; asc     ;;
*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 177 page no 4 n bits 80 index idx_name of table `test2`.`account` 
trx id 38049 lock_mode X insert intention waiting
Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 0
 0: len 8; hex 73757072656d756d; asc supremum;;
*** WE ROLL BACK TRANSACTION (1)

我們如何分析以上死鎖日誌呢？

第一部分

1）找到關鍵詞 TRANSACTION，事務 38048

2）查看正在執行的 SQL

insert 
into
 account values
(
null
,
'Jay'
,
100
)

3）正在等待鎖釋放 (WAITING FOR THIS LOCK TO BE GRANTED)，插入意向排他鎖（lockmode X locks gap before rec insert intention waiting），普通索引（idxname），物理記錄 (PHYSICAL RECORD)，間隙區間（未知，Wei）;

第二部分

1）找到關鍵詞 TRANSACTION，事務 38049

2）查看正在執行的 SQL

insert 
into
 account  values
(
null
,
'Yan'
,
100
)

3）持有鎖 (HOLDS THE LOCK)，間隙鎖 (lockmode X locks gap before rec)，普通索引 (index idxname)，物理記錄 (physical record)，區間（未知，Wei）;

4）正在等待鎖釋放 (waiting for this lock to be granted)，插入意向鎖 (lockmode X insert intention waiting)，普通索引上 (index idxname)，物理記錄 (physical record)，間隙區間（未知，+∞）;

5）事務 1 回滾 (we roll back transaction 1)；

查看日誌結果

查看日誌可得：

• 事務 A 正在等待的插入意向排他鎖（事務 A 即日誌的事務 1，根據 insert 語句來對號入座的哈），正在事務 B 的懷裏~

• 事務 B 持有間隙鎖，正在等待插入意向排它鎖

這裏面，有些朋友可能有疑惑，

• 事務 A 持有什麼鎖呢？日誌根本看不出來。它又想拿什麼樣的插入意向排他鎖呢？

• 事務 B 拿了具體什麼的間隙鎖呢？它爲什麼也要拿插入意向鎖？

• 死鎖的死循環是怎麼形成的？目前日誌看不出死循環構成呢？

我們接下來一小節詳細分析一波，一個一個問題來~

死鎖分析

死鎖死循環四要素

• 互斥條件：指進程對所分配到的資源進行排它性使用，即在一段時間內某資源只由一個進程佔用。如果此時還有其它進程請求資源，則請求者只能等待，直至佔有資源的進程用畢釋放。

• 請求和保持條件：指進程已經保持至少一個資源，但又提出了新的資源請求，而該資源已被其它進程佔有，此時請求進程阻塞，但又對自己已獲得的其它資源保持不放。

• 不剝奪條件：指進程已獲得的資源，在未使用完之前，不能被剝奪，只能在使用完時由自己釋放。

• 環路等待條件：指在發生死鎖時，必然存在一個進程——資源的環形鏈，即進程集合 {P0，P1，P2，···，Pn} 中的 P0 正在等待一個 P1 佔用的資源；P1 正在等待 P2 佔用的資源，……，Pn 正在等待已被 P0 佔用的資源。

事務 A 持有什麼鎖呢？它又想拿什麼樣的插入意向排他鎖呢？

爲了方便記錄，例子用 W 表示 Wei，J 表示 Jay，E 表示 Eason 哈~

我們先來分析事務 A 中 update 語句的加鎖情況~

update  account  
set
 balance 
=
1000
 
where
 name 
=
'Wei'
;

間隙鎖：

• Update 語句會在非唯一索引的 name 加上左區間的間隙鎖，右區間的間隙鎖 (因爲目前表中只有 name='Wei'的一條記錄，所以沒有中間的間隙鎖~)，即（E,W) 和（W，+∞）

• 爲什麼存在間隙鎖？因爲這是 RR 的數據庫隔離級別，用來解決幻讀問題用的~

記錄鎖

• 因爲 name 是索引，所以該 update 語句肯定會加上 W 的記錄鎖

Next-Key 鎖

• Next-Key 鎖 = 記錄鎖 + 間隙鎖，所以該 update 語句就有了（E，W] 的 Next-Key 鎖

綜上所述，事務 A 執行完 update 更新語句，會持有鎖：

• Next-key Lock：（E，W]

• Gap Lock ：（W，+∞）

我們再來分析一波事務 A 中 insert 語句的加鎖情況

insert 
into
 account values
(
null
,
'Jay'
,
100
);

間隙鎖：

• 因爲 Jay(J 在 E 和 W 之間)，所以需要請求加 (E,W) 的間隙鎖

插入意向鎖（Insert Intention）

• 插入意向鎖是在插入一行記錄操作之前設置的一種間隙鎖，這個鎖釋放了一種插入方式的信號，即事務 A 需要插入意向鎖 (E,W)

因此，事務 A 的 update 語句和 insert 語句執行完，它是持有了 （E，W] 的 Next-Key 鎖，（W，+∞）的 Gap 鎖，想拿到 (E,W) 的插入意向排它鎖，等待的鎖跟死鎖日誌是對上的，哈哈~

事務 B 擁有了什麼間隙鎖？它爲什麼也要拿插入意向鎖？

同理，我們再來分析一波事務 B，update 語句的加鎖分析：

update  account  
set
 balance 
=
1000
 
where
 name 
=
'Eason'
;

間隙鎖：

• Update 語句會在非唯一索引的 name 加上左區間的間隙鎖，右區間的間隙鎖 (因爲目前表中只有 name='Eason'的一條記錄，所以沒有中間的間隙鎖~)，即（-∞，E）和（E，W）

記錄鎖

• 因爲 name 是索引，所以該 update 語句肯定會加上 E 的記錄鎖

Next-Key 鎖

• Next-Key 鎖 = 記錄鎖 + 間隙鎖，所以該 Update 語句就有了（-∞，E] 的 Next-Key 鎖

綜上所述，事務 B 執行完 update 更新語句，會持有鎖：

• Next-key Lock：（-∞，E]

• Gap Lock ：（E，W）

我們再來分析一波 B 中 insert 語句的加鎖情況

insert 
into
 account  values
(
null
,
'Yan'
,
100
);

間隙鎖：

• 因爲 Yan(Y 在 W 之後)，所以需要請求加 (W,+∞) 的間隙鎖

插入意向鎖（Insert Intention）

• 插入意向鎖是在插入一行記錄操作之前設置的一種間隙鎖，這個鎖釋放了一種插入方式的信號，即事務 A 需要插入意向鎖 (W,+∞)

所以，事務 B 的 update 語句和 insert 語句執行完，它是持有了 （-∞，E] 的 Next-Key 鎖，（E，W）的 Gap 鎖，想拿到 (W,+∞) 的間隙鎖，即插入意向排它鎖，加鎖情況跟死鎖日誌也是對上的~

死鎖真相還原

接下來呢，讓我們一起還原死鎖真相吧~ 哈哈~

• 事務 A 執行完 Update Wei 的語句，持有（E，W] 的 Next-key Lock，（W，+∞）的 Gap Lock ，插入成功~

• 事務 B 執行完 Update Eason 語句，持有（-∞，E] 的 Next-Key Lock，（E，W）的 Gap Lock，插入成功~

• 事務 A 執行 Insert Jay 的語句時，因爲需要（E，W）的插入意向鎖，但是（E，W）在事務 B 懷裏，所以它陷入心塞~

• 事務 B 執行 Insert Yan 的語句時，因爲需要 (W,+∞) 的插入意向鎖，但是 (W,+∞) 在事務 A 懷裏，所以它也陷入心塞。

• 事務 A 持有（W，+∞）的 Gap Lock，在等待（E，W）的插入意向鎖，事務 B 持有（E，W）的 Gap 鎖，在等待 (W,+∞) 的插入意向鎖，所以形成了死鎖的閉環~（Gap 鎖與插入意向鎖會衝突的，可以看回鎖介紹的鎖模式兼容矩陣哈~）

• 事務 A,B 形成了死鎖閉環後，因爲 Innodb 的底層機制，它會讓其中一個事務讓出資源，另外的事務執行成功，這就是爲什麼你最後看到事務 B 插入成功了，但是事務 A 的插入顯示了 Deadlock found ~

總結

最後，遇到死鎖問題，我們應該怎麼分析呢？

• 模擬死鎖場景

• show engine innodb status; 查看死鎖日誌

• 找出死鎖 SQL

• SQL 加鎖分析，這個可以去官網看哈

• 分析死鎖日誌（持有什麼鎖，等待什麼鎖）

• 熟悉鎖模式兼容矩陣，InnoDB 存儲引擎中鎖的兼容性矩陣。

本文由 Readfog 進行 AMP 轉碼，版權歸原作者所有。
來源：https://mp.weixin.qq.com/s/aDUeXkrRbSMzUm6SvGeprw