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

技术分享 8个月前 (07-23) 0 999+

关注

首先给出要用到的数据：

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上没有索引，该语句会做全表扫描，那么其他被扫描到但不符合条件的记录是否会被加锁呢？

本文接下来没有特殊说明的，都是设定在可重复读隔离级别。

幻读是什么？

先看如果只在id=5的行加锁会怎么样。

假设有这样一个场景：

分析session A里的三次执行：

• Q1只返回id=5的行；

• 在T2时刻，session B把id=0的d值改成了5，因此T3时刻Q2能返回两行；

• 在T4时刻，session C插入一行，因此Q3时刻查出来3行。

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

这里对幻读做说明：

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

• session B的修改结果，被session A后的select语句用当前读看到，不能称为幻读，幻读专指新插入的行。

这三个查询都是加了for update，因此都是当前读，要读到所有已经提交的记录的最新值。

从事务可见性规则分析的话，这三条SQL语句的返回结果都没有问题。但是由于造成了幻读，是有其他问题的。

幻读有什么问题？

首先是语义上的问题。session A在T1时刻的语句实际上想要声明，要把所有d=5的行锁住，不允许别的事务进行读写操作。

如果现在看感觉不明显，看看如下情况：

在session B中，先将id=0的行也设置了d=5，之后设置c=5。由于在T1时刻，session A只是给id=5的行加了行锁，并没有给id=0的行加锁，因此session B是可以执行这两条update语句的，这就破坏了session A在T1时刻的加锁声明。

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

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

比如如下的情况：

分析数据库里的变化：

• 经过T1时刻，id=5这一行变为(5,5,100)，这个结果在T6正式提交；

• 经过T2时刻，id=0这一行变为(0,5,5)；

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

• 其他行与这个执行序列无关，保持不变。

而binlog里的变化：

• T2时刻，session B事务提交，写入了两条语句；

• T4时刻，session C事务提交，写入了两条语句；

• 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)。

即id=0,id=1两行发生了数据不一致。这是假设select * from t where d=5 for update只给id=5的行加锁导致的。

所以我们认为上面的设定不合理，假设改为“扫描过程中碰到的行都加上写锁”：