MySQL举例说明间隙锁与下一键锁的区别？

1. 概念回顾

1. 间隙锁（Gap Lock）：

   • 锁定索引间隙，不包含索引本身。

   • 主要用于防止幻读问题，保证一致性。

   • 示例：锁住 (5, 10) 的间隙，但不锁定 5 和 10 本身。

2. 下一键锁（Next-Key Lock）：

   • 同时锁住一个索引记录和其前后间隙。

   • 本质上是索引记录锁 + 间隙锁的组合。

   • 示例：锁住 [5, 10) 区间，包括索引值 5 和间隙 (5, 10)。

2. 示例数据库表

CREATE TABLE test (
    id INT PRIMARY KEY,
    value VARCHAR(255)
);

INSERT INTO test (id, value) VALUES 
(1, 'A'),
(5, 'B'),
(10, 'C'),
(20, 'D');

3. 示例 1：间隙锁（Gap Lock）

假设在事务中执行以下查询：

BEGIN;
SELECT * FROM test WHERE id > 5 FOR UPDATE;

结果分析：

• 查询条件 id > 5 触发了范围锁，锁定了 (5, +∞) 间隙。

• 锁定的内容为：

  • 间隙 (5, 10)（不包括 10）。

  • 间隙 (10, 20)。

  • 间隙 (20, +∞)。

效果：

• 插入限制：其他事务无法在 (5, +∞) 的间隙中插入新记录。例如，INSERT INTO test (id, value) VALUES (6, 'X') 会被阻塞。

• 不锁定现有数据：间隙锁不会阻止更新 id = 10 的记录。

4. 示例 2：下一键锁（Next-Key Lock）

假设在事务中执行以下查询：

BEGIN;
SELECT * FROM test WHERE id = 5 FOR UPDATE;

结果分析：

• 查询条件 id = 5 触发了下一键锁。

• 锁定的范围为：

  • 索引值 id = 5。

  • 前后间隙 (1, 5) 和 (5, 10)。

效果：

• 插入限制：其他事务无法在 (1, 5) 和 (5, 10) 的间隙中插入新记录。

• 锁定现有数据：同时锁定 id = 5 的记录，其他事务无法更新或删除该记录。

5. 间隙锁与下一键锁的区别

6. 示例总结

• 间隙锁：用于锁定一段范围，主要防止插入操作。例如，锁住 (5, 10) 的间隙，插入 id = 7 的操作将被阻塞。

• 下一键锁：锁定范围加具体索引值。例如，锁住 [5, 10)，阻止插入 id = 6，同时限制对 id = 5 的更新或删除。

通过这两个机制，MySQL 保证了事务的隔离性，避免了幻读等问题。