MySQL数据库中，在读已提交和可重复读这两个不同事务隔离级别下幻读的区别网站首页 学无止境

MySQL数据库中，在读已提交和可重复读这两个不同事务隔离级别下幻读的区别

yuhuofei2021 2023-07-02 00:00:03

简介MySQL数据库中，在读已提交和可重复读这两个不同事务隔离级别下幻读的区别

1. 前言

在正式开始之前，先简单回顾一下并发事务存在的问题以及事务的隔离级别等内容。

1.1 并发事务存在的问题

当两个或者两个以上事务同时开启去处理同一个表的数据时，可能会存在以下的问题：

丢失修改
脏读
不可重复读
幻读

丢失修改

丢失修改是指当两个或多个事务更新同一行记录，产生更新丢失的现象，事务回滚覆盖和事务提交覆盖都会导致这种现象的产生。

脏读

一个事务能读取到另一个事务已经修改但还没有提交的数据。

不可重复读

在一个事务中多次执行同一条查询语句，读取到的数据内容前后不一致。

幻读

在一个事务中多次执行同一条查询语句，读取到的数据记录在数量上前后不一致，可能多了几条记录也可能少了几条记录。

1.2 事务的隔离级别

为了解决并发事务存在的问题，大佬们想到了一个手段，那就是对事务进行隔离，最好是做到各个事务各干各的，互不干涉，但理想很丰满，现实很骨感，哪可能一步到位呢。

为了应对不同的需要，解决不同的问题，于是决定将事务的隔离分成四个级别，分别是：

读未提交
读已提交
可重复读
串行化

它们各自能解决的并发事务问题如下表所示：

隔离级别事务问题	事务回滚覆盖	脏读	不可重复读	事务提交覆盖	幻读
读未提交	能解决	不能解决	不能解决	不能解决	不能解决
读已提交	能解决	能解决	不能解决	不能解决	不能解决
可重复读	能解决	能解决	能解决	能解决	不能完全解决，可能发生
串行化	能解决	能解决	能解决	能解决	能解决

1.3 快照读和当前读

快照读

快照读是基于 MVCC 和 undo log 来实现的，读取数据的历史版本，得到一个 ReadView (事务视图) ，不对数据加锁，适用于简单 select 语句。

这里提一句，所谓 MVCC 并发版本控制，就是靠 ReadView (事务视图) 来实现的，多个 readView 组成 undo log（回滚日志）。

当前读

当前读是基于行锁 + 间隙锁来实现的，读取数据的最新版本，并对数据进行加锁，适用于 insert，update，delete， select ... for update， select ... lock in share mode 语句，以及加锁了的 select 语句。

在更新数据时，都是先读后写，而这个读，就是指当前读，意味读取数据时，读到的是该条数据最新生成的 ReadView。

2. 不同事务隔离级别下幻读的区别

在上面的表格中，我们能看到，在读已提交这个隔离级别下，幻读是不能被解决的，也就是说会发生；而在可重复读这个隔离级别下，幻读则是没有完全被解决，只是解决了部分，意味着仍然有可能发生。

那它们分别是怎么产生的？各自又有什么表现？有何区别？下面我们举个例子来探究一下。

假设现在有两个事务，分别是事务 A 和事务 B ，同时有一张学生表 student（表中只有一条记录，stu_name 为王大），我们用这两个事务来操作这张表。

2.1 读已提交下的幻读

在读已提交这种事务隔离级别下，两个事务的操作顺序如下：

事务A	事务B
begin;
	begin;
开始第一次查询：select * from student where stu_id > 0;
	insert into student(stu_name) values(‘李二’);
	commit;
开始第二次查询：select * from student where stu_id > 0;

事务 A 第一次查询，得到的数据记录是 stu_name 为王大这一条
事务 B 中途往表里插入了一条 stu_name 为李二的记录，并提交自身的事务
事务 A 用第一次查询的 SQL 语句进行第二次查询时，发现得到的数据记录成了两条，出现幻读

表现

普通的查询语句，能看到明显幻读现象。

原因剖析

事务 A 中用的是普通的 select 语句，因此采用的是快照读，但由于事务隔离级别为读已提交，在读已提交下，每次 select 操作，都会重新获取最新版本的数据，也正是因为这个原因，导致事务 A 中两次查询得到的结果在数量不一致，产生幻读。

解决方式

将事务隔离级别升级为可重复读

2.2 可重复读下的幻读

2.2.1 情况一，无幻读

在可重复读这种事务隔离级别下，两个事务的操作顺序如下：

事务A	事务B
begin;
	begin;
开始第一次查询：select * from student where stu_id > 0;
	insert into student(stu_name) values(‘李二’);
	commit;
开始第二次查询：select * from student where stu_id > 0;

像上面这样的事务操作，虽然在读已提交事务隔离级别下会产生幻读，但在可重复读事务隔离级别下，却不会产生幻读。

原因：在可重复读事务隔离级别下，针对普通的 select 语句，采用的是快照读，只会在第一次查询的时候获取一次数据的版本，往后继续做相同的 select 操作，不会重新获取，会延用前面得到的数据版本。

细心的你有没有发现，在可重复读事务隔离级别下，对于快照读（普通的 select 操作），这不就是使用 MVCC 解决幻读问题吗？你没看错，是这样的。

2.2.2 情况二，有幻读

在可重复读这种事务隔离级别下，两个事务的操作顺序如下：

事务A	事务B
begin;
	begin;
开始第一次查询：select * from student where stu_id > 0;
	insert into student(stu_name) values(‘李二’);
	commit;
开始第二次查询：select * from student where stu_id > 0 for update;

上面的事务操作，和前面不同的地方在于，第二次查询加上了 for update ，也就是采用当前读。

表现

能看到明显的幻读现象

原因剖析

由于事务 A 第二次查询在 SQL 语句末尾加上了 for update ，表示采用当前读的方式，获取数据的最新版本，那么自然而然会把事务 B 中途插入的数据给查出来，从而出现幻读。

解决方式

用 next-key lock 解决，如果是走索引，会锁住索引本身的行锁；如果是范围，就会成为一个行锁+间隙锁，导致范围内的无法插入；如果是无索引的，直接全表加上了间隙锁，无法插入，阻塞。

具体的操作方式如下：

事务A	事务B
begin;
	begin;
开始第一次查询：select * from student where stu_id > 0 for update;
	insert into student(stu_name) values(‘李二’);
	commit;
开始第二次查询：select * from student where stu_id > 0 for update;

事务 A 的每次查询都加上 for update，这样就不会出现幻读，原因是事务 B 的 insert 操作会被阻塞，无法将数据插入都表中，从而避免幻读的出现。

2.2.3 情况三，有幻读

在可重复读这种事务隔离级别下，两个事务的操作顺序如下：

事务A	事务B
begin;
	begin;
开始第一次查询：select * from student where stu_id > 0;
	insert into student(stu_name) values(‘李二’);
	commit;
update student set stu_class = ‘03’ where stu_id is not null;
开始第二次查询：select * from student where stu_id > 0;

在上面的事务操作下，事务 B 能将数据记录正常插入到表中，而事务 A 做了一次 update 操作，从上面的介绍我们可以知道，update 操作是当前读，会获取到数据的最新版本，自然也能拿到事务 B 的提交记录。

表现

能明显看到幻读现象

原因剖析

事务 A 的 update 操作采用的是当前读，会获取数据的最新版本，将事务 B 提交的结果读取出来，后面事务 A 再做普通的 select 操作，采用快照读，由于延用 update 操作时得到的数据历史版本，因而产生幻读。

解决方式

同 2.2.2 中的解决方式一样，具体操作如下：

事务A	事务B
begin;
	begin;
开始第一次查询：select * from student where stu_id > 0 for update;
	insert into student(stu_name) values(‘李二’);
	commit;
update student set stu_class = ‘03’ where stu_id is not null;
开始第二次查询：select * from student where stu_id > 0 for update;