MySQL 死鎖實戰分析：S 鎖升級 X 鎖導致的經典死鎖案例

事故現場還原

業務場景：歸檔數據到歷史表並刪除主表記錄。隔離級別 RR (REPEATABLE READ)，引擎 InnoDB。

簡化復現 SQL

-- 表結構
CREATE TABLE test1 (id INT PRIMARY KEY, name VARCHAR(10));
CREATE TABLE test2 (id INT PRIMARY KEY, name VARCHAR(10));
INSERT INTO test1 VALUES (1, 'hello');

-- 事務 1 (歸檔+刪除)
BEGIN;
INSERT INTO test2 SELECT * FROM test1 WHERE id = 1;  -- 步驟 A
DELETE FROM test1 WHERE id = 1;                       -- 步驟 B

-- 事務 2 (並發歸檔)
BEGIN;
INSERT INTO test2 SELECT * FROM test1 WHERE id = 1;  -- 步驟 C

執行時序與死鎖形成

1. T1 執行 A：SELECT ... FROM test1 WHERE id=1 獲取主鍵 id=1 的 共享鎖 (S Lock)
2. T2 執行 C：嘗試獲取同一記錄的 S Lock，發現 T1 已持有，進入 鎖等待隊列
3. T1 執行 B：DELETE 需要將持有的 S Lock 升級為排他鎖 (X Lock)
4. 死鎖產生：T1 等待 T2 釋放 S Lock 申請；T2 等待 T1 釋放 S Lock 以獲取 S Lock。InnoDB 檢測到循環等待，選擇權重較小的 T2 作為犧牲者回滾。

MySQL 官方文檔描述的經典死鎖為「X Lock 等待 X Lock」，本案例為「S Lock 升級 X Lock 等待 S Lock」，本質相同。

InnoDB 鎖機制深度解析

鎖粒度對比

行鎖核心特性

• 鎖定的是 索引記錄，非直接鎖物理行
• 主鍵索引查詢 → 鎖主鍵索引
• 非主鍵索引查詢 → 先鎖二級索引，再鎖相關主鍵索引
• UPDATE/DELETE 掃描範圍 → Next-Key Lock (記錄鎖 + 間隙鎖)，防止幻讀

生產環境死鎖日誌分析

*** (1) TRANSACTION: ACTIVE 0 sec
UPDATE tab_test SET state=1064,time=now() 
WHERE state=1061 AND time < date_sub(now(), INTERVAL 30 minute)
-- 任務1：走 idx_1(state,time) 索引，先鎖二級索引，再鎖主鍵

*** (2) TRANSACTION: ACTIVE 0 sec
UPDATE tab_test SET state=1067,time=now() WHERE id in (9921180)
-- 任務2：走主鍵索引，鎖主鍵，更新 state 需鎖二級索引

*** DEADLOCK: 任務1持有idx_1等主鍵，任務2持有主鍵等idx_1

解決方案：拆分大事務，統一加鎖順序

方案一：先查主鍵，再按主鍵更新 (推薦)

-- 步驟 1：只查主鍵，輕量快速
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 (...);

方案二：調整索引設計

• 為高頻 UPDATE 的 WHERE 條件建立覆蓋索引，減少回表
• 避免長事務，盡量縮小鎖持有時間

方案三：應用層重試機制

$maxRetry = 3;
for ($i=0; $i<$maxRetry; $i++) {
    try {
        $pdo->beginTransaction();
        // 業務邏輯
        $pdo->commit();
        break;
    } catch (PDOException $e) {
        if ($e->getCode() == 'HY000' && strpos($e->getMessage(), 'Deadlock') !== false) {
            $pdo->rollBack();
            usleep(100000 * ($i+1)); // 指數退避
            continue;
        }
        throw $e;
    }
}

排查工具鏈

• SHOW ENGINE INNODB STATUS：即時查看最近死鎖詳情
• innodb_print_all_deadlocks=ON：錯誤日誌記錄所有死鎖 (5.6.2+)
• pt-deadlock-logger：Percona Toolkit 死鎖收集分析工具

總結

死鎖本質是 多事務以不同順序請求同一組資源鎖。核心預防原則：

1. 統一所有事務的加鎖順序 (建議按主鍵遞增)
2. 拆分大事務，縮小鎖範圍與持有時間
3. 合理設計索引，減少不必要的掃描鎖定
4. 應用層實現死鎖重試邏輯作為最後防線