MySQL 死鎖深度剖析：SELECT ... FOR UPDATE 索引鎖機制、間隙鎖與實戰排查

InnoDB 行鎖實為「索引鎖」，SELECT ... FOR UPDATE 加排他鎖（X 鎖）時，鎖定的是符合條件的索引記錄。若不理解鎖升級、間隙鎖、Next-Key Lock 機制，極易在高併發場景觸發死鎖。本文透過實戰案例，深度剖析 FOR UPDATE 鎖定規則、死鎖成因、排查工具與解決方案。

一、InnoDB 鎖定基礎：鎖索引，不鎖行

• 主鍵索引（聚集索引）： 葉子節點即資料行。鎖主鍵索引 = 鎖資料行。
• 輔助索引（二級索引）： 葉子節點存主鍵值。鎖輔助索引 = 鎖索引記錄 + 隱式鎖定對應主鍵索引記錄。
• 鎖定單位： 記錄鎖、間隙鎖、Next-Key Lock（記錄鎖 + 間隙鎖）。

二、FOR UPDATE 鎖定規則實測

測試表：id PK, getTime INT, IsSuccess INT（有索引），資料 id=400000, getTime=1, IsSuccess=0。

案例 1：SELECT 先鎖，UPDATE 後阻塞

-- Session 1
SET autocommit=0;
BEGIN;
SELECT * FROM t WHERE getTime < 1 AND IsSuccess = 0 ORDER BY id LIMIT 30 FOR UPDATE;
-- 不 COMMIT

-- Session 2
UPDATE t SET IsSuccess = 0 WHERE id = 400000;  -- 阻塞！

分析： Session 1 鎖定 IsSuccess=0 的輔助索引記錄（含 id=400000），Session 2 更新同值需等待 X 鎖釋放。

案例 2：更新不同索引值，不阻塞

-- Session 1 同前
-- Session 2
UPDATE t SET IsSuccess = 2 WHERE id = 400000;  -- 立即成功

分析： IsSuccess=2 索引記錄未被鎖，無衝突。

案例 3：UPDATE 先鎖主鍵+輔助索引，SELECT 後阻塞（反直覺）

-- Session 1
SET autocommit=0;
BEGIN;
UPDATE t SET IsSuccess = 1 WHERE id = 400000;  -- 鎖主鍵 id=400000 + 輔助索引 IsSuccess=1
-- 不 COMMIT

-- Session 2
SELECT * FROM t WHERE getTime < 1 AND IsSuccess = 0 ORDER BY id LIMIT 30 FOR UPDATE;  -- 阻塞！

分析： Session 2 掃描 IsSuccess=0 索引時，需確認 id=400000 當前是否仍為 0。因 Session 1 已鎖定該行主鍵並修改輔助索引值（未提交），Session 2 必須等待 Session 1 提交/回滾後，才能確定該行是否符合 IsSuccess=0 條件。

案例 4：範圍條件導致大範圍鎖定

-- Session 1
BEGIN;
UPDATE t SET IsSuccess = 1 WHERE id = 400000;  -- 不提交

-- Session 2
SELECT * FROM t WHERE getTime < 1 AND IsSuccess = 0 ... FOR UPDATE;  -- 阻塞
-- 但 WHERE getTime = 0 則不阻塞

結論： getTime < 1 範圍條件導致 Next-Key Lock 鎖定從開頭到 400000 的間隙；改為等值 getTime = 0 則僅鎖定精確記錄，避開衝突。

三、死鎖成因：鎖順序不一致

經典死鎖場景：

• Session A：SELECT ... FOR UPDATE 先鎖主鍵索引 → 再鎖輔助索引
• Session B：UPDATE 先鎖輔助索引 → 再鎖主鍵索引
• 雖目標行不同，但索引重疊導致循環等待 → 死鎖

四、死鎖排查與解決工具

1. 查看近期死鎖日誌

SHOW ENGINE INNODB STATUS\G

LATEST DETECTED DEADLOCK 區塊顯示：兩事務持有/等待的鎖、SQL 文本、回滾事務 ID。

2. 殺死阻塞進程

SHOW PROCESSLIST;
KILL ;  -- 或 KILL QUERY  僅終止當前語句

3. 線上 DDL/大事務監控

SELECT * FROM information_schema.INNODB_TRX\G
SELECT * FROM information_schema.INNODB_LOCKS\G
SELECT * FROM information_schema.INNODB_LOCK_WAITS\G

五、實戰解決方案

1. 統一訪問順序： 所有事務按相同順序訪問表/索引（如先主鍵後輔助索引）。
2. 縮小掃描範圍： 避免範圍條件（<, >, BETWEEN）觸發 Next-Key Lock；改用等值或 IN 列表。
3. 調整隔離級別： 可接受幻讀場景設 READ COMMITTED（需配合 binlog_format=ROW），減少間隙鎖。
4. 使用 FOR UPDATE SKIP LOCKED (MySQL 8.0+)： 跳過已鎖行，適用佇列處理、分佈式鎖。
5. 應用層重試： 捕獲 Lock wait timeout exceeded / Deadlock found 異常，指數退避重試。
6. 拆分大事務： 將長事務拆為多個短事務，減少鎖持有時間。

六、總結

InnoDB 鎖定索引而非物理行。FOR UPDATE 依條件與索引類型決定鎖定範圍，範圍條件易觸發 Next-Key Lock 鎖大區間。死鎖本質是多事務以不同順序請求相同索引鎖。掌握 SHOW ENGINE INNODB STATUS 分析、統一訪問順序、縮小掃描範圍、善用 SKIP LOCKED 与應用層重試，是解決高併發鎖衝突的核心武器庫。