資料庫死鎖排查思路分享（糾正篇）

來源：撿田螺的小男孩

前言

大家好，我是田螺。昨天發了一篇死鎖思路排查的文章，發現我舉的程式碼例子，跟實際的業務場景有一點出入。因為我個人做事情是比較嚴謹的，所以今天糾正一下，再發一次。

這篇文章，主要給大家講講資料庫死鎖的排查思路。

• 死鎖現場
• 排查思路
• sql模擬
• 死鎖解決方案

死鎖場景現場

業務場景類似就是這樣：做使用者的資料遷移，醬紫：

把業務禮物表A的資料刪除，然後修改使用者ID後，然後插入到禮物B表。其中，A表和B表，表示同一個禮物邏輯表下的不同分表。

表結構：、

CREATE TABLE `gift_send_flow_0` (
  `id` int NOT NULL AUTO_INCREMENT COMMENT '主鍵',
  `sender_id` int DEFAULT NULL COMMENT '贈送者ID',
  `gift_type` varchar(50) NOT NULL COMMENT '禮物型別',
  `gift_id` varchar(50) NOT NULL COMMENT '禮物ID',
  `gift_name` varchar(100) NOT NULL COMMENT '禮物名稱',
  `created_time` datetime DEFAULT NULL COMMENT '建立時間',
  `updated_time` datetime DEFAULT NULL COMMENT '更新時間',
  `gift_send_time` datetime DEFAULT NULL COMMENT '禮物贈送時間',
  `quantity` int DEFAULT NULL COMMENT '禮物數量',
  `receiver_id` int DEFAULT NULL COMMENT '接收者ID',
  `message` text COMMENT '訊息',
  `status` varchar(20) DEFAULT NULL COMMENT '狀態',
  `expiry_time` datetime DEFAULT NULL COMMENT '過期時間',
  `channel_no` varchar(50) DEFAULT NULL COMMENT '渠道',
  `flow_no` varchar(50) NOT NULL COMMENT '流水號',
  PRIMARY KEY (`id`),
  UNIQUE KEY `idx_unique_flow_no` (`flow_no`)
) ENGINE=InnoDB AUTO_INCREMENT=37 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

在進行禮物流水錶資料遷移的過程中，出現了死鎖等待超時的場景。

從日誌可以看出，是在執行禮物贈送流水錶插入的時候，阻塞等待，最後鎖等待超時了。出現這種情況，一般都是因為產生了死鎖。

有些小夥伴覺得很奇怪：

既然是死鎖，為什麼出現的卻是Lock wait timeout exceeded; try restarting transaction 鎖等待超時這個日誌呢？這是因為在Innodb儲存引擎中，當檢測到死鎖時，它會嘗試自動解決死鎖問題，通常是透過回滾(rollback)其中的一個或者多個事務來解除死鎖。

死鎖是如何產生的

既然是死鎖問題，我們先來回顧一下死鎖產生的條件。死鎖是多個程式或執行緒因競爭有限的資源而發生的一種相互等待的狀態，使得每個程式或執行緒都無法繼續執行。死鎖產生的條件包括:

• 互斥條件：至少有一個資源是獨佔的，即一次只能被一個程式或執行緒使用。
• 持有和等待條件：一個程式或執行緒可以持有一個資源，並等待其他程式或執行緒持有的資源。
• 非搶佔條件：已經分配給一個程式或執行緒的資源不能被強制性地搶佔，只能由持有資源的程式或執行緒顯式釋放。
• 迴圈等待條件：一系列程式或執行緒形成迴圈等待其他程式或執行緒持有的資源。

站在資料庫的角度，死鎖的表現如下：

死鎖排查思路

死鎖的排查思路是怎樣的呢？我一般是這麼排查的。

1. 用show engine innodb status，檢視最近一次死鎖日誌。
2. 分析死鎖日誌，找到關鍵詞TRANSACTION
3. 分析死鎖日誌，檢視正在執行的SQL
4. 看它持有什麼鎖，等待什麼鎖。

順著這個排查思路，我們先復現這個死鎖案例。在插入禮物贈送流水錶阻塞等待的過程，執行show engine innodb status命令，檢視事務和鎖的資訊。

透過日誌，可以看到這個事務正在執行的SQL是：

INSERT INTO gift_send_flow_0 (id,gift_type, gift_id, gift_name, created_time, updated_time,
                                gift_send_time, quantity, sender_id, receiver_id, message, status
                                , expiry_time, channel_no,flow_no)
    VALUES (null, '虛擬', '1', '玫瑰花', '2023-11-26 19:10:45', '2023-11-26 19:10:45', '2023-11-26 19:10:45', 1, 20000, 10025, '送給女嘉賓', null, null, '1000', 'flowNo666')

它在等待一個idx_unique_flow_no的讀共享鎖。那麼到底是什麼SQL持有了這個鎖，導致它阻塞等待呢，這時候，我們聯絡上下文程式碼，把操作這個表相關的插入或者修改、刪除的SQL都梳理一下，最後發現是一條刪除的SQL涉及到：

  <delete id="delByFLowNo">
    DELETE FROM gift_send_flow WHERE flow_no = #{flowNo}
    AND sender_id = #{sendId}
  </delete>

我們遷移的過程，涉及把原來記錄刪除掉，然後替換senderId，再執行插入。基本確定就是刪除和插入的SQL形成的死鎖。我們再來本地模擬這兩條SQL的併發執行。

sql模擬死鎖復現

先開啟一個事務A，執行刪除：

mysql> BEGIN;
Query OK, 0 rows affected (0.01 sec)

mysql> DELETE FROM gift_send_flow_0 WHERE flow_no = 'flowNo666' AND sender_id = 10000;
Query OK, 1 row affected (0.00 sec)

另開一個事務，再執行插入，發現在執行的時候，就進入了阻塞等待。

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

mysql> INSERT INTO gift_send_flow_0 (id,gift_type, gift_id, gift_name, created_time, updated_time, gift_send_time, quantity, sender_id, receiver_id, message, status , expiry_time, channel_no,flow_no) VALUES (null,'虛擬', '1', '玫瑰花', '2023-11-21 22:57:28', '2023-11-21 22:57:28', '2023-11-21 22:57:28', 1,  170000, 10025, '送給女嘉賓', NULL , NULL,'1000','flowNo666');

透過show engine innodb status檢視死鎖日誌：

發現這個跟我們程式碼跑的一模一樣。為了進一步驗證，可以透過這個命令（MySQL 8.0+）檢視SQL加鎖情況：SELECT * FROM performance_schema.data_locks\G;

可以發現，當執行刪除SQL的時候，會給唯一索引 idx_unique_flow_no加一個排他鎖。那就奇怪了，我們從剛才日誌可以發現，插入SQL等待的是一個idx_unique_flow_no的讀共享鎖。為啥會衝突呢？其實是因為 讀共享鎖跟排他鎖是衝突的：

可以得出結果，delete語句的時候，持有了唯一索引的排他行鎖，然後insert的時候，也需要獲取這個索引的讀共享鎖，因此形成死鎖。

死鎖解決方案

因為併發執行刪除和插入同一個表，因此形成死鎖。

死鎖的方案解決方案有:

• 避免迴圈等待：保證資源分配的有序性，例如，定義一個全域性的資源申請順序，並要求所有程式按照這個順序申請資源。這樣可以避免迴圈等待的情況。
• 資源有序性：按照固定的順序獲取資源，避免多個程式在不同的順序下請求資源，導致迴圈等待的情況。
• 超時機制：當一個程式無法獲取所需資源時，設定一個超時機制，超過一定時間後放棄等待的資源並釋放自己所持有的資源，避免長時間等待。

回到本文的案例，那就是遷移資料的時候控制有序性，序列執行就好。

最後

本文這個例子呢，是我模擬的一個案例出來，主要就是給大家分享死鎖的排查思路。希望對大家有幫助哈。