读mysql45讲-幻读

最新推荐文章于 2024-11-14 15:58:00 发布

西埃斯迪恩-霖

最新推荐文章于 2024-11-14 15:58:00 发布

阅读量214

点赞数

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

本文链接：https://blog.csdn.net/ZRL1996/article/details/123364642

版权

待删除专栏收录该内容

82 篇文章 0 订阅

订阅专栏

创建一张表：

create table `t` ( `id` int(11) not null,
`c` int(11) default null,
`d` int(11) DEFAULTNULL,
primary key (`id`),
key `c` (`c`) ) ENGINE = InnoDB;

插入几条数据：

insert into t values(0,0,0),(5,5,5), (10,10,10),(15,15,15),(20,20,20),(25,25,25);

然后我们考虑一个简单的sql：

begin; 
select * from t where d=5 for update;
commit;

字段d上没有索引的，所以会进行全表扫描，扫描到满足条件得数据之后，因为后面的for update语义，会在select语句查询结束之后给d=5对应的主键id=5这一行加上写锁，并且由于两阶段锁协议，锁真正的释放是在提交事务的时候才会释放的。

在全表扫描的过程中，其他被扫描到不满足条件的数据会被加上锁么？

先假设只会给id=5这一行的数据加锁（下面的事务隔离级别都是可重复读。）

幻读是什么？

在这里插入图片描述

在Q1查询时刻，因为初始化数据的时候已经插入了几条数据，所以Q1会查询到一条满足条件的数据(5,5,5);
在Q1查询之后，会给(5,5,5)这一行数据加上写锁(上面假设的只会给满足条件的加上锁)
sessionB想更新id=0的这条数据，因为上面加锁的是id=5这一行，所以不会影响到sessionB的操作，正常执行，将id=0的字段d更新为5；
Q2查询语句的时候，除了会查询到初始化的那一条数据，还会查询到sessionB更新之后满足条件的数据
seesion执行插入操作，插入一条数据
Q3查询的时候，除了查询到初始化的数据，sessionB更新满足条件的数据，还会查询到sessionC插入的数据。

其中Q3中查询到id=1(新插入)这一行的数据的现象就是 “幻读”，也就是前一次查询和后一次查询查询得到的查询结果是不一样的。

在可重复读事务隔离级别下，普通的查询是快照查询，也就是看到的数据都是快照那一刻的数据；因此“幻读“只会在当前读条件下发生。
sessionB通过更新操作是Q1查询和Q2查询看到的结果是不称为”幻读“的，”幻读“仅仅指对新插入的数据行来说的。

幻读有什么问题？

首先是语义上的。

首先 session A在T1时刻就声明了，“我要把所有d=5的行锁住，不准别的事务进行读写操作”。而实际上，这个语义被破坏了。

在这里插入图片描述

sessionB是可以执行的，因为我们之前假设锁只是加在id=5的这一行上面的，所以更新id=0的数据是不受影响的，但是第一个update语句将d=5，第二个update语句就相当于把一个d=5的数据更新了，这个和sessionA时刻给d=5加上写锁是冲突的。sessionC中在插一条数据之后，下面的update语句，也是将d=5的数据更新了，也破坏了T1时刻的申明。

其次，是数据一致性的问题。

我们知道，锁的设计是为了保证数据的一致性。而这个一致性，不止是数据库内部数据状态在此刻的一致性，还包含了数据和日志在逻辑上的一致性。

在这里插入图片描述

T1时刻之后id=5的这一行的数据变为了(5,5,100)，但是这个事务是在T6提交之后生效的。
T2时刻之后，id=0的数据变为了(0,5,5),提交事务
T4时刻之后，多了一行(1,5,5)，提交事务
T6时刻，提交事务

然后binlog记录的内容：

T2时刻，session B事务提交，写入了两条语句；
T4时刻，session C事务提交，写入了两条语句；
T6时刻，session A事务提交，写入了update t set d=100 where d=5 这条语句。

合并到一起之后就是：

update t set d=5 where id=0; /(0,0,5)/

update t set c=5 where id=0; /(0,5,5)/

insert into t values(1,1,5); /(1,1,5)/

update t set c=5 where id=1; /(1,5,5)/

update t set d=100 where d=5;/所有d=5的行，d改成100/

这个语句序列，不论是拿到备库去执行，还是以后用binlog来克隆一个库，这三行的结果，都变成了 (0,5,100)、(1,5,100)和(5,5,100)。也就是说，id=0和id=1这两行，发生了数据不一致。这个问题很严重，是不行的。

分析一下可以知道，这是假设“select *fromt where d=5 for update这条语句只给d=5这一行，也就是id=5的这一行加锁”导致的。所以上面的设定不合理，要改。那怎么改呢？把扫描过程中碰到的行，也都加上写锁，再来看看执行效果。

在这里插入图片描述

因为在T1时刻给所有的d=5加上了锁，所在sesionB在第一个update语句执行的时候就会被阻塞，会sessionA在T6时刻提交事务之后才可以操作。
但是sessionC的操作是可以的，因为在T1时刻给d=5加锁的时候，id=1这一行是不存在的，所以是加不上锁的，所以还是出现了幻读的现象。

如何解决幻读？

产生幻读的原因是，行锁只能锁住行，但是新插入记录这个动作，要更新的是记录之间的“间隙”。
因此，为了解决幻读问题，InnoDB只好引入新的锁，也就是间隙锁(Gap Lock)。

顾名思义，间隙锁，锁的就是两个值之间的空隙。

在开始的时候插入了6条记录(0,0,0),(5,5,5), (10,10,10),(15,15,15),(20,20,20),(25,25,25)，所以产生了7个间隙。

在这里插入图片描述

行锁分为读锁和写锁，之间的关系如下：

在这里插入图片描述

但是间隙锁不一样，跟间隙锁存在冲突关系的，是 “往这个间隙中插入一个记录”这个操作。间隙锁之间都不存在冲突关系。

在这里插入图片描述

现在c=7的数据是不存在的，所以sessionA加的是间隙锁，同理sessionB也是加的间隙锁。间隙锁和间隙锁之间没有冲突的。

在这里插入图片描述

这个图是我直接截图的，但是我开了两个数据库连接的客户端，想要模拟sessionA和sessionB的操作。

sessionA: begin 开启事务
sessionA: select * from words where id = 27 for update; 加上间隙锁
sessionB: begin 开启事务
sessionB : select * from words where id = 27 for update; 加上间隙锁
sessionB : insert into words values(27,‘222’); 我这边的效果是执行sql一直转圈，然后50s左右的时候报错提示超过等待时间，尝试重启事务，
sessionA : Linsert into words values(27,‘222’); 直接提示更新一行成功
提交sessionA事务之后，可以看到插入的数据，没有上图中的提示发现死锁。

其中步骤5，报错是因为步骤2中session已经给加上了间隙锁，所以步骤5的操作会被阻塞。