MySQL 记录锁 + 间隙锁可以防止删除操作而导致的幻读吗？

昨天有位读者在美团二面的时候，被问到关于幻读的问题：

面试官反问的大概意思是，MySQL 记录锁 + 间隙锁可以防止删除操作而导致的幻读吗？

答案是可以的。

实验验证

接下来，来验证「MySQL 记录锁 + 间隙锁可以防止删除操作而导致的幻读问题」的结论。

实验环境：MySQL 8.0 版本，可重复读隔离级。

现在有一张用户表（t_user），表里只有一个主键索引，表里有以下行数据：

现在有一个 A 事务执行了一条查询语句，查询到年龄大于 20 岁的用户共有 6 条行记录。

然后，B 事务执行了一条删除 id = 2 的语句：

此时，B 事务的删除语句就陷入了等待状态，说明是无法进行删除的。

因此，MySQL 记录锁 + 间隙锁可以防止删除操作而导致的幻读问题。

加锁分析

问题来了，A 事务在执行 select ... for update 语句时，具体加了什么锁呢？

我们可以通过 select * from performance_schema.data_locks\G; 这条语句，查看事务执行 SQL 过程中加了什么锁。

输出的内容很多，共有 11 行信息，我删减了一些不重要的信息：

从上面输出的信息可以看到，共加了两种不同粒度的锁，分别是：

• 表锁（LOCK_TYPE: TABLE）：X 类型的意向锁；
• 行锁（LOCK_TYPE: RECORD）：X 类型的 next-key 锁；

这里我们重点关注「行锁」，图中 LOCK_TYPE 中的 RECORD 表示行级锁，而不是记录锁的意思：

• 如果 LOCK_MODE 为 X，说明是 next-key 锁；
• 如果 LOCK_MODE 为 X, REC_NOT_GAP，说明是记录锁；
• 如果 LOCK_MODE 为 X, GAP，说明是间隙锁；

然后通过 LOCK_DATA 信息，可以确认 next-key 锁的范围，具体怎么确定呢？

• 根据我的经验，如果 LOCK_MODE 是 next-key 锁或者间隙锁，那么 LOCK_DATA 就表示锁的范围最右值，而锁范围的最左值为 LOCK_DATA 的上一条记录的值。

因此，此时事务 A 在主键索引（INDEX_NAME : PRIMARY）上加了 10 个 next-key 锁，如下：

• X 型的 next-key 锁，范围：(-∞, 1]
• X 型的 next-key 锁，范围：(1, 2]
• X 型的 next-key 锁，范围：(2, 3]
• X 型的 next-key 锁，范围：(3, 4]
• X 型的 next-key 锁，范围：(4, 5]
• X 型的 next-key 锁，范围：(5, 6]
• X 型的 next-key 锁，范围：(6, 7]
• X 型的 next-key 锁，范围：(7, 8]
• X 型的 next-key 锁，范围：(8, 9]
• X 型的 next-key 锁，范围：(9, +∞]

这相当于把整个表给锁住了，其他事务在对该表进行增、删、改操作的时候都会被阻塞。

只有在事务 A 提交了事务，事务 A 执行过程中产生的锁才会被释放。

为什么只是查询年龄 20 岁以上行记录，而把整个表给锁住了呢？

这是因为事务 A 的这条查询语句是全表扫描，锁是在遍历索引的时候加上的，并不是针对输出的结果加锁。

因此，在线上在执行 update、delete、select ... for update 等具有加锁性质的语句，一定要检查语句是否走了索引，如果是全表扫描的话，会对每一个索引加 next-key 锁，相当于把整个表锁住了，这是挺严重的问题。

如果对 age 建立索引，事务 A 这条查询会加什么锁呢？

接下来，我对 age 字段建立索引，然后再执行这条查询语句：

接下来，继续通过 select * from performance_schema.data_locks\G; 这条语句，查看事务执行 SQL 过程中加了什么锁。

具体的信息，我就不打印了，我直接说结论吧。

因为表中有两个索引，分别是主键索引和 age 索引，所以会分别对这两个索引加锁。

主键索引会加如下的锁：

• X 型的记录锁，锁住 id = 2 的记录；
• X 型的记录锁，锁住 id = 3 的记录；
• X 型的记录锁，锁住 id = 5 的记录；
• X 型的记录锁，锁住 id = 6 的记录；
• X 型的记录锁，锁住 id = 7 的记录；
• X 型的记录锁，锁住 id = 8 的记录；

分析 age 索引加锁的范围时，要先对 age 字段进行排序。

age 索引加的锁：

• X 型的 next-key lock，锁住 age 范围 (19, 21] 的记录；
• X 型的 next-key lock，锁住 age 范围 (21, 21] 的记录；
• X 型的 next-key lock，锁住 age 范围 (21, 23] 的记录；
• X 型的 next-key lock，锁住 age 范围 (23, 23] 的记录；
• X 型的 next-key lock，锁住 age 范围 (23, 39] 的记录；
• X 型的 next-key lock，锁住 age 范围 (39, 43] 的记录；
• X 型的 next-key lock，锁住 age 范围 (43, +∞] 的记录；

化简一下，age 索引 next-key 锁的范围是 (19, +∞]。

可以看到，对 age 字段建立了索引后，查询语句是索引查询，并不会全表扫描，因此不会把整张表给锁住。

总结一下，在对 age 字段建立索引后，事务 A 在执行下面这条查询语句后，主键索引和 age 索引会加下图中的锁。

事务 A 加上锁后，事务 B、C、D、E 在执行以下语句都会被阻塞。

有一点要注意的是，在执行 update、delete、select ... for update 等具有加锁性质的语句，一定要检查语句是否走了索引，如果是全表扫描的话，会对每一个索引加 next-key 锁，相当于把整个表锁住了，这是挺严重的问题。

---