『MySQL 实战 45 讲』20 - 幻读是什么，幻读有什么问题？

幻读是什么，幻读有什么问题？

需求：创建一个小表

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

幻读是什么？

幻读指的是一个事务在前后两次查询同一个范围的时候，后一次查询看到了前一次查询没有看到的行
幻读说明：

在可重复读隔离级别下，普通的查询是快照读，是不会看到别的事务插入的数据的。因此，幻读在“当前读”下才会出现

幻读有什么问题？

语义上会被破坏
产生数据不一致性

幻读带来的问题分析

假设需要对 id=5 这一行加锁，其他行不加锁（这个是个假设场景）

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

其中因为当前读，session C 新插入的行被 session A 看到了，这就是幻读
问题一：语义被破坏

实际上，session A 在 T1 时刻，就要求锁住 d=5 的行。由于只锁住 id=5 这一行，T2 时刻 session B 是可以执行 2 条 update 语句，破坏了 Q1 的声明（session C 同理）

问题二：数据一致性问题（数据和日志在逻辑上的一致性）

执行逻辑：
- T1 时刻：id=5 这一行变成 (5,5,100)，当然这个结果最终是在 T6 时刻正式提交的
- T2 时刻，id=0 这一行变成 (0,5,5)
- T4 时刻，表里面多了一行 (1,5,5)
对应 binlog 里面的内容：

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 语句序列问题
- 不论是拿到备库去执行，还是以后用 binlog 来克隆一个库，这三行的结果，都变成了 (0,5,100)、(1,5,100) 和 (5,5,100)
- id=0 和 id=1 这两行，发生了数据不一致
- 引起该问题的是 for update 只给 d=5 这行加锁
解决 binlog 语句序列问题：
执行逻辑：
- session A 把所有的行都加了写锁，session B 在执行第一个 update 就会被锁住，只有 T6 时刻 session A 提交以后，session B 才能继续执行
- 新的 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 插入时，id=1 这一行还不存在，不存在也就加不上锁。也就是说，即使把所有的记录都加上锁，还是阻止不了新插入的记录

如何解决幻读？

为了解决幻读问题，InnoDB 只好引入新的锁，也就是间隙锁
实际上，开头的表 t，初始化插入了 6 个记录，这就产生了 7 个间隙

极客时间版权所有: https://time.geekbang.org/column/article/75173
3. 当执行 select * from t where d=5 for update 的时候，6 个记录加上了行锁，还同时加了 7 个间隙锁，这样就确保无法再插入新的记录
4. 注意：跟间隙锁存在冲突关系的，是“往这个间隙中插入一个记录”这个操作
在这里插入图片描述

这里 session B 并不会被堵住，因为表 t 里并没有 c=7 这个记录，因此 session A 加的是间隙锁 (5,10)

间隙锁和行锁合称 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-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;

一旦有并发，就有可能死锁
执行逻辑
- session A 执行 select … for update 语句，由于 id=9 这一行并不存在，因此会加上间隙锁 (5,10)
- session B 执行 select … for update 语句，同样会加上间隙锁 (5,10)，间隙锁之间不会冲突，因此这个语句可以执行成功
- session B 试图插入一行 (9,9,9)，被 session A 的间隙锁挡住了，只好进入等待
- session A 试图插入一行 (9,9,9)，被 session B 的间隙锁挡住了
间隙锁的引入，可能会导致同样的语句锁住更大的范围，这其实是影响了并发度的