MySQL在RR级别下到底会不会造成幻读【转载】

最新推荐文章于 2024-01-17 19:48:40 发布

秃了也弱了。

最新推荐文章于 2024-01-17 19:48:40 发布

阅读量341

点赞数 2

分类专栏： mysql 文章标签： mysql 数据库 sql

原文链接：https://blog.csdn.net/javaanddonet/article/details/111187211

版权

mysql 专栏收录该内容

44 篇文章 9 订阅

订阅专栏

文章目录

背景
疑问点
分析
MySQL到底有没有解决幻读
总结
参考资料

背景

我最近一直被一个问题困扰着：MySQL在其默认的可重复读(Read-Committed)RR隔离级别下，到底有没有解决幻读的问题？

看了网上很多的贴子，感觉每一个帖子都有一个观点，越看越感觉混乱。有的说是在RR级别下，MySQL的幻读不会发生；有的说RR级别下，MySQL中幻读会发生。各执一词，并且每一个观点都不能给出一些有说服性的例子。所以，打算自己去分析一下到底是否解决了幻读的问题。

疑问点

我的疑惑只要有以下几点：

1.什么是快照读？
2.什么是当前读？
3.什么是幻读？
4.如果说MySQL解决了幻读，那为什么在当前读下面还会发生幻读的问题？
5.幻读的解决是不是需要在SQL中去使用某种方式才可以“启用”避免幻功能，会不会是默认不启动的？

分析

什么是快照读

说到快照读，就得先说一下快照，而说到快照，就得说说MVCC。

快照是属于MVCC中的一个概念。在RR级别下，MySQL通过MVCC的技术会给每一个事务在启动的时候，创建一个一致性的快照视图，这个快照中的所有数据内容就是这个事务在启动时刻的生成的，后续数据库中的数据再怎么变化，这个快照的数据内容都不受它们的影响。而这个快照一直会伴随着整个事务的生命周期。在这个事务运行过程中的，所有的普通查询都会从这个快照中去获取数据，事务中的这些普通的查询就属于快照读。

举例说明一下什么是快照读，下面事务中的几个查询语句都是属于快照读。

start transaction with consistent snapshot; -- begin/start transaction命令也可以
do something...
select * from t; -- 快照读
do something...
select * from t where id = 1; -- 快照读
do something...
select * from t where name = 'zhangsan'; -- 快照读
do something...
commit;

开启事务命令的区别
说明：上面的开启事务的方式，没有使用begin或start transaction命令，而是使用了start transaction with consistent snapshot命令启动的事务。其实，这两个命名都是可以开启事务的。但是它们之间有一点点区别。

start transaction with consistent snapshot命令会在事务开启之后马上就创建MVCC一致性视图。而使用begin或start transaction命令启动的事务，会在开启事务后，第一次操作InnoDB表的SQL语句后，才会创建MVCC一致性视图，这里的操作InnoDB的SQL语句可以是insert、update、delete、select中的任何一个，但是要求是操作的InnoDB存储引擎的表，不能是其他引擎的表。

通过下面的两个实验截图来说明这两个命令的区别。

begin开启事务如下：
在这里插入图片描述
上面的第6步中，你可能会感觉到很奇怪：为什么第6步可以看到右侧事务新增加的数据行呢？在RR级别下，左侧的事务启动后，在事务运行期间和结束后，应该看不到右侧事务新增加的行才对？这不就是发生不可重复读的问题了吗？这和我们平时所说的RR级别下，MySQL是支持可重复的结论相违背呀？

其实不违背我们平时说的RR下面MySQL支持可重复的结论。之所以出现上面的这个情况的根本原因是MVCC一致性视图在一个事务当中，创建的时间点是什么时候？是事务开启之后就创建了？还是在事务执行的过程中在某一个动作之后才创建。如果你再上面实验的步骤3之后，不直接去执行步骤4，而是在左侧事务中执行一个和步骤3一样的查询动作，我们暂时称为步骤3.1，如果你再左侧的事务中，3.1步骤，那么久不会发生上面左侧事务读取到右侧事务中插入数据的现象了。因为这个3.1的动作就会触发创建一致性视图的动作，而此时左侧事务创建的MVCC视图中的数据就不会包含右侧事务步骤4插入的数据行。

我们分析一下上面的这个过程：

步骤6是发生在右侧事务开启并结束之后，是步骤1执行完成后，我们就没有在左侧事务中执行任何操作，而是直接在右侧事务中一口气的把右侧的事务执行结束。
此时数据库的表已经有(5,55)这一行数据了。
而在步骤6中，此时是左侧事务在执行完begin命令之后，第一次操作innodb类型的表的SQL语句。此时执行SQL语句的时候才去创建的MVCC一致性视图。
在创建一致性视图的时候，是会获取数据库中最新的已经提交的数据作为MVCC视图中的数据。所以在步骤6这个SQL语句中，会查询出来右侧事务中已经提交的(5,55)这一行数据。

start transaction with consistent snapshot开启的事务如下：
在这里插入图片描述

什么是当前读

在一个事务执行的过程中，如果我们这个时候使用了DML语句，也就是我们平时所说的insert、update、delete语句，此时DML会执行当前读，它们会在操作数据库内容之前，去读取数据库中当前时间点以及提交的最新的数据，基于最新的数据的基础上，再去做这个DML语句自己的SQL逻辑。此时的这个读取数据库中最新已提交的数据的这个动作，就是当前读。

我们拿一个事务当中的update语句来说，在修改数据的时候，需要先读到数据，才能基于读到的数据上再去做修改。而这个读取数据的时候，需要基于数据库中最新的已经提交的数据来做，如果此时仍然按照一致性快照读，那么会读取到当前事务开启的时候所能读取到的数据版本，而这个数据版本有可能已经不是最新的了，其他事务可能已经在这个数据版本的基础上进行的修改，如果不去数据库中读取其他事务更改后的数据，那么此时就会覆盖掉其他事务的修改操作。而这是数据库中锁不允许的。所以，在修改的时候，要执行当前读，然后再修改。

在一个事务当中，使用DML语句操作数据库就是属于当前读的范畴。例如使用如下的SQL语句就是属于当前读。

begin;
do something...
insert into t values(11,'zhangsan'); -- 会发生当前读
do something...
update t set name = 'zhang' where id = 11; -- 会发生当前读
do something...
delete from t where id = 11; -- 会发生当前读
do something...
commit;

除了我们平时使用的DML之外，如下两个SQL语句也属于当前读的范畴：

begin;
do something...
select * from t where id = 1 lock in share mode; -- 会发生当前读
do something...
select * from t where id = 2 for update; -- 会发生当前读
do something...
commit;

什么是幻读

幻读是基于插入的操作而言的。更新、删除操作不属于幻读的范畴，属于不可重复读的范畴。

当前事务在运行的过程中，一开始的时候没有读取到其他事务插入的行，但是后来读取到了其他事务插入数据，这才是幻读。读取到其他事务更新、删除的操作内容，不是幻读，而是不可重复读。

MySQL到底有没有解决幻读

我所的困惑点只要有以下几个：

MySQL在RR隔离级别下，幻读到底会不会发生？
如果会发生，那么MySQL能避免这种情况的发生吗？
如果可以避免，那它是使用什么方式来避免的呢？

带着以上这3个问题，我们来逐步做实验来验证一下。下面所有的实验都是在MySQL5.7版本的RR事务隔离级别下进行的。

在只有快照读前提下，不会发生幻读

也就是说，如果在事务执行过程中，全部都是使用的一致性快照读，他们读取的数据都是从快照视图中读取的数据，此时的数据就是在事务开始的时候创建好的，在事务执行的过程中，任何时候只要是从快照中去读取，那么数据永远都是一样的，不会发生变化。所以说，在快照读的情况下，不会发生幻读，如下所示：
在这里插入图片描述
注意：在一个事务当中，如果有一次或多次发生了当前读，就有可能会发生幻读。例如先开始的时候是执行的快照读，后来执行了一次因为更新语句而发生的当前读，然后再次执行快照读，就有可能发生幻读。

这里的有可能是有这几个前提：

1.一个事务中，快照读和当前读混合使用。
2.在执行当前读之前，另外一个事务插入了新的数据。
3.在当前这个事务中，执行当前读的时候，查询的范围结果中包含了另外一个事务的插入数据。
4.再次执行快照读，幻读就会发生。

begin;
select * from t; -- 快照读
-- 在此时间点，当前事务下面的update语句还没有执行的时候，如果有另外一个事务，向t表中插入的一条数据，然后当前事务再去执行更新全表数据的语句，更新完成后，再次执行快照对，就有可能会发生幻读。
update t set a = a + 1; -- 更新语句，会先执行当前读再去更改数据。
select * from t; -- 此时的快照读有可能发生幻读。
commit;

当前读的时候，可能会发生幻读

开启左侧事务A
开启右侧事务B
是左侧事务A中查询表t
在右侧事务B中查询表t
在右侧事务B中，向表t插入一行数据(5,55)
在右侧事务B中，查询表t，可以查询到自己刚插入的新数据。
在左侧事务A中，查询表t，看不到事务B中刚插入的数据。
提交右侧的事务B
在右侧事务B提交后的窗口中，再次查询表t，可以看到刚插入的数据行。
在左侧事务A中，查询表t，仍然查询不到右侧事务B所插入并提交的数据。
在左侧的事务A中，更新表中所有的数据，不使用任何条件。此时发现输入的影响结果的行数不是前面事务A所能查询到的4行数据，而是输出影响了5行数据，多了一行。此时在更新的时候，使用了当前度的功能，把刚才右侧事务B中插入并提交的一行数据(5,55)也给查询出来并其修改掉了。所以在提示结果中，显示影响行数为5行。此时已经发生了幻读，事务A读取到了事务B的插入的数据。
在右侧事务B结束的窗口中，继续查询表t，发现可以看到5行数据，但是数据内容不是左侧事务A修改的结果，此时正常，因为左侧的事务A还没有提交。
在左侧事务A中，再次查询表t的数据，发生此时的结果为5行数据。包含了右侧事务B插入且提交的数据行(5,55)，并且它被上面的update语句给修改为了(5,5500)。此时发生了幻读。

实验截图如下：
在这里插入图片描述
注意：把上面截图中的第11步更新全表数据的操作，换成如下SQL语句，也会发生幻读。上面的试验操作是把右侧事务新插入的行通过update语句给修改了，下面的两个SQL分别是在左侧事务中尝试再次插入同一行数据，和删除右侧事务新插入的数据。

/*
在左侧事务中，再次尝试插入右侧事务已经插入且提交的数据行的试试，会提示主键冲突的错误，但是查询整个表的数据又发现没有这样的主键值的行，但是就是插入不成功。这也是幻读的一种体现。
*/
insert into t values(5,55);


/*
在左侧事务中，尝试删除右侧事务中插入且提交的数据，此时发现影响行数为1行，我们预期的应该是影响行数为0行，因为我们前面查询的时候，并没有id=5的这样的行存在，但是删除的时候却提示删除成功了。并且提交左侧事务后，在右侧已经结束的事务窗口中再次查询表t，发现之前可以看到的新插入的数据，确实不存在了，被左侧的事务给删除掉了。
*/
delete from t where id = 5;

当前读中发生的幻读是如何解决的

针对前面我们在当前读中锁发生的幻读的现象，MySQL在RR下面，到底能否解决这样的幻读问题呢？答案是肯定的，是通过间隙锁来实现的。

原理就是在我的事务将要操作的表上，除了增加行锁之外，增加间隙锁，让其在这些间隙中，不能插入数据，然后再我的事务后面即便是我执行了当前读也不会发生幻读的现象了。

实验如下：
在这里插入图片描述
注意：上面的第5步中是给表增加了表级别的S锁。这里的加锁方式取决于我们的SQL语句是什么样子的，如果是for update语句，那就是增加X锁，如果是lock in share mode就是S锁，这是指锁的类型。那么锁的粒度或者说是范围是什么样子的呢？

至于锁的粒度范围也是取决于我们的SQL语句和我们的表结构设计的。

如果SQL语句中的where条件，使用到了索引，那么就是增加行锁和间隙锁或临键锁。
如果SQL语句中的where条件，没有使用到了索引，使根据一个普通的列筛选条件，那么将会降级为表锁。把整个表都给锁上了。
这里比较复杂，有各种加锁的方式和规则。后续单独分享一下这里的加锁的逻辑。

总结

所以，到目前为止，我们的疑惑目前应该依据清楚了。

MySQL在RR隔离级别下，幻读到底会不会发生？会的。
如果会发生，那么MySQL能避免这种情况的发生吗？能。
如果可以避免，那它是使用什么方式来避免的呢？显示的使用间隙锁。

MySQL在RR隔离级别下，是有可能会发生幻读的。这里的有可能是有以下前提条件的。

在一个事务当中，快照读和当前读都使用到了。
与此同时，其他事务刚好有插入数据的操作发生且提交了。
当前读在当前事务中发生的时间点是在其他事务插入数据且提交之后。
然后在当前事务中再次执行快照读或当前读的时候，如果查询条件搜索的范围刚好可以包含其他事务插入的数据，则会发生幻读。

严格意义上，MySQL是在一定程度上修复了幻读。为了修复幻读的问题，它提供了间隙锁。在事务中，操作数据之前，我们给我们要操作的数据范围增加上了正确的间隙锁就可以避免幻读的发生。

MySQL提供了修复幻读机制：间隙锁。但是需要业务自己去加锁，如果不加锁，只是简单的SELECT查询，是无法限制并行事务的插入数据的。

而这个加锁的功能，不是MySQL自动就给增加上的，需要结合我们自己的实际业务场景，有选择性的去“开启”这个功能，当我们去开启了这个加锁的功能后，从而可以避免幻读的发生。只是锁数据的粒度、范围是MySQL自己控制的，它会根据我们的表结构、主键、唯一索引、普通索引、普通数据列、SQL语句的where条件等关系，来自动决定锁数据的粒度是使用行锁、还是间隙锁、还是临键锁、还是表锁。

开启这个功能的方式就是在我们的事务中，当前读之前，先去获取锁，然后再去做DML的当前读。

例如下面两个语句，第一个是不能避免大于10的id行在其他事务中别插入的动作，而第二个SQL是可以避免其他事务在执行完该语句后再次向表t中插入id大于10的记录。其实这就是启用的间隙锁的功能，避免了后续出现幻读的可能性。

select * from t where id > 10;
select * from t where id > 10 for update;

参考资料

本文转自：https://blog.csdn.net/javaanddonet/article/details/111187211

秃了也弱了。

关注

2
点赞
踩
4

收藏

觉得还不错? 一键收藏
0
评论
MySQL在RR级别下到底会不会造成幻读【转载】

说到快照读，就得先说一下快照，而说到快照，就得说说MVCC。快照是属于MVCC中的一个概念。在RR级别下，MySQL通过MVCC的技术会给每一个事务在启动的时候，创建一个一致性的快照视图，这个快照中的所有数据内容就是这个事务在启动时刻的生成的，后续数据库中的数据再怎么变化，这个快照的数据内容都不受它们的影响。而这个快照一直会伴随着整个事务的生命周期。在这个事务运行过程中的，所有的普通查询都会从这个快照中去获取数据，事务中的这些普通的查询就属于快照读。
复制链接

扫一扫