EagleBear2002 的博客

这里必须根绝一切犹豫,这里任何怯懦都无济于事

MySQL 可重复读如何“避免”幻读?

摘要

大多数数据库教材中明确可重复读(RR)不能防止幻读。在本文中,我们展示了 RR 在幻读下的一些表现,分析了 RR 的实现原理,并论证了为何 MySQL 的 RR 隔离级别下几乎不会出现幻读。本文内容来自南京大学软件学院刘峰老师课程《数据库开发》。

可重复读的实验

上图是可重复读隔离级别下的一个实验,事务 1 的两次查询得到的结果完全相同。看起来在这里可重复读防止了幻读,但我们其实希望读到 Dong 老师的数据。

RR 的实现原理

在 InnoDB 中,会在每行数据后添加两个额外的隐藏的值来实现 MVCC,这两个值一个记录这行数据何时被创建,另外一个记录这行数据何时过期(或者被删除)。在实际操作中,存储的并不是时间,而是事务的版本号,每开启一个新事务,事务的版本号就会递增。

在 RR 事务隔离级别下:

  • SELECT 时,读取创建版本号<=当前事务版本号,删除版本号为空或>当前事务版本号。
  • INSERT 时,保存当前事务版本号为行的创建版本号
  • DELETE 时,保存当前事务版本号为行的删除版本号
  • UPDATE 时,插入一条新纪录,保存当前事务版本号为行创建版本号,同时保存当前事务版本号到原来删除的行(即 UPDATE 相当于删除旧的,插入新的)

Read 的差异

我们平时只用使用 select 就是快照读,这样可以减少加锁所带来的开销。

对于会对数据修改的操作(updateinsertdelete)都是采用当前读的模式。在执行这几个操作时会读取最新的记录,即使是别的事务提交的数据也可以查询到。假设要 update 一条记录,但是在另一个事务中已经 delete 掉这条数据并且 commit 了,如果 update 就会产生冲突,所以在 update 的时候需要知道最新的数据。读取的是最新的数据,需要加锁。

1
2
3
4
5
select * from table where ?; # 加共享锁
select * from table where ? for update; # 加排他锁
insert; # 加排他锁
update; # 加排他锁
delete; # 加排他锁

SELECT FOR UPDATE 语句的意思是当前读到的语句接下来会被修改,相当于提前声明“我要写这些数据”,因此要加排他锁。程序员应当谨慎使用 SELECT FOR UPDATE

另外,因为数据库事务几乎没有“盲写”(即 write(x) 之前没有 read(x)),所以笔者认为对于准备修改的事务使用 FOR UPDATE 子句是良好的。

RC 和 RR 的对比

RC 的隔离级别

RR 的隔离级别

RR 如何“避免”幻读?

在 RR 隔离级别下:

  • 事务 1 在 update 后,对该数据加锁,事务 2 在事务 1 结束前无法插入新的数据;
  • 这样事务 1 在 update 前后数据保持一致,避免了幻读。

怎么做到的呢?

  • 可以明确的是,update 锁住的肯定不只是已查询到的几条数据,因为这样无法阻止 insert
  • 你嘴角一撇,靠,那不就是表锁嘛?
  • MySQL 这么成熟,怎么会如此抵销的使用表锁,它使用的是 NEXT-key 锁。

Next-Key 锁(考研不会考的我想)

很简单,还记得我们说过的外键加锁是一个普遍的状况嘛?

Users 表中,dept_id 如果有非聚簇索引,MySQL 维护一个非聚簇索引与主键的关系,我们通过 dept_id 找到这个索引所对应所有的节点,这些节点存储着对应数据的主键信息,我们再通过主键 id 找到我们需要的数据,这个过程叫回表

讲到这里,你就知道,B 树索引所有的数据都在叶节点上,只要在外键索引上构建一个区间锁,就可以保证 dept_id=1 的数据无法被插入(当然也无法被删除和修改),但是可以任意处理 dept_id!=1 的相关增删改,这种区间锁叫 GAP 锁。

RR 处理幻读的表现

教材明明说 RR 不能避免幻读,那么 RR 的表现到底如何?

Mysql 官方给出的幻读解释是:只要在一个事务中,第二次 select 多出了 row 就算幻读,所以在刚刚钦哥的那个这个场景下,是算出现幻读的。

SQL 标准中的术语幻象问题指非可重复谓词读(在这种情况下 SI 解决了幻象问题);但在另一些领域(如《数据库系统概念(第 6 版)》中译版第 390 页脚注也提到了这一问题,该书中 P369 对幻象问题的定义是“查询结果会因另一并发事务是先于还是后于执行的查询而有所不同”,因此该书不认为 SI 解决了幻象问题。

所以,理论上,可重复读(Repeatable Read)是无法避免幻读的。

实际上,可重复读(Repeatable Read)几乎都不会产生幻读。

你只要记住钦老师的那个行为能奏效的场景,在也许能用到的场景下注意就好了,只有这一种特例。