MySQL 通过 Next-Key Locking 技术避免幻读问题

在 MySQL 的 InnoDB 引擎中，Next-Key Locking 技术结合了 行锁（Record Lock） 和 间隙锁（Gap Lock），通过锁定记录和间隙，解决了 Repeatable Read 隔离级别下的幻读问题。

什么是 Next-Key Locking？

Next-Key Locking 是一种锁定区间的机制，包含以下两部分：

• 行锁（Record Lock）： 锁定精确的数据行，防止其他事务修改该行。
• 间隙锁（Gap Lock）： 锁定数据行之间的“间隙”，防止其他事务在这些空隙中插入新数据。

通过锁定查询范围内的记录及其前后间隙，Next-Key Locking 有效避免了幻读现象。

Next-Key Locking 的原理

在 Repeatable Read 隔离级别下，执行范围查询时，InnoDB 会通过 Next-Key Locking 锁定范围内的记录及其相邻间隙。
例如，执行以下 SQL 查询：

SELECT * FROM users WHERE age BETWEEN 20 AND 30 FOR UPDATE;

假设当前数据如下：

id	age
1	18
2	25
3	28
4	35

• 锁定 age=25 和 age=28 的行（Record Lock）。
• 锁定 age > 18 到 age < 35 的所有间隙（Gap Lock），阻止插入 age=19 到 age=34 范围内的新数据。

这样可以确保当前事务提交之前，其他事务无法在查询范围内插入、删除或修改数据，从而避免幻读。

Next-Key Locking 的实现机制

• Record Lock（行锁）： 精确锁定某一行，防止修改。
• Gap Lock（间隙锁）： 锁定数据行之间的空隙，防止插入新记录。
• Next-Key Locking（行锁 + 间隙锁）： 同时锁定行和间隙，保证数据一致性。

Next-Key Locking 的使用过程

1. 事务 A 执行范围查询，例如：

   SELECT * FROM users WHERE age BETWEEN 20 AND 30 FOR UPDATE;
   

   InnoDB 锁定范围内的数据行和间隙。
2. 事务 B 尝试插入 age=26 的新记录，但被阻塞。
3. 事务 A 提交或回滚后，事务 B 才能继续。

通过这种方式，Next-Key Locking 有效防止了并发插入导致的幻读问题。

优势与限制

优势

• 解决幻读问题： 保证范围查询结果的一致性。
• 增强数据安全性： 锁定查询范围内的间隙，防止未提交事务的干扰。

限制

• 降低并发性能： 锁粒度较大，可能导致锁竞争。
• 间隙锁开销： 高并发写入场景下，可能影响效率。

使用场景

1. 银行系统中的余额查询： 避免用户查询账户余额时插入新的交易记录，确保一致性。
2. 电商系统中的订单查询： 防止订单查询期间插入新订单，保证查询结果稳定。

总结

Next-Key Locking 通过结合行锁和间隙锁，锁定查询范围内的数据行及间隙，有效解决了 Repeatable Read 隔离级别下的幻读问题。虽然这种机制增强了数据一致性，但可能降低并发性能，在实际应用中需要权衡使用。