MySQL 死鎖原理深度解析與 8 大應對策略

當多事務以不同順序請求相同資源鎖，形成循環等待即發生死鎖。InnoDB 會自動檢測並回滾持有較少排他鎖的事務。本文以 StockPrice 表雙事務交叉更新為例，剖析成因並給出預防與處理方案。

死鎖形成示例

-- 事務 1
START TRANSACTION;
UPDATE StockPrice SET close = 45.50 WHERE stock_id = 4 AND date = '2002-05-01';
UPDATE StockPrice SET close = 19.80 WHERE stock_id = 3 AND date = '2002-05-02';
COMMIT;

-- 事務 2
START TRANSACTION;
UPDATE StockPrice SET high = 20.12 WHERE stock_id = 3 AND date = '2002-05-02';
UPDATE StockPrice SET close = 47.20 WHERE stock_id = 4 AND date = '2002-05-01';
COMMIT;

若事務 1 鎖住 (4, '2002-05-01')，事務 2 鎖住 (3, '2002-05-02')，雙方嘗試獲取對方持有的鎖 → 死鎖。

InnoDB 處理機制

• 即時偵測循環依賴，回滾「持有排他行鎖較少」的事務（簡單啟發式）。
• 死鎖是事務系統常態，**應用層必須具備重試邏輯**。

8 大降低死鎖策略

1. 檢視 SHOW ENGINE INNODB STATUS 分析最新死鎖日誌，針對性調優索引與 SQL 順序。
2. 應用層捕獲死錯誤碼 (1213) 重試事務，死鎖不代表資料錯誤，重試通常即可成功。
3. 保持事務短小、頻繁提交，減少鎖持有時間與衝突機率。
4. 降低隔離級別：若使用鎖定讀 (SELECT ... FOR UPDATE)，嘗試 READ COMMITTED 減少間隙鎖。
5. 固定存取順序：所有事務以相同順序訪問表與行（如先按主鍵排序），消除循環等待根源。
6. 添加精準索引：EXPLAIN 分析查詢計劃，減少掃描行數與鎖定範圍。
7. 避免不必要的鎖定讀：可接受舊快照讀時移除 FOR UPDATE/LOCK IN SHARE MODE，搭配 READ COMMITTED。
8. 退而求其次用表鎖序列化：SET AUTOCOMMIT=0; LOCK TABLES t1 WRITE, t2 READ; ... COMMIT; UNLOCK TABLES; 犧牲並發換無死鎖。

進階技巧：信號量表

建立單行 semaphore 表，事務進入前先 UPDATE semaphore SET val = val + 1，以行鎖實現邏輯序列化，InnoDB 死鎖檢測同樣有效。

避坑提醒：AUTOCOMMIT=1 時 LOCK TABLES 不會加 InnoDB 表鎖，務必關閉自動提交。