深度解析 MySQL InnoDB 鎖機制：從死鎖案例看索引鎖定與 Next-Key Locking

案例背景：項目實戰中的死鎖現場

專案開發中遇到數據庫死鎖，通過 SHOW INNODB STATUS 發現兩個事務互相等待對方持有的鎖，最終 MySQL 選擇回滾事務 (1)。涉及表 TSK_TASK，結構如下：

• ID：主鍵（聚簇索引）
• MON_TIME：監測時間
• STATUS_ID：任務狀態
• 索引 KEY_TSKTASK_MONTIME2：(STATUS_ID, MON_TIME) 非聚簇索引

死鎖 SQL 分析

-- 事務 1
UPDATE TSK_TASK SET STATUS_ID=1064, UPDATE_TIME=NOW() WHERE STATUS_ID=1061 AND MON_TIME ...

-- 事務 2
UPDATE TSK_TASK SET STATUS_ID=1067, UPDATE_TIME=NOW() WHERE ID IN (9921180)

兩條語句看似操作不同記錄，為何死鎖？關鍵在於 InnoDB 的鎖機制：

核心原理：鎖定的是索引，而非記錄本身

1. 行級鎖實為索引鎖：InnoDB 對索引記錄加鎖。
2. Next-Key Locking：UPDATE/DELETE 不僅鎖定 WHERE 掃描的索引記錄，還會鎖定相鄰鍵值（間隙鎖）。
3. 非聚簇索引鎖定需回表：鎖定非聚簇索引時，相關聚簇索引記錄也需加鎖。

死鎖形成過程

1. 事務 1 走 KEY_TSKTASK_MONTIME2 索引，鎖定該索引記錄，需回表鎖定主鍵索引（等待中）。
2. 事務 2 走主鍵索引，鎖定主鍵記錄，需更新 STATUS_ID 而鎖定 KEY_TSKTASK_MONTIME2 索引記錄（等待中）。
3. 雙方互相持有對方需要的鎖 → 死鎖。

解決方案：拆分 SQL 打破鎖順序

-- 步驟 1：先查符合條件的 ID（僅讀，無鎖衝突）
SELECT ID FROM TSK_TASK WHERE STATUS_ID=1061 AND MON_TIME < DATE_SUB(NOW(), INTERVAL 30 MINUTE);

-- 步驟 2：再按主鍵批量更新（走聚簇索引，避免非聚簇索引鎖定）
UPDATE TSK_TASK SET STATUS_ID=1064 WHERE ID IN (...);

將「掃描非聚簇索引+回表」拆為「先讀後寫」，消除交叉等待，死鎖徹底解決。

經驗總結

• InnoDB 行鎖實質是索引鎖，需關注 SQL 走哪個索引。
• 複合索引更新涉及多個索引鎖定，易產生死鎖。
• 大批量更新建議「先查主鍵，再按主鍵更新」。
• 善用 SHOW INNODB STATUS 分析死鎖現場。