MySQL 死鎖深度剖析：Next-Key Locking 導致雙事務互等與拆解 SQL 破解之道

鎖機制速覽

死鎖案例還原

表 db.tab_test：

• 主鍵 id
• 索引 idx_1 (state, time)

並發執行：

-- 任務 1 (掃描 idx_1 → 鎖主鍵)
UPDATE tab_test SET state=1064, time=NOW()
WHERE state=1061 AND time < DATE_SUB(NOW(), INTERVAL 30 MINUTE);

-- 任務 2 (按主鍵 → 鎖 idx_1)
UPDATE tab_test SET state=1067, time=NOW()
WHERE id IN (9921180);

死鎖形成鏈路

1. 任務 1 走 idx_1 範圍掃描，先鎖定 idx_1 相關記錄，再回表鎖定主鍵記錄 (Next-Key Lock 鎖住間隙)
2. 任務 2 走主鍵點查，先鎖定主鍵記錄，更新 state 需同步修改 idx_1，嘗試鎖定 idx_1 記錄
3. 任務 1 持有 idx_1 等主鍵，任務 2 持有主鍵等 idx_1 → 循環等待 → 死鎖
4. InnoDB 檢測到死鎖，回滾持有較少鎖的任務 1 (日誌顯示 WE ROLL BACK TRANSACTION (1))

破解方案：拆分「掃描」與「更新」

-- 步驟 1：只讀查詢主鍵 (無鎖或共享鎖，極快)
SELECT id FROM tab_test
WHERE state=1061 AND time < DATE_SUB(NOW(), INTERVAL 30 MINUTE);

-- 步驟 2：按主鍵批量更新 (主鍵點查，無範圍掃描，無 Next-Key Lock)
UPDATE tab_test SET state=1064, time=NOW()
WHERE id IN (上一步查出的 id 列表);

效果

• 消除範圍掃描帶來的間隙鎖
• 主鍵點查鎖定順序一致，杜絕循環等待
• 應用層可控制批次大小，避免長事務

通用預防清單

1. 固定多表更新順序 (按表名字母序)
2. 避免長事務，拆分大批量操作
3. 合理設計索引，減少回表與範圍掃描
4. 設定 innodb_lock_wait_timeout (預設 50s) 避免無限等待
5. 啟用 innodb_print_all_deadlocks=ON (5.6.2+) 記錄完整死鎖日誌