MySQL InnoDB 死鎖深度剖析：SELECT FOR UPDATE 與 UPDATE 交互導致的鎖衝突

前言

在高併發業務中，多台機器需從同一張表獲取數據並避免重複處理。採用 SELECT ... FOR UPDATE 加排他鎖（X 鎖）是常見方案，但若與其他 UPDATE 語句交互不當，極易引發死鎖。本文通過實戰案例深入剖析 InnoDB 鎖機制，揭示死鎖根因並給出解決思路。

業務場景與鎖策略選型

• 存儲引擎：InnoDB（支援行鎖）
• 需求：多機器並發獲取數據段，需鎖定查詢範圍防止重複
• 方案：SELECT ... FOR UPDATE LIMIT 0,30 鎖定前 30 條，其他機器等待鎖釋放
• 對比：Oracle 支援 FOR UPDATE SKIP LOCKED 可跳過已鎖行，MySQL 5.7+ 才引入此特性

死鎖案例複現與分析

案例一：範圍查詢鎖定索引區間

表結構：id 為主鍵，IsSuccess、GetTime 為非主鍵索引。初始數據 id=400000，IsSuccess=0、GetTime=0。

Session 1：

SET autocommit=0;
BEGIN;
SELECT * FROM table1 
WHERE getTime < 1 AND IsSuccess=0 
ORDER BY id ASC LIMIT 0,30 FOR UPDATE;
-- 不提交，持有鎖

Session 2：

UPDATE table1 SET IsSuccess=0 WHERE id=400000;  -- 阻塞等待

原因： Session 1 的範圍查詢鎖定了 IsSuccess=0 的二級索引區間，Session 2 更新同一索引值導致衝突。若改為 IsSuccess=2 則不阻塞，因索引值不同。

案例二：更新操作阻塞後續範圍查詢

先將 id=400000 的 GetTime 改為 1，IsSuccess=0。

Session 1：

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

Session 2：

SELECT * FROM table1 
WHERE getTime < 1 AND IsSuccess=0 
ORDER BY id ASC LIMIT 0,30 FOR UPDATE;  -- 阻塞！

深度解析：

1. Session 1 先鎖主鍵索引（id=400000），再鎖二級索引 IsSuccess（值從 0→1）
2. Session 2 的 WHERE 條件含 IsSuccess=0，需讀取該二級索引
3. 因 Session 1 未提交，id=400000 的 IsSuccess 值不確定（可能回滾），Session 2 必須等待
4. 雖然 GetTime=1 已不滿足 < 1，但 InnoDB 按索引加鎖，仍會掃描並嘗試鎖定該記錄

案例三：死鎖形成的完整鏈路

真實業務中發現：SELECT ... FOR UPDATE 與另一條 UPDATE 導致死鎖。

• SQL A：SELECT ... FOR UPDATE 先鎖主鍵索引 → 再鎖二級索引
• SQL B：UPDATE 先鎖二級索引 → 再鎖主鍵索引
• 雖目標行不同，但鎖獲取順序相反，形成循環等待 → 死鎖

InnoDB 鎖行為總結（經驗歸納）

1. WHERE 條件逐列檢查鎖：任何條件列若有鎖，需等待該事務提交/回滾後確定值才能繼續。
2. ORDER BY 影響掃描順序：前置記錄被鎖時，即使從後面開始查，也可能被阻塞。
3. LIMIT 不能跳過鎖等待：LIMIT 20,30 若第 1 行被鎖，同樣會等待。
4. 範圍查詢鎖定整個掃描區間：GetTime < 1 會鎖定從第一行開始的所有符合條件範圍。

解決方案與最佳實踐

• 調整查詢條件：將 GetTime < 1 改為 GetTime = 0（等值查詢），移除 IsSuccess 條件，避免範圍鎖擴大。
• 統一鎖獲取順序：所有事務按相同順序訪問索引（如先主鍵後二級），避免交叉等待。
• 縮短事務持有時間：盡快提交/回滾，減少鎖佔用窗口。
• 升級 MySQL 5.7+ 使用 SKIP LOCKED：SELECT ... FOR UPDATE SKIP LOCKED 可直接跳過已鎖行。

附錄：KILL 命令緊急處理死鎖

-- 查看正在執行的線程
SHOW PROCESSLIST;

-- 終止指定線程（殺死查詢，保留連線）
KILL QUERY thread_id;

-- 或終止整個連線
KILL CONNECTION thread_id;

需具備 PROCESS 權限查看所有線程，SUPER 權限可殺死任意線程。

結語

InnoDB 的行鎖本質是鎖索引，理解索cesi加鎖順序、範圍查詢鎖定範圍、條件列檢查機制，是避免死鎖的關鍵。實際開發中應優化 SQL 條件、統一訪問順序、控制事務粒度，並善用 SHOW ENGINE INNODB STATUS 分析死鎖日誌。