【MySQL】幻读 案例分析

目录

假设1：只在 id=5 这一行加锁，其他行不加锁？

幻读的定义

幻读的场景

假设1 产生的问题：语义被破坏

假设1 产生的问题：数据一致性

结论： 假设1不成立

假设2：扫描过程中每一行都加上写锁？

幻读产生的原因？

如何解决幻读？

间隙锁

next-key lock

间隙锁导致的死锁

---

首先创建表t，字段c创建索引，插入以下测试数据。

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

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

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

首先，由于字段 d 上没有索引，因此这条查询语句会做全表扫描。

问题来了：其他被扫描到，但是不满足条件（d=5）的记录，会不会被加锁呢？

假设1：只在 id=5 这一行加锁，其他行不加锁？

这三条 SQL 语句，分别会返回什么结果？

1. Q1 只返回 id=5 这一行；
2. Q2 查出来的是 id=0 和 id=5 这两行；
3. Q3 查出来的是 id=0、id=1 和 id=5 的这三行。

其中，Q3 读到 id=1 这一行的现象，被称为“幻读”。

幻读的定义

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

幻读的场景

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

2. 上面 session B 的修改结果，被 session A 之后的 select 语句用“当前读”看到，不能称为幻读。幻读仅专指“新插入的行”。

假设1 产生的问题：语义被破坏

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

如果现在这样看感觉还不明显的话，我再往 session B 和 session C 里面分别加一条 SQL 语句。

由于在 T1 时刻，session A 还只是给 id=5 这一行加了行锁， 并没有给 id=0 这行加上锁。因此，session B 在 T2 时刻，是可以执行这两条 update 语句的。这样，就破坏了 session A 里 Q1 语句要锁住所有 d=5 的行的加锁声明。

session C 也是一样的道理，对 id=1 这一行的修改，也是破坏了 Q1 的加锁声明。

假设1 产生的问题：数据一致性

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

给 session A 在 T1 时刻再加一个更新语句，即：update t set d=100 where d=5。

分析一下执行完成后，数据库里的结果：

经过 T1 时刻，id=5 这一行变成 (5,5,100)，当然这个结果最终是在 T6 时刻正式提交的 ;

经过 T2 时刻，id=0 这一行变成 (0,5,5);

经过 T4 时刻，表里面多了一行 (1,5,5);

我们再来看看这时候 binlog 里面的内容：

// T2 时刻，session B 事务提交，写入了两条语句；
update t set d=5 where id=0; /*(0,0,5)*/
update t set c=5 where id=0; /*(0,5,5)*/// T4 时刻，session C 事务提交，写入了两条语句；
insert into t values(1,1,5); /*(1,1,5)*/
update t set c=5 where id=1; /*(1,5,5)*/// T6 时刻，session A 事务提交，写入了 update t set d=100 where d=5 这条语句。
update t set d=100 where d=5;/*所有d=5的行，d改成100*/
// 三行的结果，都变成了 (0,5,100)、(1,5,100) 和 (5,5,100)

这个binlog语句序列，执行后这三行的结果，都变成了 (0,5,100)、(1,5,100) 和 (5,5,100)。也就是说，id=0 和 id=1 这两行，d被改成了100，发生了数据不一致。

结论： 假设1不成立

所以，假设“select * from t where d=5 for update 这条语句只给 d=5 这一行，也就是 id=5 的这一行加锁”的设定不合理。

假设2：扫描过程中每一行都加上写锁？

由于 session A 把所有的行都加了写锁，所以 session B 在执行第一个 update 语句的时候就被锁住了。需要等到 T6 时刻 session A 提交以后，session B 才能继续执行。

这样对于session B  id=0 这一行，在数据库里的最终结果还是 (0,5,5)没问题。

在 binlog 里面，执行序列如下：

id=1 这一行，在数据库里面的结果是 (1,5,5)，而根据 binlog 的执行结果是 (1,5,100)

/*(0,0,0) (5,5,5) (10,10,10) (15,15,15) (20,20,20) (25,25,25)*/// session C 
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 (1,5,100) (5,5,100)*/// session B 没问题
update t set d=5 where id=0; /*(0,0,5)*/
update t set c=5 where id=0; /*(0,5,5)*/

幻读产生的原因？

可以看到，新插入的 id=1 这一行，在数据库里面的结果是 (1,5,5)，而根据 binlog 的执行结果是 (1,5,100)。

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

如何解决幻读？

为了解决幻读问题，InnoDB 只好引入新的锁，也就是间隙锁 (Gap Lock)。

间隙锁

锁的就是两个值之间的空隙。比如文章开头的表 t，初始化插入了 6 个记录，这就产生了 7 个间隙。当你执行 select * from t where d=5 for update 的时候，就不止是给数据库中已有的 6 个记录加上了行锁，还同时加了 7 个间隙锁。这样就确保了无法再插入新的记录。

next-key lock

间隙锁和行锁合称 next-key lock，每个 next-key lock 是前开后闭区间。也就是说，我们的表 t 初始化以后，如果用 select * from t for update 要把整个表所有记录锁起来，就形成了 7 个 next-key lock，分别是 (-∞,0]、(0,5]、(5,10]、(10,15]、(15,20]、(20, 25]、(25, +supremum]。

间隙锁导致的死锁

间隙锁的引入，可能会导致同样的语句锁住更大的范围，这其实是影响了并发度的。

参考：20 | 幻读是什么，幻读有什么问题？-MySQL实战45讲-极客时间