MySQL深入17-幻读
# MySQL深入17-幻读
现有如下的表：
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;
进行如下事务：
begin;
select * from t where d=5 for update;
commit;
这个语句会命中d=5的这一行，对应的主键是id=5，因此在select语句执行完成后会对这行数据加写锁，而且由于两阶段锁协议，这个写锁是在执行commit语句的时候释放。
由于字段d上没有索引，查询语句会全表扫描，那么其他被扫描但是不满住条件的数据行会不会被加锁呢？
# 幻读是什么
现在假设只在id=5这一行加锁，其他行不加锁下有如下场景（注意这里只是假设）：
session A中执行了三次查询，且查询的SQL语句相同，而且使用了当前读，并且加上写锁。
当前读指的是select for update或者select in share mode，指的是在更新之前必须先查寻当前的值，因此叫当前读。
快照读指的是在语句执行之前或者在事务开始的时候会创建一个视图，后面的读都是基于这个视图的，不会再去查询最新的值。
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 的这三行。
可以看到在一定查询时间范围内，前后两次查询的结果不相同：
- 幻读是针对insert导致的数据不一致（Q3就是幻读），
- 不可重复读是针对 delete、update导致的数据不一致（Q2是不可重复读）。
# 幻读存在的问题
# 语义问题
session A在T1时刻的声明了：“d=5的数据行锁住，其他事务不能进行读写操作”。
由于session A只锁住了id = 5一行数据，session B在T2时刻把id = 0的数据进行修改，修改后id = 0的数据d为5，接着第二条语句对id = 0的数据又进行修改，但是此刻这个d为5数据应该是被锁住的，但是能够进行修改，就破环了加锁声明。
# 数据一致性问题
锁的设计是为了保证数据的一致性：
- 数据库内部状态在此刻的一致性。
- 数据和日志逻辑上的一致性。
现在有如下的场景：
- T1时刻后，id = 5 这一行数据变为（5，5，100），最终结果是在T6时刻进行提交。
- T2时刻后，id = 5 这一行数据变为 （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 这条语句。
// B事务
update t set d=5 where id=0; /*(0,0,5)*/
update t set c=5 where id=0; /*(0,5,5)*/
// C事务
insert into t values(1,1,5); /*(1,1,5)*/
update t set c=5 where id=1; /*(1,5,5)*/
// A事务
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 这两行，发生了数据不一致。
其实这个数据不一致的问题就是因为我们所假设的只对id=5这一行数据进行加锁。
现在我们把扫描过程中碰到的行，都加上写锁，再来分析上诉场景：
session A由于是全表扫描（字段d上没有索引），所以session B在执行第一个update语句的时候就被锁住了，需要等到T6时刻session A提交之后，session B才能继续执行。
下面我们来看一下binlog里面记录的：
// 事务C
insert into t values(1,1,5); /*(1,1,5)*/
update t set c=5 where id=1; /*(1,5,5)*/
// 事务A
update t set d=100 where d=5;/*所有d=5的行，d改成100*/
// 事务B
update t set d=5 where id=0; /*(0,0,5)*/
update t set c=5 where id=0; /*(0,5,5)*/
可以看到id=0这一行数据最终结果为（0，5，5），所以这一行数据的问题解决了。但是id = 1这一行数据，在数据库里面的结果是 （1，5，5），但是使用redo log最终执行出来结果为（1，5，100）。问题仍然没有解决，这是因为新插入的数据是没有加锁的。
# 如何解决幻读
产生幻读的原因是，行锁只能锁住行，但是新插入记录这个动作，要更新的是记录之间的“间隙”。因此，为了解决幻读问题，InnoDB 只好引入新的锁，也就是间隙锁 (Gap Lock)。
间隙锁： 锁的就是两个值之间的空隙。比如我们往表t中插入6个数据，就会产生7个间隙。
insert into t values(0,0,0),(5,5,5),(10,10,10),(15,15,15),(20,20,20),(25,25,25);
这样当我们执行select * from t where d = 5 for update的时候，就不止是给数据库中已经初始化的6条数据，还给这6条数据所在范围加上了7个间隙锁，这样就确保了无法再插入新的记录。
在一行行扫描的过程中，不仅将给行加上了行锁，还给行两边的空隙，也加上了间隙锁。
现在我们来对比一下行锁和间隙锁：
行锁分为读锁和写锁，两种类型锁的冲突关系如下：
| 读锁 | 写锁 | |
|---|---|---|
| 读锁 | 兼容 | 冲突 |
| 写锁 | 冲突 | 冲突 |
也就是说跟行锁冲突的是“另一个行锁”。
对于间隙锁而言，跟间隙锁存在冲突关系的是“往这个间隙中插入一个记录”这个操作，间隙锁之间是不存在冲突关系。
这里 session B 并不会被堵住。因为表 t 里并没有 c=7 这个记录，因此 session A 加的是间隙锁 (5,10)。而 session B 也是在这个间隙加的间隙锁。它们有共同的目标，即：保护这个间隙，不允许插入值。但，它们之间是不冲突的。
间隙锁和行锁合称 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]。
supremum是InnoDB给每个索引加了一个不存在的最大值。
但是间隙锁和next-key lock的引入虽然解决了幻读的问题，但同时也带来一些“困难”。
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 的间隙锁挡住了。
至此，两个 session 进入互相等待状态，形成死锁。当然，InnoDB 的死锁检测马上就发现了这对死锁关系，让 session A 的 insert 语句报错返回了。
由于间隙锁的引入，可能会导致同样的语句锁住更大的范围，这其实是影响了并发度的。