InnoDB Next-Key Lock 深度解析：原理、降級規則與實戰死鎖案例

核心定義

Next-Key Lock = Record Lock（記錄鎖） + Gap Lock（間隙鎖），鎖定一個**左開右閉**區間 (前一記錄值, 當前記錄值]。

• Record Lock：鎖定單個索引記錄。
• Gap Lock：鎖定兩個索引記錄之間的**開區間**，防止插入新記錄。
• Next-Key Lock：兩者結合，既鎖住記錄本身，又鎖住其前方間隙。

關鍵規則速查表

經典實驗複現（單列主鍵 a，數據 11,12,13,14）

案例 1：DELETE 存在的主鍵

Session 1: BEGIN; DELETE FROM t WHERE a=11;  -- 加鎖 (11,11] 即 Record Lock
Session 2: BEGIN; INSERT 10, 15, 9, 16;      -- 全部成功，無阻塞

✅ 預期內：唯一索引等值命中，降級 Record Lock，僅鎖該行。

案例 2：DELETE 不存在且超出最大值

Session 1: BEGIN; DELETE FROM t WHERE a=22;  -- 掃描到 supremum pseudo-record，加鎖 (14, +∞)
Session 2: INSERT 201, 20, 19, 18, 16;       -- 全部阻塞 (Lock wait timeout)
Session 2: INSERT 9;                          -- 成功，不在鎖區間

🔑 關鍵：InnoDB 為防止幻讀，將「不存在的值」視為落在「最後一條記錄後的間隙」，鎖定 (max_value, +∞)。

案例 3：DELETE 不存在但落在中間

數據: 7,9,10,12,13,14,15,22,23,24,25
Session 1: BEGIN; DELETE FROM t WHERE a=21;  -- 掃描到 22，加鎖 (15,22]
Session 2: INSERT 20, 21, 16;                -- 阻塞
Session 2: INSERT 26, 6;                     -- 成功

🔑 鎖區間精確縮小到 (前一記錄, 目標值]。

為什麼是左開右閉？

官方定義：「Next-Key Lock = Record Lock on index record + Gap Lock on gap before the index record」。掃描方向由優化器決定，為保證範圍查詢完整性，InnoDB 採用「掃描到的記錄 + 其前方間隙」策略，自然形成左開右閉。

優化器陷阱：全表掃描導致全表鎖

-- Session 1
DELETE FROM t WHERE id IN (100, 200, 300);  -- 按索引順序加鎖 100→200→300

-- Session 2
DELETE FROM t WHERE id IN (300, 200, 100);  -- 按 300→200→100 加鎖
-- 死鎖：1 持有 100 等 200，2 持有 300 等 200

修正：應用層**統一按主鍵升序**處理批量操作。

案例：覆蓋索引未命中導致回表死鎖

-- 查看當前事務鎖等待
SELECT * FROM performance_schema.data_locks;
SELECT * FROM sys.innodb_lock_waits;

-- 死鎖日志
SHOW ENGINE INNODB STATUS\G  -- LATEST DETECTED DEADLOCK 區塊

-- 開啟鎖監控 (5.7+)
SET GLOBAL innodb_print_all_deadlocks=ON;
SET GLOBAL innodb_status_output=ON;
SET GLOBAL innodb_status_output_locks=ON;

總結

1. Next-Key Lock 是 RR 級別防幻讀的核心機制，本質是區間鎖。
2. 唯一索引等值查詢會降級為 Record Lock，併發度最高。
3. **索引設計決定鎖粒度**：無索引=全表鎖，非唯一索引=範圍鎖，唯一索引=行鎖。
4. 批量操作**統一順序**，避免交叉死鎖。
5. 善用 performance_schema 與 sys schema 可視化鎖等待，從「感覺慢」進化到「精準定位」。