幻读在 InnoDB 中是被如何解决的?(转)

MySQL事务初识中,我们了解到不同的事务隔离级别会引发不同的问题,如在 RR 级别下会出现幻读。但如果将存储引擎选为 InnoDB ,在 RR 级别下,幻读的问题就会被解决。在这篇文章中,会先介绍什么是幻读、幻读会带来引起那些问题以及 InnoDB 解决幻读的思路。

实验环境:RR,MySQL 5.7.27

为了后面实验方便,假设在数据库中有这样一张表以及数据,注意这里的 d 列并没索引:

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);

什么是幻读?

幻读:是指在同一个事务中,前后两次查询相同范围时,得到的结果不一致,后一次查询到新插入的行。

这里需要注意的是,由于在 RR 级别下,普通的读是快照读(一致性读),所以幻读仅发生在当前读的基础上

举例来说:

select * from t where d=0 就是快照读,对于同一个事务来说,每次读到的结果是一样的。

select * from t where d=0 in share mode 或 select * from t where d=0 for update 就是当前读,总是读取当前数据行的最新版本,关于数据行版本问题可参考事务究竟有没有被隔离

回到幻读,有如下 Session:

Session A Session B
begin:  
select * from t where d=5 for update;  
  insert into t values(1,1,5);
select * from t where d=5 for update;  
commit;  

Session A 第一个 select 结果是:(5,5,5),第二个 select 结果是(1,1,5)和(5,5,5)。由于两次当前读的结果不一致,这就表明出现了幻读。有一点需要说明,你在尝试 Session B 会被阻塞,因为在 RR 级别下,默认已经将幻读的问题的解决,这里仅作为思考的过程。

幻读带来的问题?

为了更好的展现幻读带来的问题,为 Session A,B 添加一条 SQL:

Session A Session B
begin:  
select * from t where d=5 for update;  
update t set d=100 where d=5;  
  insert into t values(1,1,5);
  update t set d=5 where id=1;
select * from t where d=5 for update;  
commit;  

1. 破坏了语义

新的 Session B 中,除了添加一条新记录外,还修改了新记录的 d 值。这就破坏了 A 的语义, Session A 的目的就是锁住所有 d=5 的行,不让其被操作。

2. 数据一致性的问题

锁的存在就是为了避免在并发条件下,出现的数据一致性的问题。这里我们看下 A,B 提交后数据库的数据结果:

id=1 插入了一条新的记录,id=5 的记录 d 被修改成 100.

(0,0,0),
(1,5,5);
(5,5,100),
(10,10,10),
(15,15,15),
(20,20,20),
(25,25,25);

上面的结果看似没有问题,这里看下生成的 binlog 的执行逻辑,由于 Session B 先提交,所以对应语句在前:

# Session B 先执行
insert into t values(1,1,5); /*(1,1,5)*/
update t set c=5 where id=1; /*(1,5,5)*/

# Session A 后执行
update t set d=100 where d=5;/*所有d=5的行,d改成100*/

如果拿此 binlog 进行数据恢复,可见 id=1 的这样行被修改成了(1,5,100),这就出现了数据一致性的问题。

如何解决幻读?

对于 select * from t where d=5 for update; 来说,锁住d=5对应的行或者锁住扫描过程中所有的行都是没有用的, 因为插入并不影响之前行的操作,所以 InnoDB 为了解决幻读,引入了新的锁 - 间隙锁。

间隙锁,会将行之间的空隙锁住。比如,初始化是插入的 6 个值,就会产生 7 个空隙。

幻读在 InnoDB 中是被如何解决的?(转)

当再执行select * from t where d=5 for update;时,不但会将全表的数据行锁住,还会将间隙锁住。

这里提一下,由于 d 没有索引,所以走全表扫描,会将整个表锁住。

事务是否隔离这篇文章中知道,行锁(Record Lock)按照类型分为读锁和写锁,并且行锁与行锁在不同的事务间是互斥的。

但间隙锁不同,正由于它解决的是幻读插入的问题,所以间隙锁仅仅对插入操作本身互斥,不同事务之间的间隙锁并不互斥。

比如下面这两个事务:

Session A Session B
begin:  
select * from t where c=7 lock in share mode;  
update t set d=100 where d=5; begin;
  select * from t where c=7 lock in share mode;

由于 c=7 这条记录并不存在,出于共同的目的,防止其他值的插入。Session B 不会被阻塞。Session A 和 Session B 都会为其加上(5,10)的间隙锁。

为了加锁时的方便,间隙锁和行锁的合集称为 next-key lock.行锁锁住的是存在的记录行,间隙锁锁住的是行之间的空隙。而 next-key lock 锁住的是两者之和,比如 select * from t for update 锁住的就是 (-∞,0]、(0,5]、(5,10]、(10,15]、(15,20]、(20, 25]、(25, +supremum]。

(-∞,0],由间隙锁 (-∞,0]) 和行锁 0 组成,其他类似。

+supremum 表示 InnoDB 给每个索引加了一个不存在的最大值。

next -key lock 影响并发怎么办?

间隙锁的引入,虽然解决了幻读的问题,但同时也降低了并发度。

比如下面的业务逻辑,锁住一行,如果该行不存在就插入否则就更新:

begin;
select * from t where id=N for update;

/*如果行不存在*/
insert into t values(N,N,N);
/*如果行存在*/
update t set d=N set id=N;

commit;

当查询一条不存在的记录时,会给所在 id 的间隙加上间隙锁。假如同时出现并发的情况,由于间隙锁之间不冲突,两个事务都会加上间隙锁。之后执行插入时,每个事务的插入操作与另外事务的间隙锁出现冲突,进而引发死锁。

由此看见,间隙锁的引入导致同样的语句锁住更大的范围,降低了并发度。

假如业务需求并不需要间隙锁怎么办,这时可以将隔离级别 RC,在此级别下就不存在间隙锁了。由此引出一个问题,为什么一般在 RC 下,binlog 的格式要设置成 row 呢?

 

为什么 在 RC 级别下,binlog 格式要设置成 row?

先来看下 binlog 的三种格式:

  • --binlog-format=STATEMENT :在 Master 向 Slave 同步时,会以原生的 SQL 语句进行同步。
  • --binlog-format=ROW :Master 会把被操作后的表中的行记录在日志中, 向 Slave 同步。简单来说同步的就是表中的数据
  • --binlog-format=MIXED :默认会以 STATEMENT 的方式记录,但在一些情况下可以自动的切换成 ROW 方式,比如执行用户自定义的函数 UUID.

这里采用反证法,如果在 RC 级别下,将 binlog 的格式设置成 Statement 会发生什么?

还是使用之前 RR 级别下幻读的例子:

Session A Session B
begin:  
update t set d=100 where d=5;  
   
  insert into t values(1,1,5);
  update t set d=5 where id=1;
   
commit;  

得到的结果是一样的,Binlog 日志中 Session B 先执行,Session A 后执行,A 会把 id=1 中 d 的值改为 100,出现了 binlog 和 数据库数据不一致的现象。

而基于 ROW 格式则不同,binlog 日志中记录的是被操作后的数据,不是重新执行 SQL 自然就没有这个问题。

 

可重复读隔离级别下:next_key_lock 锁住sessionB,保证数据一致

RC隔离级别下,session A只有行锁,不会锁到session B,只会改到session A,binlog为row格式,同步A、B的数据到从库。如果是statement格式则会有问题。

总结

在这篇文章中,主要介绍了幻读的问题,知道了 InnoDB 为了在 RR 级别上解决该问题,引入了间隙锁。并知道了间隙锁会降低并发率,增加死锁情况的发生。还了解到 next-key lock 其实就是行锁(Record Lock)和间隙锁的合集。

在业务不需要 RR 支持下,如果想提高并发率,可以将隔离级别设置成 RC 并将 binlog 格式设置成 row.

 

幻读在 InnoDB 中是被如何解决的?(转)

上一篇:在Kubernetes上部署应用时我们常忽略的几件事


下一篇:关于NodeJS工作原理的五个误解