MySQL-20：幻读如何解决？

先建个表，看下面sql：

CREATE TABLE `r` (
  `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 r values(0,0,0),(5,5,5),
(10,10,10),(15,15,15),(20,20,20),(25,25,25);

问题：在InnoDB引擎默认的隔离级别(可重复读下)，下面事务的锁什么时候加，什么时候释放，加多少行的锁？

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

20.1 幻读是什么？

幻读： 事务A在不同的时间对同一数据集查询的结果不一致。

说明： ①在可重复读隔离级别下，普通的查询是快照读，幻读只出现在当前读下。②幻读偏向于新插入的数据。

当前读： 能读到所有已经提交的记录的最新值。

看下面示意图，我们能明显看到Session A出现幻读。

20.2 幻读的问题

下面我们假设仅在d=5行加锁：

看下面示意图：

问题一： select * from t where d = 5 for update的语义为锁住所有d=5的行，不允许所有事务进行读写操作。T2，T4时刻就破坏了该语义。T1在一开始只在id=5这一行加锁，并没有在id=0,1行加锁。

问题二： 数据一致性问题，数据库和binlog恢复出来的数据不一致。

# 上面示意图写入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*/

数据库： (0,5,5)，(1,5,5)，(5,5,100)；binlog恢复：(0,5,100)，(1,5,5)，(5,5,100)

现在我们假设把所有行都加上锁：

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)*/

我们得到数据库：(0,5,5)，(1,5,5)，(5,5,100)；binlog恢复：(0,5,5)，(1,5,100)，(5,5,100)

在所有行上加写锁无法控制到新数据 insert ，因为它本来就不存在，下面看InnoDB如何解决它。

20.3 如何解决幻读

产生幻读： 无法控制新数据的插入，也就是记录之间的“间隙”。所以InnoDB引入了新的锁→间隙锁(Gap Lock)。

间隙锁，锁的就是两条数据之间的间隙，例如开头的表格，插入6个数据，产生7个间隙。

也就是说当你去 执行select * from t where d=5 for update的时候，不仅会给所有行加写锁，还同时加上7个间隙锁。间隙锁之间不存在冲突关系，它们的共同目的都是保护这个间隙，不插入新值。

间隙锁和行锁合称next-key lock，每个next-key lock是前开后闭区间。

但间隙锁和next-key lock的引入帮我们解决幻读问题，也带来了其他问题。

这里说明个情况：select * from t where d=9 for update，但没有数据的时候，就只会加间隙锁，不会加行锁，因为没有数据行。

模拟下图情景会导致死锁：

间隙锁是在可重复读隔离级别下才会生效的 。