MySQL如何解决幻读

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

什么是幻读

在可重复读的隔离级别下，我们如果只对id=5（也就是d=5由于d上没有索引，所以会走主键索引树）加行锁，我们可以分析以下SessionA会出现什么情况：

1. Q1查询只会返回id=5这一行，也就是(5,5,5)
2. T2时刻Session B把id为0这一行的d值改成5，因此Q2查询查出来的是id=0和id=5的这两行(0,0,5)，(5,5,5)
3. T4时刻，Session C插入一行(1,1,5)，因此Q3查询查出来的是id=0、1、5的这三行(0,0,5)，(1,1,5)，(5,5,5)

注意：上述Session A中查询都是当前读，关于当前读可以见MySQL MVCC(多版本控制)这篇文章。

Q3读到id=1这一行的现象成为幻读。幻读是指一个事务在前后两次查询同一个范围的数据的时候，后一次查询查到了第一次查询没有查到的行。

在可重复隔离级别下，普通的查询时快照读，是无法看到别的事务插入的数据的，只有当前读才会出现幻读。

幻读有什么问题？

• 语义上带来了破坏。

Session A在T1时刻就声明我要把所有d=5的行锁住，不允许别的事务进行读写操作，但是实际上别的事务可以破坏这个声明。

• 数据一致性上会有问题。

1. 经过T1时刻，id=5这一行变成(5,5,100)，当然这个结果最终是在T6时刻进行提交
2. 经过T2时刻，id=0这一行变成了(0,5,5)
3. 经过T4时刻，表里面新生成了一行(1,5,5)

数据似乎很正常没有什么问题，但是如果你去分析binlog就会发现有问题了：

1. T2时刻，Session B事务提交，写入两条语句；
2. T4时刻，Session C事务提交，写入两条语句；
3. T6时刻，Session A事务提交，写入update t set d=100 where d=5这条语句

上述形成的binlog如下：

-- binlog的模式是statement;
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这两行就出现了数据不一致。

如何解决幻读？

产生幻读的原因就是行锁只能锁住行，插入动作更新的是记录之间的间隙。因此为了解决幻读问题，InnoDB引入了间隙锁。

什么是间隙锁？

在文章开始的时候我们插入了6条数据，这就会产生7个间隙，如下：

• (-∞, 0)
• (0, 5)
• (5, 10)
• (10, 15)
• (15, 20)
• (20, 25)
• (25, +∞)

当我们在执行select * from t where d=5 for update的时候，除了给数据库已有的行加行锁以外，还会对7个间隙加锁，这样确保了没有拿到锁的事务无法插入新的记录。

间隙锁之间没有冲突，跟间隙锁冲突的是往这个间隙中插入一个记录的操作。

什么是next-key lock？

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

select * from t where d=5 for update在执行的时候将形成7个next-key lock：

• (-∞, 0]
• (0, 5]
• (5, 10]
• (10, 15]
• (15, 20]
• (20, 25]
• (25, +∞]

其中+∞在这里是InnoDB给每个索引加了一个不存在的最大值。

间隙锁导致死锁？

Session B的insert会被阻塞，Session A在执行insert的时候会检测到死锁，如下图：

1. Session A执行select for update，由于id=8这一行并不存在，因此会加上间隙锁(5,10)
2. Session Bz执行select for update同样也会加上间隙锁，间隙锁之间并不冲突，因此可以执行成功
3. Session B尝试插入(9,9,9)，被Session A的间隙锁阻塞，只好进入等待
4. Session A尝试插入(9,9,9)，被Session B的间隙锁阻塞

此时两个Session形成死锁等待，InnoDB的死锁检测发现死锁关系，让Session A的insert 语句报错返回。

间隙锁是在可重复读隔离级别下才会生效，如果将隔离级别设置为读提交，就不会有间隙锁了，但是同时需要解决数据和日志不一致的问题（需要把binlog格式设置为ROW）。