MySQL InnoDB 鎖機制深度解析（二）：行鎖、間隙鎖、Next-Key Lock 與幻讀防護

MySQL InnoDB 鎖機制深度解析（二）：三種行級鎖模式與幻讀防護實戰

InnoDB 在 REPEATABLE READ 隔離級別下，通過 Next-Key Lock（行鎖 + 間隙鎖） 實現無幻讀。本文通過實測案例，詳細演示 Record Lock、Gap Lock、Next-Key Lock 的加鎖範圍、阻塞行為，並給出禁用間隙鎖的配置方案。

一、三種鎖模式定義

二、實測環境準備

CREATE TABLE tb1 (
    id INT(11) NOT NULL,
    id2 INT(11) NOT NULL,
    PRIMARY KEY (id),
    KEY idx (id2)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO tb1 VALUES (1,3), (2,6), (3,9);
-- id2 值 3,6,9 將間隙分為四段：(-∞,3) (3,6) (6,9) (9,+∞)

三、會話 S1：對 id2=6 加共享鎖 (LOCK IN SHARE MODE)

SESSION 1:
START TRANSACTION;
SELECT * FROM tb1 WHERE id2 = 6 LOCK IN SHARE MODE;
+----+-----+
| id | id2 |
+----+-----+
| 2  | 6   |
+----+-----+
-- 事務未提交，保持會話開啟

加鎖分析：
- 索引 idx (id2) 上定位到 id2=6 記錄 → Record Lock。
- 同時在該記錄左右兩側間隙 (3,6) 和 (6,9) 加 Gap Lock。
- 組合形成 Next-Key Lock：(3, 6] 和 (6, 9]。

四、會話 S2：嘗試插入間隙內數據（被阻塞）

SESSION 2:
START TRANSACTION;
INSERT INTO tb1 VALUES (4, 5);  -- 落在 (3,6) 間隙
-- ERROR 1205 (HY000): Lock wait timeout exceeded

INSERT INTO tb1 VALUES (4, 7);  -- 落在 (6,9) 間隙
-- ERROR 1205 (HY000): Lock wait timeout exceeded

間隙鎖不分共享/排他，唯一目的是禁止其他事務插入，從而防止幻讀。

五、會話 S2：插入間隙外數據（成功）

INSERT INTO tb1 VALUES (4, 2);  -- 落在 (-∞,3) → 成功
INSERT INTO tb1 VALUES (5, 10); -- 落在 (9,+∞) → 成功

六、關鍵規則：唯一索引/主鍵查詢不加間隙鎖

若 WHERE id = 2（主鍵）或 WHERE uk_col = 'val'（唯一索引），InnoDB 確定最多一行，僅加 Record Lock，不加 Gap Lock，不形成 Next-Key Lock。
驗證：對主鍵加鎖後，其他事務仍可插入間隙數據。

七、禁用間隙鎖的兩種方式

1. 降低隔離級別：SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
   READ COMMITTED 下無間隙鎖，僅 Record Lock，但會出現不可重複讀/幻讀。
2. 開啟參數：innodb_locks_unsafe_for_binlog = 1（動態生效，無需重啟）。
   警告：會導致 Binlog 記錄不一致，主從復制可能數據不一致，生產環境極不推薦。

八、死鎖常見場景與排查

-- 會話 A
START TRANSACTION;
SELECT * FROM tb1 WHERE id2 = 6 LOCK IN SHARE MODE;  -- 鎖 (3,6] (6,9]

-- 會話 B
START TRANSACTION;
SELECT * FROM tb1 WHERE id2 = 3 LOCK IN SHARE MODE;  -- 鎖 (-∞,3] (3,6]
-- 會話 B 等待會話 A 釋放 (3,6] 間隙鎖

-- 會話 A 再查 id2=3
SELECT * FROM tb1 WHERE id2 = 3 LOCK IN SHARE MODE;  -- 等待會話 B 釋放 (3,6]
-- 死鎖形成，InnoDB 自動檢測回滾一方

排查：SHOW ENGINE INNODB STATUS\G 查看 LATEST DETECTED DEADLOCK 章節。

九、最佳實踐建議

• 業務允許時，優先使用主鍵/唯一索引查詢加鎖，避免間隙鎖爭用。
• 大事務拆小，縮短鎖持有時間。
• 索引設計覆蓋高頻查詢路徑，減少全表掃描導致的大量 Next-Key Lock。
• 監控 Innodb_row_lock_waits、Innodb_row_lock_time_avg 指標，納入告警。

深度理解 Next-Key Lock 機制，是解決 InnoDB 高併發下鎖衝突、死鎖、幻讀問題的核心鑰匙。

1. 上一篇文章： CentOS 6.5 YUM 安裝 MySQL 入門：服務管理、存儲引擎、賬號授權、表操作全流程
2. 下一篇文章： MySQL InnoDB 非阻塞讀實現原理：MVCC 多版本並發控制深度解析

MySQL 綜合教學

macOS 10.12 安裝 MySQL 5.7.18 避坑指南（臨時密碼、root 啟用、alias 永久生效）

安裝與臨時密碼 官網下載 mysql-5.7.18-m

資料庫設計陷阱：訂單地址引用用戶地址 ID 導致歷史訂單地址被竄改

資料庫設計陷阱：訂單地址引用用戶地址 ID 導致歷史訂單地址

MySQL Workbench 圖形化備份還原完整教學：視覺化操作替代命令行

拒絕黑框框：Workbench 讓備份還原像操作檔案一樣簡單

MySQL/phpMyAdmin Root 密碼忘記重置完整指南 (Linux 系統)

前言 遺忘 MySQL root 密碼是運維常態。本文提供