MySQL InnoDB 間隙鎖導致死鎖深度解析與避坑指南

現象重現

空表執行 UPDATE ... WHERE pk = 不存在值，預期無鎖，實際持有 X GAP LOCK（間隙鎖），導致並發插入死鎖。

實驗環境

CREATE TABLE `preferences` (
    `numericId` INT UNSIGNED NOT NULL,
    `receiveNotifications` TINYINT(1) DEFAULT NULL,
    PRIMARY KEY (`numericId`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

-- 表為空
SELECT COUNT(*) FROM preferences;  -- 0

關鍵操作序列

-- Session 1
BEGIN;
UPDATE preferences SET receiveNotifications=1 WHERE numericId = 1;  -- 0 rows affected
-- 持有 GAP X LOCK on (supremum pseudo-record)

-- Session 2
BEGIN;
UPDATE preferences SET receiveNotifications=1 WHERE numericId = 2;  -- 0 rows affected
-- 持有 GAP X LOCK on (supremum pseudo-record)

-- Session 1
INSERT INTO preferences VALUES (1, 1);  -- LOCK WAIT（等待 Session 2 的 GAP 鎖）

-- Session 2
INSERT INTO preferences VALUES (2, 1);  -- ERROR 1213: Deadlock found

InnoDB 狀態驗證

---TRANSACTION 4A18101, ACTIVE 12 sec
2 lock struct(s), heap size 376, 1 row lock(s)
RECORD LOCKS space id 31766 page no 3 n bits 72 index `PRIMARY` of table `test`.`preferences`
trx id 4A18101 lock_mode X  -- GAP X 鎖

原理深度解析

為什麼不存在的記錄也要鎖？

InnoDB 使用 Next-Key Lock（記錄鎖 + 間隙鎖）防止 Phantom Read（幻讀）。

• REPEATABLE READ 隔離級別（預設）要求：事務期間重複查詢結果集不變
• WHERE pk = 1 掃描主鍵索引，發現無記錄，為防止其他事務插入 pk=1，必須鎖住「不存在的位置」
• 此位置即 Gap（間隙），鎖住 (-∞, 1)、(1, 2)、(2, +∞) 等區間

死鎖形成邏輯

1. S1 鎖住 Gap(-∞, 1) 和 Gap(1, +∞)
2. S2 鎖住 Gap(-∞, 2) 和 Gap(2, +∞)
3. S1 插入 1 → 需 Gap(1, +∞) 的插入意向鎖 → 被 S2 的 Gap(1, 2) 阻塞
4. S2 插入 2 → 需 Gap(2, +∞) 的插入意向鎖 → 被 S1 的 Gap(2, +∞) 阻塞
5. 雙向等待 → 死鎖

解決方案對比

最佳實踐建議

1. 預設隔離級別改為 READ COMMITTED：

   [mysqld]
   transaction-isolation = READ-COMMITTED
   binlog_format = ROW  -- RC 必須配合 ROW 格式 Binlog

2. 業務邏輯合併為 UPSERT：

   INSERT INTO preferences (numericId, receiveNotifications) 
   VALUES (1, 1) 
   ON DUPLICATE KEY UPDATE receiveNotifications = 1;

3. 避免「先查/更新不存在記錄，再插入」模式，這是死鎖高發區
4. 監控死鎖：SHOW ENGINE INNODB STATUS 定期分析，或用 pt-deadlock-logger 記錄
5. 索引設計：確保 WHERE 條件走索引，減少掃描範圍與鎖範圍

版本演進補充

• MySQL 5.6+：innodb_deadlock_detect 可關閉死鎖檢測（極高併發時提升吞吐）
• MySQL 8.0：NOWAIT/SKIP LOCKED 子句支援，SELECT ... FOR UPDATE SKIP LOCKED 實現任務隊列
• MySQL 8.0.16+：READ COMMITTED 為預設隔離級別選項之一，官方文檔推薦 RC + ROW

1. 上一篇文章： MySQL 匯入大型 SQL 腳本報錯 2006 解決方案：max_allowed_packet 與超時調優
2. 下一篇文章： MySQL 字元集修改與查看完整指南：資料庫、資料表、欄位三層級操作

MySQL 資料庫相關知識

MySQL 單表百萬數據分頁性能優化實戰：從 37 秒到 0.2 秒

測試環境基線 指標數值 表名product 行數86

Ubuntu 14.04 環境 MySQL 安裝配置實戰筆記

環境資訊 系統：Ubuntu 14.04 版本：mysql

SQL Server 排序規則衝突完整解析：Collation 名稱結構、衝突原因與三大解決方案

跨資料庫、跨伺服器查詢時，若聯結欄位排序規則不同，會拋出「無

MySQL 存儲引擎深度解析：MyISAM、InnoDB、MEMORY 與 MERGE 特性比較

MySQL 存儲引擎概述 MySQL 提供了多種存儲引擎以適