JDBC 並發事務執行相同 Update 導致死鎖的原因與解決方案

問題現場重現

多執行緒並發對同一資料表執行兩條 UPDATE：

-- 事務內容（每個執行緒相同）
UPDATE xxxx SET column1 = '11' WHERE ID = '11';
UPDATE xxxx SET column2 = '22' WHERE ID = '22';

資料庫拋出死鎖異常：Transaction (Process ID 211) was deadlocked on lock resources with another process and has been chosen as the deadlock victim.

死鎖形成機制分析

行級鎖與索引順序

1. InnoDB（MySQL 預設引擎）在 WHERE ID = '11' 時，若 ID 為主鍵或唯一索引，會對該聚簇索引記錄加 排他鎖 (X Lock)。
2. 執行緒 A 執行第一條 UPDATE ... WHERE ID = '11'，鎖定記錄 ID=11。
3. 執行緒 B 同時執行第一條 UPDATE ... WHERE ID = '11'，等待鎖釋放（阻塞）。
4. 若執行緒 A 繼續執行第二條 UPDATE ... WHERE ID = '22'，鎖定記錄 ID=22。
5. 執行緒 B 若在不同上下文先鎖住 ID=22 再嘗試鎖 ID=11，即形成循環等待 → 死鎖。

關鍵點：不同事務鎖定資源的順序不一致

即使 SQL 文本相同，若執行計畫或並發調度導致實際鎖定順序不同（例如：某執行緒先更新 ID=22，另一個先更新 ID=11），即可能死鎖。

解決方案

1. 統一資源存取順序（最根本）

在應用層確保所有事務按相同順序更新記錄，例如永遠先 ID=11 再 ID=22：

// 應用程式邏輯強制排序
List ids = Arrays.asList("11", "22");
ids.sort(String::compareTo); // 確保固定順序
for (String id : ids) {
    executeUpdate("UPDATE xxxx SET column1 = ? WHERE ID = ?", val, id);
}

2. 使用 SELECT ... FOR UPDATE 鎖定整個範圍

// 事務開始時一次鎖定所有相關行，按主鍵順序
SELECT * FROM xxxx WHERE ID IN ('11','22') FOR UPDATE;
-- 之後再執行 UPDATE，不再產生新的鎖等待

注意：IN 子句鎖定順序依賴索引掃描順序，建議配合 ORDER BY ID 明確指定。

3. 調整隔離等級（權衡方案）

• READ COMMITTED：減少間隙鎖，降低死鎖機率，但可能出現不可重複讀。
• 避免 SERIALIZABLE：會加大量間隙鎖，極易死鎖。

4. 應用層重試機制（必要補充）

int maxRetry = 3;
for (int i = 0; i < maxRetry; i++) {
    try {
        runTransaction();
        break; // 成功跳出
    } catch (SQLException e) {
        if (isDeadlock(e) && i < maxRetry - 1) {
            Thread.sleep(randomBackoff()); // 指數退避 + 抖動
            continue;
        }
        throw e;
    }
}

排查工具與最佳實務

• MySQL： SHOW ENGINE INNODB STATUS 查看最新死鎖日誌；開啟 innodb_print_all_deadlocks=ON 記錄完整歷史。
• SQL Server： 擴充事件 xml_deadlock_report 或 Trace Flag 1222。
• 索引優化： 確保 WHERE 條件走索引，避免全表掃描升級為表鎖。
• 縮短事務： 只在事務中執行必要的 DML，業務邏輯、外部呼叫移至事務外。

總結

並發執行相同 UPDATE 導致死鎖的核心原因是多事務以不同順序請求相同資源的排他鎖。根治辦法是應用層強制統一存取順序，輔以事務重試機制與適當隔離等級，可將死鎖機率降至最低。