Mysql幻读如何解决

2020-12-16 09:47:55 浏览数 (2)

幻读也是在面试中经常被问到的，今天我们按照下面几个方面讲解

  1. 幻读是什么
  2. 幻读有什么问题
  3. 如何解决幻读

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

代码语言：javascript复制
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);

幻读是什么

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

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

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

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

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

幻读有什么问题

  • 破坏语义

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

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

  • 一致性问题

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

代码语言：javascript复制
update t set d=100 where d=5。

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

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

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

  1. T2时刻事物B提交了两条更新语句
  2. T4时刻事物C提交了两条语句
  3. T6时刻事物A提交了一个语句update t set d=100 where id=5

按照上面事物提交的序列日志如下记录

代码语言：javascript复制
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这一行进行了加锁，如果是对扫描的所有行加锁，是不是就可以了.看看下面执行的结果

在sessionA还没有提交的时候，是对所有行进行了加做，sessionB此刻是阻塞的,id=0执行的结果是(0.5,5),我们在看看日志的如何记录

代码语言：javascript复制
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个间隙

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

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

我们看到对于不存在的记录,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 的引入虽然解决了幻读的问题，但是也引来了一些问题

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

执行顺序

  1. sessionA先查询id=9不存在，就加上间隙锁(5,10]
  2. sessionB同理也会加上间隙锁(5,10]
  3. sessionB执行插入id=9的记录阻塞,等待sessionA释放间隙锁
  4. sessionA同理也被锁住，阻塞中，等待sessionB释放间隙锁

因此引入的间隙锁会增加锁的范围,影响并发度，那么我们还有其他办法解决幻读的问题吗，答案是有的，Mysql默认的事物隔离级别是可重复读，但是如何我们把事物设置成读已提交和binlog_format=row，也是可以解决幻读的问题，当然要这样配置是否合理要看你们的业务场景.

如果对您有一丝丝帮助，麻烦点个关注，也欢迎转发，谢谢

key lock mysql t4

0 人点赞