【解决幻读的方法】MySQL的间隙锁

锁住一行之间的“间隙”（Gap Lock）是 MySQL InnoDB 存储引擎中的一种锁定机制，用于防止幻读（Phantom Reads）。它的作用是在执行范围查询时，不仅锁住符合条件的具体行，还会锁住这些行之间的“间隙”，从而阻止其他事务在这些间隙中插入或删除数据。

间隙锁（Gap Lock）的含义

“间隙”指的是数据库索引中相邻行记录之间的范围。例如，假设表中有两行记录，ID 分别为 10 和 20，那么这两行记录之间的间隙就是 (10, 20)。使用“间隙锁”时，InnoDB 会锁住这个范围，以防止其他事务在这个范围内插入新的记录。

间隙锁的用途

当事务以 REPEATABLE READ 隔离级别执行范围查询（如 SELECT ... FOR UPDATE）时，InnoDB 使用间隙锁来防止其他事务在锁定范围内插入新记录，从而避免幻读的发生。

示例说明

假设有一个表 users，其中 id 字段是主键，当前表中的记录如下：

id	name
10	Alice
20	Bob
30	Carol

假设我们执行以下 SQL 语句来锁定范围内的行：

SELECT * FROM users WHERE id BETWEEN 10 AND 20 FOR UPDATE;

在执行此查询后：

1. 记录锁（Record Lock）：InnoDB 会锁住符合查询条件的现有行（ID 为 10 和 20 的行）。
2. 间隙锁（Gap Lock）：InnoDB 还会锁住以下两个“间隙”：
   • (negative infinity, 10)：表示小于 ID 为 10 的所有间隙。
   • (10, 20)：ID 10 和 20 之间的间隙。
   • (20, positive infinity)：表示大于 ID 为 20 的所有间隙。

间隙锁的作用

• 防止插入新记录：例如，在上述查询执行过程中，如果另一个事务试图插入一条新记录，ID 为 15（在 (10, 20) 之间的间隙中），将会被阻塞直到第一个事务提交或回滚。
• 防止幻读：如果允许其他事务在当前事务的查询范围内插入新记录，那么当前事务在第二次查询时可能会读到新的行，这就是幻读现象。间隙锁防止了这种情况的发生。

举例说明

1. 幻读现象

假设有两个事务（事务A和事务B）并发运行，当前表 users 中有以下记录：

id	name
10	Alice
20	Bob
30	Carol

• 事务A 开始，执行以下查询：

START TRANSACTION;
SELECT * FROM users WHERE id BETWEEN 10 AND 20 FOR UPDATE;

此时，事务A锁住了 id 为 10 和 20 的行，以及 id 在 10 和 20 之间的间隙 (10, 20)。

• 事务B 尝试在事务A进行期间插入一条新记录：

INSERT INTO users (id, name) VALUES (15, 'Dave');

由于间隙锁 (10, 20) 的存在，事务B 会被阻塞，无法插入 ID 为 15 的新记录。事务B 必须等待事务A提交或回滚，才能继续执行插入操作。

2. 无法插入新记录

这就避免了事务A在再次执行相同查询时读取到一条新的记录（ID 为 15），从而防止了幻读现象的发生。

总结

“锁住一行之间的间隙”意味着通过 间隙锁（Gap Lock） 机制锁定数据库索引中相邻行记录之间的范围，以防止其他事务在此范围内插入或删除数据，从而防止幻读。它确保了在同一个事务内多次执行相同的查询时，查询结果的一致性。