MySQL学习笔记-幻读有什么问题？

1.表结构

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;

Tips：下面介绍的操作内容都是 可重复读 隔离级别。

2.插入实验数据

insert into t values(0,0,0),(5,5,5),(10,10,10),(15,15,15),(20,20,20),(25,25,25);

如下图所示：

3.什么是幻读？

Tips：其中 for update 表示在查询的时候加上一个写锁，并且是当前读，两阶段锁协议 规则是在 InnoDB 事务中，行锁是在需要的时候才加上的，要等到事务结束时才释放。

• 幻读：按照两阶段锁协议的逻辑，在上述例子中，在 时刻1 的时候，SESSION A 查出来的满足条件的结果为 (5,5,5)， 若此时 SESSION A 中事务只对 d=5 的行加上了行锁，那么在 时刻2 时刻 SESSION B 对 id=0 的行的修改，会使得 SESSION A 在 时刻3 查出来的满足条件的结果为 (0,0,5)、(5,5,5)，然后在 时刻4 的时候，新增的行是没有行锁的，所以 SESSION A 在 时刻5 查出来满足条件的结果为 (0,0,5)、(1,1,5)、(5,5,5)，像 SESSION A 这样的在同一个事务中前后两次查询同一个范围的时候，后一次查询看到了前一次查询没有看到的行被称为幻读
  

4.幻读有什么问题？

       假设 SESSION A 中的事务只在 id=5 这一行加行锁，在时刻1 的时候，SESSION A 中的 update 修改语句是在 commit 之后才提交的，在 时刻5 之后提交之后数据是 (5,5,100)，在时刻2 的时候，SESSION B 中的 update 修改语句会把 id=0 这行数据修改为 (0,5,5)，在 时刻4 的时候，表中会增加一条数据 (1,5,5)，综合上面事务提交情况，binlog 记录的内容逻辑如下：

#时刻2 SESSION B
update t set d=5 where id=0; /*(0,0,5)*/
update t set c=5 where id=0; /*(0,5,5)*/
#时刻4 SESSION C
insert into t values(1,1,5); /*(1,1,5)*/
#时刻5 SESSION A commit 后
update t set c=5 where id=1; /*(1,5,5)*/
update t set d=100 where d=5;/*所有d=5的行，d改成100*/

       上面的 binlog 日志逻辑，如果是从库用来复制，或者备课用来恢复，得到的数据逻辑就会和原主库主库不一致，如果只在加行锁的情况下，即使把所有的记录都加上锁，还是阻止不了新插入的记录。

5.间隙锁(Gap Lock)是如何解决幻读问题的

       按照上述研究的内容，行锁只能锁住已有的行，但阻止不了新增的行数据，要更新的是记录之间的间隙,为了解决幻读问题，InnoDB 引入了间隙锁 (Gap 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]，其中 InnoDB 给每个索引加了一个不存在的最大值 supremum，具体现象如下：

• 表初始化数据如下：
  
  
• SESSION A 执行和现象如下：
  
  
• SESSION B 执行和现象如下：
  
  
• SESSION C 执行和现象如下：
  

Tips：从上述现象可以看出，InnoDB 通过行锁和间隙锁解决幻读问题。

6.next-key lock在什么情况下会造成死锁

上述情况，在时刻1的时候，由于在 SESSION A 中 id=9 这一行不存在，会加入 (5,10) 的间隙锁，此时事务还没提交，在时刻2的时候，在 SESSION B 中 id=9 这一行不存在，也会加入 (5,10) 的间隙锁，并且间隙锁之间不互斥，此时 SESSION B 插入的新行数据会被 SESSION A 的间隙锁阻塞，在 时刻4 的时候，SESSION A 插入的新行数据会被 SESSION B 的间隙锁阻塞，因此两个 SESSION 进入了互相等待对方锁释放，就造成了死锁，具体现象如下：

• SESSION A 执行和现象如下：
  
  
• SESSION B 执行和现象如下：
  
  
• SESSION B 执行和现象如下：
  
  
• SESSION A 执行和现象如下：
  

Tips：间隙锁是在可重复读隔离级别下才会生效的，如果把隔离级别设置为读提交的话，就没有间隙锁了。