mysql 数据库幻读的原因解决方案 mysql innodb 幻读

转载

网络智叶 2023-10-19 21:29:49

文章标签 mysql 数据库幻读的原因解决方案数据隔离级别数据一致性 文章分类 MySQL 数据库

在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 个空隙。

当再执行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 级别下幻读的例子：