innodb幻读问题怎么解决

发布时间：2022-05-12 14:43:24 作者：iii
来源：亿速云阅读：180

本篇内容主要讲解“innodb幻读问题怎么解决”，感兴趣的朋友不妨来看看。本文介绍的方法操作简单快捷，实用性强。下面就让小编来带大家学习“innodb幻读问题怎么解决”吧!

1.幻读

CREATE TABLE `test_20` ( `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 test_20 values(0,0,0),(5,5,5),(10,10,10),(15,15,15),(20,20,20),(25,25,25);

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

这个语句会命中 d=5 的这一行，对应的主键 id=5，因此在 select 语句执行完成后，id=5 这一行会加一个写锁，而且由于两阶段锁协议，这个写锁会在执行 commit 语句的时候释放。

如果只在 id=5 这一行加锁，而其他行的不加锁的话，会怎么样？

在READ COMMITTED级别下可执行
在REPEATABLE READ级别下，session B会阻塞

innodb幻读问题怎么解决

1）Q1 只返回 id=5 这一行；
2）在 T2 时刻，session B 把 id=0 这一行的 d 值改成了 5，因此 T3 时刻 Q2 查出来的是id=0 和 id=5 这两行；
3）在 T4 时刻，session C 又插入一行（1,1,5），因此 T5 时刻 Q3 查出来的是 id=0、id=1和 id=5 的这三行。

其中，Q3 读到 id=1 这一行的现象，被称为“幻读”。也就是说，幻读指的是一个事务在前后两次查询同一个范围的时候，后一次查询看到了前一次查询没有看到的行。

说明：

1）在可重复读隔离级别下，普通的查询是快照读，是不会看到别的事务插入的数据的。因此，幻读在“当前读”下才会出现。
2）上面 session B 的修改结果，被 session A 之后的 select 语句用“当前读”看到，不能称为幻读。幻读仅专指“新插入的行”。

2.幻读的问题（更新与新增）

2.1 语义上

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

2.2 数据一致性问题

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

innodb幻读问题怎么解决

候 binlog 里面的内容：

1） T2 时刻，session B 事务提交，写入了两条语句；
2）T4 时刻，session C 事务提交，写入了两条语句；
3）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)。

原因是：

这是我们假设“select * from t where d=5 for update 这条语句只给d=5 这一行，也就是 id=5 的这一行加锁”导致的。

进一步假设：

把扫描过程中碰到的行，也都加上写锁，再来看看执行效果

innodb幻读问题怎么解决

binlog 里面执行序列：

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)*/

这里解决了session B的问题，但是session C新增的问题并没有解决！

在 T3 时刻，我们给所有行加锁的时候，id=1 这一行还不存在，不存在也就加不上锁。也就是说，即使把所有的记录都加上锁，还是阻止不了新插入的记录。

3.InnoDB 怎么解决幻读的问题

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

间隙锁，锁的就是两个值之间的空隙。比如表 test_20，初始化插入了 6 个记录，这就产生了 7 个间隙。

当你执行 select * from t where d=5 for update 的时候，就不止是给数据库中已有的 6个记录加上了行锁，还同时加了 7 个间隙锁。这样就确保了无法再插入新的记录。

数据行是可以加上锁的实体，数据行之间的间隙，也是可以加上锁的实体。但是间隙锁跟我们之前碰到过的锁都不太一样。

跟行锁有冲突关系的是“另外一个行锁”。
跟间隙锁存在冲突关系的，是“往这个间隙中插入一个记录”这个操作。间隙锁之间都不存在冲突关系。
如下：这里 session B 并不会被堵住。因为表 test_20里并没有 c=7 这个记录，因此 session A 加的是间隙锁 (5,10)。而 session B 也是在这个间隙加的间隙锁。它们有共同的目标，即：保护这个间隙，不允许插入值。但，它们之间是不冲突的。
间隙锁和行锁合称 next-key lock，每个 next-key lock 是前开后闭区间。也就是说，表test_20初始化以后，如果用 select * from t for update 要把整个表所有记录锁起来，就形成了 7 个next-key lock，分别是 (-∞,0]、(0,5]、(5,10]、(10,15]、(15,20]、(20, 25]、(25,+suprenum]。
把间隙锁记为开区间，把 next-keylock 记为前开后闭区间。