MySQL的预读机制 mysql是怎么解决幻读的

转载

mob6454cc6d1c0b 2023-10-16 20:58:49

文章标签 MySQL的预读机制如何解决幻读数据加锁隔离级别 文章分类 MySQL 数据库

幻读是什么
幻读有什么问题
如何解决幻读

首先我们建立我们的表，且插入6条数据，如下图

CREATE TABLE `t` (`id` int(11) NOT NULL,`c` int(11) DEFAULT NULL,`d` int(11) DEFAULT NULL,  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);

幻读是什么

按照上面建表语句，我们做如下操作

MySQL的预读机制 mysql是怎么解决幻读的_数据

sessionA中添加了三条相同的语句，都是给d=5这行添加行锁,且使用当前读,而上面运行的结果如下

Q1仅仅返回id=5这一行
由于sessionB修改了id=0中的d=5,在Q2中返回id=0,5
sessionC插入id=1这行数据,Q3返回id=0,1,5

这里Q3读到id=1这行数据就是幻读，幻读是指在一次事务中，前后两次相同的范围查询，看到了不一样的数据。

在可重复读级别下，普通查询是读取快照读，不可以看到其他事物中的插入的数据，只有当前读,才会有幻读
上面的Q2不是幻读,他是当前读，幻读是针对新插入行

幻读有什么问题

破坏语义

sessionA中T1select * from 他where d=5 for update 这句就是在锁住d=5所有行，不准别的事物进行读写操作,但是按照下面执行就会破坏

MySQL的预读机制 mysql是怎么解决幻读的_数据_02

sessionB中的语句是对id=0的记录进行了修改d=5.c=5(0,5,5)，由于sessionA只对id=5进行了加行锁，所以sessonB的两条更新语句没有问题，但是也就是破坏了d=5行的记录加锁的声明。

一致性问题

数据的一致性不仅仅是数据此刻的一致性，也包括数据和日志上的逻辑一致性，如我们在sessionA上加上下面语句.

update t set d=100 where d=5。

MySQL的预读机制 mysql是怎么解决幻读的_如何解决幻读_03

上面执行完之后会是上面结果呢

经过T1执行结果是(5,5,100)
经过T2执行结果是(0,5,5)
经过T4执行结果是(1,5,5)

上面数据上没有说明问题,我们再看看日志的记录

T2时刻事物B提交了两条更新语句
T4时刻事物C提交了两条语句
T6时刻事物A提交了一个语句update t set d=100 where id=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*/

看到日志，我们发现日志不管在哪里进行执行，最终的记过是(0,5,100)(1,5,100)(5,5,100),数据发生了不一致.

我们发现原来是此时只对id=5这一行进行了加锁，如果是对扫描的所有行加锁，是不是就可以了.看看下面执行的结果

MySQL的预读机制 mysql是怎么解决幻读的_隔离级别_04

在sessionA还没有提交的时候，是对所有行进行了加做，sessionB此刻是阻塞的,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*/update t set d=5 where id=0; /*(0,0,5)*/update t set c=5 where id=0; /*(0,5,5)*/

按照上面执行日志的记录，最终的id=0的结果是(0,5,5),但是我们发现id=1的结果却是(1,5,100),原因很简单，在T3时刻给所有记录加了锁，但是id=1此时不存在，因此没有加上锁，所以即使对所有的记录加上锁，也不能给新增的记录加上锁。最终也会导致数据不一致的问题。

如何解决幻读

我们知道行锁只能锁住行，但是新插入的记录是更新记录的间隙，因此引入了间隙锁，顾名思义就是两条记录的间隙，正如文章开头的记录，插入6条记录就有7个间隙

MySQL的预读机制 mysql是怎么解决幻读的_隔离级别_05

这样不仅仅对每一行数据加上了行锁，也为每一个间隙加上了间隙锁，因此新增的数据就无法插入。

行锁和行锁之间是有冲突的，但是间隙锁和间隙锁是没有冲突的，间隙做的冲突是往这个间隙插入记录的操作存在冲突，

MySQL的预读机制 mysql是怎么解决幻读的_加锁_06

我们看到对于不存在的记录,sessionA和sessionB都会给(0,5]这个间隙加上间隙锁，目的是一样的不允许插入值。

间隙锁和行锁合起来就是next-key lock，每个next-key lock是前开后闭的,如果使用select * from t for update ,就是对表的所有记录加锁，就形成了7个next-key lock,分别是 (-∞,0]、(0,5]、(5,10]、(10,15]、(15,20]、(20, 25]、(25, +supremum]。

next-lock key 的引入虽然解决了幻读的问题，但是也引来了一些问题

如下图执行结果，就可能引起死锁

MySQL的预读机制 mysql是怎么解决幻读的_数据_07