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

2022 年 1 月 16 日
筆記
【資料庫】-- MYSQL

在生產環境中如果出現MySQL死鎖問題該如何排查和解決呢，本文將模擬真實死鎖場景進行排查，最後總結下實際開發中如何盡量避免死鎖發生。

一、準備好相關數據和環境

當前自己的數據版本是8.0.22

mysql> select @@version;
+-----------+
| @@version |
+-----------+
| 8.0.22    |
+-----------+
1 row in set (0.00 sec)

資料庫隔離級別(默認隔離級別)

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

自動提交關閉

mysql> select @@autocommit;
+--------------+
| @@autocommit |
+--------------+
|            1 |
+--------------+
1 row in set (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)

表結構

這個age為 非唯一索引，這點對下面整個案例非常重要。

-- id是自增主鍵，age是非唯一索引，name普通欄位
CREATE TABLE `user` (
  `id` int NOT NULL AUTO_INCREMENT COMMENT '主鍵',
  `age` int DEFAULT NULL COMMENT '年齡',
  `name` varchar(255)  DEFAULT NULL COMMENT '姓名',
  PRIMARY KEY (`id`),
  KEY `idx_age` (`age`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8 COMMENT='用戶資訊表';

表中暫時先插入兩條數據

二、模擬出真實死鎖案例

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

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

mysql> update  user  set name = 'wangwu' where age= 20;
Query OK, 1 row affected (0.00 sec)

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

mysql> update  user  set name = 'zhaoliu' where age= 10;
Query OK, 1 row affected (0.00 sec)

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

mysql> insert into user values (null, 15, "tianqi");

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

insert into user values (null, 30, "wangba");
Query OK, 1 row affected (0.00 sec)

事務A的插入操作變成報錯。

上面四步操作後，我們分別對事務A和事務B進行commit操作。

mysql> commit;
Query OK, 0 rows affected (0.00 sec)

我們再來看資料庫中表的數據。

我們發現，事務B的所有操作最終都成功了，而事務A的操作因為報錯都回滾了。所以事務A的操作都失敗。

那既然是死鎖，為什麼回滾事務A,而不是事務B,是隨機的還是有機制在裡面？

我們可以理解死鎖是資料庫對事務的保護機制,一旦發生死鎖,MySQL會選擇相對小的事務(undo較少的)進行回滾。

三、查看分析死鎖日誌

可以用 show engine innodb status，查看最近一次死鎖日誌哈，執行後，死鎖日誌如下(只展示部分日誌)：

LATEST DETECTED DEADLOCK
------------------------
2021-12-24 06:02:52 0x7ff7074f8700
*** (1) TRANSACTION:
TRANSACTION 2554368, ACTIVE 22 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

INSERT INTO user VALUES (NULL, 15, "tianqi")

*** (1) HOLDS THE LOCK(S):
RECORD LOCKS space id 309 page no 5 n bits 72 index idx_age of table `mall_goods`.`user` trx id 2554368 lock_mode X
Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 0
 0: len 8; hex 73757072656d756d; asc supremum;;

*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 309 page no 5 n bits 72 index idx_age of table `mall_goods`.`user` trx id 2554368 lock_mode X locks gap before rec insert intention waiting
Record lock, heap no 4 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
 0: len 4; hex 80000014; asc     ;;
 1: len 4; hex 80000002; asc     ;;

*** (2) TRANSACTION:
TRANSACTION 2554369, ACTIVE 14 sec inserting
mysql tables in use 1, locked 1
LOCK WAIT 5 lock struct(s), heap size 1136, 4 row lock(s), undo log entries 2
INSERT INTO user VALUES (NULL, 30, "wangba")

*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 309 page no 5 n bits 72 index idx_age of table `mall_goods`.`user` trx id 2554369 lock_mode X locks gap before rec
Record lock, heap no 4 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
 0: len 4; hex 80000014; asc     ;;
 1: len 4; hex 80000002; asc     ;;


*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 309 page no 5 n bits 72 index idx_age of table `mall_goods`.`user` trx id 2554369 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、事務A相關日誌

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

2）查看事務1正在執行的sql

insert into user values (null, 15, "tianqi")

查看當前事務已佔有的鎖和等待其它事務釋放的鎖

2、事務B相關日誌

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

2）查看事務2正在執行的sql

insert into user values (null, 30, "wangba")

查看當前事務已佔有的鎖和等待其它事務釋放的鎖

3、總結

這裡把一些關鍵的日誌截圖了下

我們把這張圖換一種方式畫下

1）從圖中可以很明顯的看出,事務1和事務2都在等對方的鎖釋放，所以導致了死鎖問題。而且最終是事務1進行了回滾。

2）這個日誌提供比較重要的資訊就是我們可以看出的是哪兩條sql在互相一直等待其它事務的鎖釋放而產生了死鎖，也知道是哪個索引導致產生的死鎖，同時也知道最終哪個事務

被回滾了。

3）如果上面的資訊還不能幫你定位解決問題，那可以問資料庫DB要詳細的binlog日誌來分析這段時間這兩個事務具體執行的所有sql。

四、總結分析案例中產生死鎖的原因

這個分析就需要對MySQL中的各種鎖機制有所了解，還不清楚的話可以看我之前寫的兩篇文章，看完你就清楚我下面所寫的了。

1、事務A的SQL產生了哪些鎖

1) 事務A的update語句產生哪些鎖

我們先來看

update  user  set name = 'wangwu' where age= 20;

記錄鎖

因為是等值查詢，所以這裡會在滿足age=20的所有數據請求一個記錄鎖。

間隙鎖

因為這裡是非唯一索引的等值查詢,所以一樣會產生間隙鎖(如果是唯一索引的等值查詢那就不會產生間隙鎖，只會有記錄鎖),因為這裡只有2條記錄

所以左邊為(10,20),右邊因為沒有記錄了，所以請求間隙鎖的範圍就是(20,+∞),加一起就是(10,20) +（20,+∞)。

Next-Key鎖

Next-Key鎖=記錄鎖+間隙鎖，所以該Update語句就有了（10,+∞)的 Next-Key鎖

事務A的install語句產生哪些鎖

INSERT INTO user VALUES (NULL, 15, "tianqi");

間隙鎖

因為age 15(在10和20之間)，所以需要請求加(10,20)的間隙鎖

插入意向鎖（Insert Intention）

插入意向鎖是在插入一行記錄操作之前設置的一種間隙鎖，這個鎖釋放了一種插入方式的訊號，即事務A需要插入意向鎖(10,20),這個插入鎖的作用就是提高插入效率的，在分析

死鎖的時候我們可以不用關心它，只關心上面間隙鎖就好了。

2、事務B的SQL產生了哪些鎖

事務B的update語句產生哪些鎖

我們先來看

update  user  set name = 'zhaoliu' where age= 10

記錄鎖

因為是等值查詢，所以這裡會在滿足age=10的所有數據請求一個記錄鎖。

間隙鎖

因為左邊沒有記錄,右邊有一個age=20的記錄，所以間隙鎖的範圍是(-∞,10),右邊為(10,20),一起就是(-∞,10)+(10,20)。

Next-Key鎖

Next-Key鎖=記錄鎖+間隙鎖，所以該Update語句就有了（-∞,20)的 Next-Key鎖

事務A的install語句產生哪些鎖

INSERT INTO user VALUES (NULL, 30, "wangba")

間隙鎖

因為age 30(左邊是20，右邊沒有值)，所以需要請求加(20,+∞)的間隙鎖

插入意向鎖（Insert Intention）

(20,+∞)

鎖都分析清楚了，接下來再來看下是什麼地方導致死鎖的呢？

這樣以來產生整個死鎖的原因也就清楚了，不過這裡再補充兩點

1）MySQL的間隙鎖雖然有左開右閉的原則,但是其實這個並不完全正確,因為它有可能是左閉右開,也可能是左開右開，它會跟你插入主鍵值位置有關，具體的可以看我之前寫的

一篇文章裡面有完整示例MySQL記錄鎖、間隙鎖、臨鍵鎖小案例演示。所以這裡間隙鎖寫的都是左開右開的範圍，可能臨界點有點模糊，但不影響分析這個案例的死鎖問題。

2）通過事務A和事務B的update語句，我們可以發現其實它們都持有間隙鎖(10,20)的這段範圍,說明間隙鎖範圍是可以相互兼容的，意思就是只要你的10不在我（10,+∞)的間隙鎖

範圍內，那就可以產生部分重合的間隙鎖，也就是這裡的(10,20)。

五、實際開發中如何盡量避免死鎖發生

一般來講在實際開發中，很少會發生死鎖的情況，尤其是在業務並發量不是很大的情況下。在並發很大的情況下可能會存在偶爾產生死鎖。

不過呢，在自己實際開發中，有遇到過請求一個介面出現100%概率死鎖的情況。

當時的場景其實很簡單。一段業務程式碼中，有去走Dubbo調其它介面服務,這就存在了兩個事務,結果各自事務提交的時候，都需要等待對方的鎖釋放，就導致每次都發生死鎖超時。

這其實是一種程式碼不規範而導致死鎖的發生。這裡也總結下如何盡量避免死鎖發生。

1）不同的應用訪問同一組表時，應盡量約定以相同的順序訪問各表。對一個表而言，應盡量以固定的順序存取表中的行。這點真的很重要,它可以明顯的減少死鎖的發生。

舉例：好比有a,b兩張表，如果事務1先a後b,事務2先b後a,那就可能存在相互等待產生死鎖。那如果事務1和事務2都先a後b，那事務1先拿到a的鎖，事務2再去拿a的鎖，如果

鎖衝突那就會等待事務1釋放鎖，那自然事務2就不會拿到b的鎖，那就不會堵塞事務1拿到b的鎖，這樣就避免死鎖了。

2）在主鍵等值更新的時候，盡量先查詢看資料庫中有沒有滿足條件的數據，如果不存在就不用更新，存在才更新。為什麼要這麼做呢，因為如果去更新一條資料庫不存在的數據，

一樣會產生間隙鎖。

舉例：如果表中只有id=1和id=5的數據，那麼如果你更新id=3的sql，因為這條記錄表中不存在，那就會產生一個(1,5)的間隙鎖，但其實這個鎖就是多餘的，因為你去更新一個

數據都不存在的數據沒有任何意義。

3）盡量使用主鍵更新數據,因為主鍵是唯一索引，在等值查詢能查到數據的情況下只會產生行鎖，不會產生間隙鎖，這樣產生死鎖的概率就減少了。當然如果是範圍查詢，

一樣會產生間隙鎖。

4）避免長事務，小事務發送鎖衝突的幾率也小。這點應該很好理解。

5）在允許幻讀和不可重複度的情況下，盡量使用RC的隔離級別，避免gap lock造成的死鎖，因為產生死鎖經常都跟間隙鎖有關，間隙鎖的存在本身也是在RR隔離級別來

解決幻讀的一種措施。

感謝

這篇文章給自己提供了很好的思路，這篇文章也基本上按照這個思路往下寫的

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

聲明: 公眾號如需轉載該篇文章,發表文章的頭部一定要告知是轉至公眾號: 後端元宇宙。同時也可以問本人要markdown原稿和原圖片。其它情況一律禁止轉載！

Tags: 【資料庫】-- MYSQL