資料庫 UPDATE 死鎖根因分析與排序解決方案實戰

資料庫 UPDATE 死鎖根因分析與排序解決方案實戰

批量更新場景下，因不同事務以不同順序鎖定行記錄，極易觸發死鎖。本文通過實戰案例深度剖析死鎖產生機制，提供「統一排序」這一通用解決方案。

一、死鎖現場還原

-- 業務 SQL：批量更新營銷活動頁面瀏覽量
UPDATE cross_marketing 
SET gmtModified = NOW(), pageview = pageview + #{extpageview} 
WHERE marketingId = #{marketingId};

並發場景：

• 事務 A（線程 1）：按順序處理 marketingId = 1, 3, 5, 12
• 事務 B（線程 2）：按順序處理 marketingId = 1, 2, 5, 3

二、死鎖形成過程圖解

時間軸 →
事務 A: 鎖行(1) → 鎖行(3) → 等待鎖行(5) [被 B 持有]
事務 B: 鎖行(1) → 鎖行(2) → 鎖行(5) → 等待鎖行(3) [被 A 持有]
                     ↑                                    ↑
               持有鎖(3) 等待(5)                  持有鎖(5) 等待(3)
                            循環等待 = 死鎖

MySQL InnoDB 檢測到死鎖後，會選擇代價較小的事務回滾（通常是修改行數較少者），返回 ERROR 1213 (HY000): Deadlock found when trying to get lock; try restarting transaction。

三、根因分析

1. 無序訪問： 多事務以不同順序訪問同一組資源
2. 持鎖並等待： 事務持有部分鎖，同時請求新鎖
3. 循環等待： 形成資源請求環路

滿足 Coffman 條件中的「互斥、請求與保持、不剝佔、循環等待」四條件即發生死鎖。

四、通用解決方案：統一訪問順序

核心原則： 所有並發事務以**相同順序**訪問同一組資源（按主鍵/唯一鍵升序）。

-- 修正前（應用層隨機順序）
List ids = Arrays.asList(1, 3, 5, 12);  // 線程 A
List ids = Arrays.asList(1, 2, 5, 3);   // 線程 B

-- 修正後（統一排序）
List ids = Arrays.asList(1, 3, 5, 12);  // 線程 A
List ids = Arrays.asList(1, 2, 3, 5);   // 線程 B 排序後

-- Java 實現
ids.sort(Long::compareTo);  // 或 Collections.sort(ids)
for (Long id : ids) {
    updateMarketing(id, extPageview);
}

五、進階優化策略

六、死鎖排查工具箱

-- 1. 查看最近死鎖詳情
SHOW ENGINE INNODB STATUS\G
-- 關注 LATEST DETECTED DEADLOCK 區塊

-- 2. 開啟死鎖記錄（MySQL 5.7+）
innodb_print_all_deadlocks=ON
-- 錯誤日誌將記錄完整死鎖資訊

-- 3. 監控鎖等待
SELECT * FROM performance_schema.data_locks;
SELECT * FROM performance_schema.data_lock_waits;

-- 4. 實時查看事務
SELECT * FROM information_schema.INNODB_TRX\G
SELECT * FROM information_schema.INNODB_LOCKS\G
SELECT * FROM information_schema.INNODB_LOCK_WAITS\G

七、最佳實踐檢查清單

• [ ] 所有批量 DML 操作的 ID 列表在執行前**強制排序**
• [ ] 大批量操作**分批提交**，單批 ≤ 1000 行
• [ ] 避免在事務中穿插 RPC/HTTP 呼叫（延長持鎖時間）
• [ ] 合理設置 innodb_lock_wait_timeout（建議 5-10s）
• [ ] 關鍵業務啟用死鎖日誌 innodb_print_all_deadlocks=ON
• [ ] 監控 Lock_deadlocks 計數器，設定告警