MySQL 死鎖實戰分析：索引鎖定順序導致循環等待與 SQL 拆分解決方案

MySQL 死鎖深度剖析：次級索引與主鍵索引鎖定順序反向導致循環等待

一、鎖機制速覽

二、InnoDB 行級鎖核心原則

• 鎖的是**索引記錄**，非直接鎖資料行
• 主鍵索引（聚簇索引）：直接鎖定主鍵記錄
• 次級索引：先鎖次級索引記錄，**再鎖相關主鍵記錄**
• UPDATE/DELETE 掃描 WHERE 條件時，會鎖定**掃描到的所有索引記錄** + **鄰近鍵值** (Next-Key Lock)
• WHERE 條件順序影響鎖定範圍：首個條件應過濾性最高或為聯合索引最左列

三、實戰死鎖案例還原

表結構：

CREATE TABLE tab_test (
  id BIGINT PRIMARY KEY,
  state INT,
  time DATETIME,
  INDEX idx_1 (state, time)
);

並發執行兩條 SQL：

-- 任務 1（掃描次級索引 idx_1）
UPDATE tab_test SET state=1064, time=NOW() 
WHERE state=1061 AND time < DATE_SUB(NOW(), INTERVAL 30 MINUTE);

-- 任務 2（主鍵精確查找）
UPDATE tab_test SET state=1067, time=NOW() 
WHERE id IN (9921180);

四、死鎖形成過程

1. 任務 1 使用 idx_1 → **先鎖次級索引記錄** → 需回表 → **等待主鍵索引鎖**
2. 任務 2 使用主鍵 → **先鎖主鍵索引記錄** → 更新 state → **需鎖次級索引記錄** → 等待任務 1 持有的次級索引鎖
3. 形成循環等待：任務1(次級→主鍵) ⇄ 任務2(主鍵→次級)
4. InnoDB 偵測死鎖，**回滾權重較低的任務 1**

五、解決方案：拆分 SQL 統一鎖定順序

-- 步驟 1：先查主鍵（走次級索引但只取 id）
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 (1001, 1002, 1003...);

核心原則：所有並發更新**統一按主鍵順序**獲取鎖，打破循環等待條件。

六、其他避免死鎖最佳實踐

1. 固定多表更新順序（如按表名字母序）
2. 縮短事務持有鎖時間：避免事務中 RPC、Sleep、大計算
3. 適當降低隔離級別：READ COMMITTED 減少間隙鎖
4. 為高頻 WHERE 條件建立**覆蓋索引**，避免回表
5. 大批次更新分批提交（每批 100-500 行）
6. 啟用 innodb_print_all_deadlocks=ON 記錄完整死鎖日誌分析