理解 mysql 幻读问题

2023-06-28, 1108 words, 5 min read

1 前置知识

1.1 快照读

单纯的select操作，不包括上述 select ... lock in share mode, select ... for update。　　　　
Read Committed隔离级别：每次select都生成一个快照读
Read Repeatable隔离级别：开启事务后第一个select语句才是快照读的地方，而不是一开启事务就快照读

快照读的实现方式：undolog和多版本并发控制MVCC

1.2 当前读

当前读，读取的是最新版本，并且对读取的记录加锁，阻塞其他事务同时改动相同记录，避免出现安全问题。以下形式的SQL属于当前读:

select...lock in share mode (共享读锁)
select...for update
update , delete , insert

例如，假设要update一条记录，但是另一个事务已经delete这条数据并且commit了，如果不加锁就会产生冲突。所以update的时候肯定要是当前读，得到最新的信息并且锁定相应的记录。
此处参考 https://www.jianshu.com/p/eb3f56565b42

2. 事务隔离级别和锁

对于读操作，mysql默认的隔离级别是 可重复读（Read Repeatable） 。普通的select查询是可重复读的，底层基于多版本并发控制MVCC的快照读 ，其他事务的修改操作对当前事务不可见，当前事务读的是历史版本。但是如果加上 for update 或者 in share mode 就会切换到当前读，能读到其他事务已经提交commit的数据。

其他隔离级别如下：

ISOLATION LEVEL	DIRTY READ	NON-REPEATABLE READ	PHANTOM READ
READ_UNCOMMITTED	allowed	allowed	allowed
READ_COMMITTED	prevented	allowed	allowed
REPEATABLE_READ	prevented	prevented	allowed
SERIALIZABLE	prevented	prevented	prevented

基于多版本并发控制MVCC实现的默认隔离REPEATABLE_READ并不能解决幻读（PHANTOM READ）问题，mysql解决幻读并不是在隔离级别层面上处理，而是通过锁层面处理，这是两个层面的问题。

这里，我需要对“幻读”做一个说明，来源自mysql45讲，第20讲：

在可重复读隔离级别下，普通的查询是快照读，是不会看到别的事务插入的数据的。因此，幻读在“当前读”下才会出现。

上面session B的修改结果，被session A之后的select语句用“当前读”看到，不能称为幻读。幻读仅专指“新插入的行”。

我自己的理解是，普通的行锁是锁住已经存在的行，而在可重复读的前提下插入的是第二次读的时候读到第一次读不存在的数据，所以这才是幻读（PHANTOM READ）。PHANTOM 在英文中就有幽灵的意思，意味幻觉，不存在的。对于“不存在的数据”和“不符合过滤条件的数据” 要分开理解，在mysql中，如果对应字段没有索引，会遍历所有行，然后对所有遍历过的行进行加行锁，即使没有符合条件的行结果。所以，幻读就是针对插入（insert）这种情况。

而mysql在可重复读隔离级别下，为了解决幻读，在行锁（record lock）的基础上，加入了间隙锁（gap lock）。行锁和间隙锁组合起来一起，就成为了临键锁（next-key lock）。

3. 覆盖索引

对于索引中包含了查询语句所有结果的情况，查询就不需要通过主键进行回表，对应索引就是覆盖索引。因为对于锁而已，它锁住的就是其搜索过的对象。如果没有索引而走全表扫描，锁住的就是全部行，走索引锁住的就是索引对象。

加锁规则如下，来源自mysql45讲，第21讲

加锁规则里面，包含了两个“原则”、两个“优化”和一个“bug”

原则1：加锁的基本单位是next-key lock。希望你还记得，next-key lock是前开后闭区间。

原则2：查找过程中访问到的对象才会加锁。

优化1：索引上的等值查询，给唯一索引加锁的时候，next-key lock退化为行锁。

优化2：索引上的等值查询，向右遍历时且最后一个值不满足等值条件的时候，next-key lock退化为间隙锁。

一个bug：唯一索引上的范围查询会访问到不满足条件的第一个值为止。