MySQL 死鎖深度剖析：InnoDB 行鎖機制、死鎖日誌解讀、SQL 拆分解決方案實戰

MySQL 死鎖深度剖析：InnoDB 行鎖機制、死鎖日誌解讀、SQL 拆分解決方案實戰

死鎖是高併發 InnoDB 系統的常見痛點。本文從鎖機制原理切入，實戰解讀 SHOW ENGINE INNODB STATUS 死鎖日誌，提出「查詢主鍵 → 按主鍵更新」拆分策略徹底規避死鎖。

一、常用引擎鎖機制對比

解決死鎖核心聚焦 InnoDB 行鎖。

二、InnoDB 行鎖核心原理

1. 鎖索引，不鎖記錄： 行鎖實際鎖定 B+ 樹索引項；
2. 主鍵索引 vs 輔助索引： 操作主鍵 → 鎖主鍵索引；操作輔助索引 → 先鎖輔助索引，再鎖相關主鍵索引；
3. Next-Key Locking： UPDATE/DELETE 掃描範圍時，鎖定掃描到的所有索引記錄 + 相鄰間隙，防止幻讀。

三、實戰死鎖日誌解讀

表結構：id PK, state, time, idx_1(state,time)

事務 1：UPDATE tab_test SET state=1064, time=NOW() WHERE state=1061 AND time < DATE_SUB(NOW(), INTERVAL 30 MINUTE)
→ 走 idx_1，先鎖 idx_1 相關項，再鎖主鍵索引

事務 2：UPDATE tab_test SET state=1067, time=NOW() WHERE id IN (9921180)
→ 走主鍵，先鎖主鍵索引，更新 state 需鎖 idx_1 相關項

死鎖形成： 事務 1 持有 idx_1 等主鍵；事務 2 持有主鍵 等 idx_1 → 循環等待 → InnoDB 檢測並回滾事務 1。

四、解決方案：SQL 拆分消除索引順序衝突

-- 步驟 1：先查符合條件的主鍵（僅讀，無鎖衝突）
SELECT id FROM tab_test
WHERE state=1061 AND time < DATE_SUB(NOW(), INTERVAL 30 MINUTE);

-- 步驟 2：按主鍵批量更新（僅走主鍵索引，無輔助索引鎖）
UPDATE tab_test SET state=1064, time=NOW() WHERE id IN (...);

原理： 將「範圍掃描輔助索引 + 回表鎖主鍵」拆分為「純讀輔助索引」+「純寫主鍵索引」，打破鎖順序循環依賴。

五、通用防死鎖最佳實踐

1. 固定多表更新順序（如按表名字母序）；
2. 縮短事務持有時間，避免事務中呼叫外部 API；
3. 合理設計索引，減少範圍掃描鎖定行數；
4. 必要時鎖升級：LOCK TABLES ... WRITE（慎用，降級為表鎖）；
5. 應用層重試機制：捕獲 ER_LOCK_DEADLOCK (1213) 指數退避重試。