MySQL InnoDB 死鎖排查實戰：缺少索引導致全表鎖升級

InnoDB 死鎖案例：UPDATE 無索引 WHERE 條件引發表級鎖升級

生產環境拋出 Deadlock found when trying to get lock; try restarting transaction，通過 SHOW ENGINE INNODB STATUS 定位到具體 SQL，根因是 **WHERE 條件列缺少索引，導致行鎖退化為表鎖**，並發下極易死鎖。

一、死鎖現象與異常堆棧

org.springframework.dao.DeadlockLoserDataAccessException: ...
Caused by: com.mysql.jdbc.exceptions.MySQLTransactionRollbackException:
Deadlock found when trying to get lock; try restarting transaction

框架層面：iBATIS + Spring + c3p0 連接池。

二、定位死鎖 SQL

MySQL 命令行執行：

SHOW ENGINE INNODB STATUS\G

輸出 LATEST DETECTED DEADLOCK 區塊，可見死鎖涉及語句：

UPDATE `table_name` SET xx=xxx WHERE xxx=xxx;  -- WHERE 列 xxx 無索引

三、根因深度解析

1. InnoDB 基於**索引實現行鎖**：通過索引定位行記錄，加鎖於索引項上。
2. WHERE 條件列**無索引** → 無法通過索引定位 → 掃描全表 (聚簇索引) → **為每一行記錄加鎖** → 實際效果等同**表級鎖 (鎖全表所有行)**。
3. 並發事務並發更新不同行，實際爭奪同一把表級鎖 → 循環等待 → 死鎖檢測器觸發，犧牲一個事務回滾。

四、解決方案

ALTER TABLE `table_name` ADD INDEX `idx_xxx` (`xxx`);  -- 為 WHERE 列添加索引

加索引後，更新可精準定位行記錄，僅加行鎖，死鎖概率大幅降低 (僅在真正行衝突時發生)。

五、預防檢查清單

• 所有 UPDATE/DELETE/SELECT ... FOR UPDATE 的 WHERE 條件列**必須有索引覆蓋**。
• 定期運行 pt-deadlock-logger 或監控 Innodb_deadlocks 狀態變量。
• 慢查詢日誌中關注 Rows_examined 遠大於 Rows_sent 的更新語句。
• 避免長事務，縮短鎖持有時間。

六、補充：死鎖 vs 鎖等待超時

1. 上一篇文章： MySQL 自定義序列號生成方案：函數 + 觸發器實現業務單號自動編碼
2. 下一篇文章： MySQL NUMERIC 與 DECIMAL 類型深度對比：精度、存儲行為與實測差異

MySQL 綜合教學

MySQL Cluster 集群入門部署教學：管理節點、資料節點與 SQL 節點架構實作

MySQL Cluster 概述 MySQL Cluster

MySQL 事件排程器 實現存儲程式定時執行

MySQL Event Scheduler 事件排程器：定時

MySQL 5.7.13 Windows x64 解壓版安裝配置圖文教程：從下載到安全初始化

下載與解壓 官網下載：MySQL Community Se

MySQL ID 關聯與索引優化實戰：從 10 秒到毫秒級的兩大關鍵點

MySQL ID 關聯與索引優化實戰：從 10 秒到毫秒級的