InnoDB 死鎖深度剖析：Next-Key Locking、索引鎖升級與 SQL 重構解決方案

死鎖現場還原

*** (1) TRANSACTION:
UPDATE TSK_TASK SET STATUS_ID=1064, UPDATE_TIME=NOW() 
WHERE STATUS_ID=1061 AND MON_TIME < DATE_SUB(NOW(), INTERVAL 30 MINUTE)
-- 等待：主鍵索引 X 鎖 (heap no 92)

*** (2) TRANSACTION:
UPDATE TSK_TASK SET STATUS_ID=1067, UPDATE_TIME=NOW() 
WHERE ID IN (9921180)
-- 持有：主鍵索引 X 鎖 (heap no 92)
-- 等待：KEY_TSKTASK_MONTIME2 (STATUS_ID, MON_TIME) 索引 X 鎖

核心機制：Next-Key Locking

• InnoDB 行鎖實際鎖定的是索引記錄，非主鍵索引鎖定後需回表鎖主鍵
• UPDATE ... WHERE 索引列 = 值：鎖定匹配記錄 + 間隙鎖 防止幻讀
• UPDATE ... WHERE 索引列 > 值：鎖定範圍內所有記錄 + 間隙
• 非唯一索引鎖定時，同時鎖定對應主鍵記錄

死鎖形成邏輯

1. 事務 A：走 KEY_TSKTASK_MONTIME2 索引 → 鎖定二級索引記錄 → 需回表鎖主鍵
2. 事務 B：走主鍵 ID → 鎖定主鍵記錄 → 需更新 STATUS_ID 鎖二級索引
3. 雙方持有對方需要的鎖 → 死鎖 → InnoDB 檢測回滾較小事務

解決方案：SQL 重構拆分

-- 原：單條複雜 UPDATE 觸發雙索引鎖
-- 新：兩步驟，明確走主鍵

-- 步驟 1：只讀查詢，走二級索引，無鎖（或 LOCK IN SHARE MODE）
SELECT ID FROM TSK_TASK 
WHERE STATUS_ID = 1061 AND MON_TIME < DATE_SUB(NOW(), INTERVAL 30 MINUTE);

-- 步驟 2：應用層收集 ID 列表，走主鍵批次更新
UPDATE TSK_TASK SET STATUS_ID = 1064, UPDATE_TIME = NOW() 
WHERE ID IN (id1, id2, id3, ...);

其他預防策略

死鎖診斷工具

-- 即時查看
SHOW ENGINE INNODB STATUS\G  -- LATEST DETECTED DEADLOCK 區塊

-- 永久記錄 (5.6+)
SET GLOBAL innodb_print_all_deadlocks = ON;
-- error log 將記錄完整死鎖資訊

-- 監控指標
SHOW GLOBAL STATUS LIKE 'Innodb_deadlocks';

實戰檢查清單

1. 所有 UPDATE/DELETE 是否有合適索引避免全表掃描鎖
2. 複合索引列順序是否符合 WHERE 條件
3. 是否存在無索引外鍵導致全表鎖
4. 長事務是否可拆分
5. 應用層重試邏輯是否處理 ER_LOCK_DEADLOCK (1213)

1. 上一篇文章： MySQL 客戶端連線完整指南：mysql 命令參數、連線協議、選項檔與批次模式
2. 下一篇文章： MySQL-Proxy 讀寫分離實戰：Lua 腳本路由、連線池管理與高可用架構

MySQL 資料庫相關知識

MySQL 樹結構核心原理與操作全解析：嵌套集模型實戰

從一個真實踩坑故事說起 早期使用 CakePHP ACL 插

Windows 下 MySQL 社區版安裝配置與密碼管理圖文教程

下載與版本選擇 訪問 MySQL 官網下載頁 選擇 MyS

MySQL 主從複制、讀寫分離、備份恢復企業級實戰全紀錄

一、主從複制部署 (GTID 模式推薦) 1. 規劃與前置

JDBC 連接 MySQL 資料庫完整實戰教學

前言 近期在學習資料庫開發的實作範例，這裡分享如何使用 JD