还分不清楚MySQL幻读与不可重复读吗？一个小实验帮你搞定

最新推荐文章于 2024-09-11 10:18:51 发布

CR553

最新推荐文章于 2024-09-11 10:18:51 发布

阅读量203

点赞数 1

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

本文链接：https://blog.csdn.net/weixin_44273948/article/details/120192585

版权

mysql 专栏收录该内容

2 篇文章 0 订阅

订阅专栏

不可重复读与幻读一直有人分不清，而且网上对这两者的区别和解释的也不太到位，有的甚至会误人子弟。

我以我的理解解释一下幻读和不可重复读，若有错误还望指正。

希望文章中的例子可以自己实验一样，对事务的理解会更深刻。

首先看一下对不可重复读和幻读的解释

不可重复读（幻影问题）：指在同一事务内，多次相同的SQL返回的结果却不同

幻读：事务A前后查询多次的结果条数是一样的，此时事务A查询的记录是不包括事务B新增的那条记录的，但是此时事务A对事务B插入的那条记录是可以执行更新操作的，操作完之后再次查询就会发现记录条数更加了一条。难以用语言描述，我这实际上是在用一个例子解释。

问题是

为什么innodb的repeatable read的隔离级别下能解决不可重复读问题而不能解决幻读问题？

因为innodb利用Next-key Locking解决不可重复读问题，每次查询会锁定记录和一个范围，以隔离本事务中已经查询过的行记录来避免其他事务的干扰，实现可重复读。但是Next-key Locking 无法解决幻读问题，因为其他事务可以修改或插入本事务中没有被锁定的范围内的数据，且本事务感知不到这种修改或插入，即原来的查询语句仍返回原来的结果。但是，本事务是可以对这种插入做修改的！因为事务B已经成功提交了，这种感知不到却能做出修改的行为就好像幻觉一样。

接下来会用实验演示，为什么会产生不可重复读，如何实现可重复读，为什么会出现幻读

数据准备如下：

create table t (a int primary key)

insert into t select 1;

insert into t select 2;

insert into t select 5;

不可重复读如下：

时间	事务A	事务B
1	set session transaction_isolation=‘read-committed’（设置隔离级别为读已提交）
2	begin；
3	select * from t where a >2 for update (结果返回5)
4		begin;
5		insert into t select 4;
6		commit;
7	select * from t where a >2 for update (结果返回4,5)
10	commit；

这里产生了不可重复读问题，在read-committed下，innodb会采用record Lock的方式锁定行记录，即select * from t where a >2 for update只会锁住5这行记录，所以事务B插入4成功了。

但是如果在repeatable read下，innodb会采用Next-key Lock的方式，除了锁住5这行记录以外，还会锁住a>2这个范围，因此事务B无法插入4这行记录，实现了可重复读。

但是这又产生了一个问题，只锁住了已经查询到的行记录，这里是5，还有一个范围，这里是a>2，但是还有没有被锁住的地方，比如a<2这个范围，这里就是会产生幻读的地方！

先接着恢复表中的数据还是1,2,5这三条

幻读如下：

时间	事务A	事务B
1	begin
2	select * from t where a >2 for update (结果返回5)
3		begin;
4		insert into t select 0;
5		commit;
6	select * from t where a >2 for update (结果还是返回5，但t表已经有4条记录)
7	update t set a=-1 where a=0;（成功了）
8	select * from t;（返回-1,1,2,5）
9	commit；