MySQL教程(四)---MySQL 幻读与 InnoDB 间隙锁（Gap Lock）
本文对 MySQL 事务隔离级别及其常见问题进行了分析，同时记录了 InnoDB 是如何通过间隙锁来解决幻读的，最后还分析了为什么大部分业务事务隔离级别会使用读已提交级别。
1. 常见问题
在不考虑事务隔离级别的情况下，DB 操作可能出现以下几个问题：
- 1）脏读：事务 A 读取了事务 B 更新的数据，然后 事务B 进行回滚操作，那么 事务 A 读取到的数据就是脏数据。
- 指一个事务读取到了另外事务中未提交的数据。
- 2）不可重复读：事务 A 多次读取同一数据，事务 B 在事务 A 读取的过程中，对数据作了更新并提交，导致事务 A 多次读取同一数据时，结果不一致。
- 指一个事务读取到了事务中提交的 update 的数据。
- 3）幻读：系统管理员 A 将数据库中所有学生的成绩从具体分数改为ABCDE等级，但是系统管理员B就在这个时候新增了一条具体分数的记录，当系统管理员A改结束后发现还有一条记录没有改过来，就好像发生了幻觉一样，这就叫幻读。
- 指一个事务读取到了事务中提交的 insert 的数据。
不可重复读和幻读最大的区别，一者是对已存在的行进行操作导致，一者是对不存在的行进行操作导致。
2. 如何解决
ISO 和 ANIS SQL 提供了4 种事务隔离级别的标准。
- read-uncommitted
- read-committed
- repeatable-read
- serializable
为了解决这些问题，InnoDB 存储引擎同样提供了 4 种事务隔离级别。
| 事务隔离级别 | 脏读 | 不可重复读 | 幻读 |
|---|---|---|---|
| 读未提交（read-uncommitted） | 是 | 是 | 是 |
| 读已提交（read-committed） | 否 | 是 | 是 |
| 可重复读（repeatable-read） | 否 | 否 | 对InnoDB否 |
| 串行化（serializable） | 否 | 否 | 否 |
1. 脏读
将事务隔离级别提升到读已提交（read-committed）即可限制，禁止其他事务访问当前事务未提交的数据，这样就不会出现脏读的情况。
2. 不可重复读
将事务隔离级别提升到可重复读（repeatable-read）即可。
该事务隔离级别下，每次开启事务都会新建一个快照，在当前事务中的多次查询都是基于此快照进行的，不会查询到其他事务提交的数据，所以也不会出现 不可重复读的问题。
3. 幻读
这个就比较复杂了，只有 InnoDB 存储引擎下把事务隔离级别调到可重复读（repeatable-read）才能限制该问题。
幻读与不可重复读的区别：不可重复读是对当前已存在的数据进行更新，导致后续的查询与之前的查询结果不一致，而幻读是新增了满足当前查询条件的行，导致前后查询结果不一致。
解决不可重复读只需要每次将满足条件的行，加锁即可。
当是幻读则不能通过该方式解决，因为当前行都不存在怎么加锁。
于是 MySQL 中为了解决这个问题，新增了一种锁：间隙锁（Gap Lock）。
3. 间隙锁（Gap Lock）
间隙锁是 InnoDB 行锁中的一种。
产生幻读的原因是，行锁只能锁住行，但是新插入记录这个动作，要更新的是记录之间的间隙。因此，为了解决幻读问题，mysql InnoDB 只好引入新的锁，也就是间隙锁 (GapLock)。间隙锁，锁的就是两个值之间的空隙，因此间隙锁只与往间隙里写入记录这个操作冲突 。值得注意的是，间隙锁只在隔离级别是 可重复读隔离级别下才会生效。
- 1）行锁(Record Lock)：锁直接加在索引记录上面。
- 2）间隙锁(Gap Lock)：锁加在不存在的空闲空间，可以是两个索引记录之间，也可能是第一个索引记录之前或者最后一个索引之后的空间，两边都是开区间。
- 3）Next-Key Lock：行锁与间隙锁组合起来用就叫做 Next-Key Lock，是一个前开后闭区间。
默认情况下，InnoDB工作在 重复读（repeatable-read）的隔离情况下，并且以 Next-Key Lock 的方式对数据进行加锁，这样就可以有效地防止幻读的发生。
Next-key Lock 是行锁与间隙锁的组合，这样，当 InnoDB 扫描索引记录的时候，会首先对选中的索引记录加上行锁(Record Lock)，再对索引记录两边的间隙加上间隙锁(Gap Lock)。如果一个间隙被事务 A 加了锁，其他事务是不能在这个间隙插入记录的。
4. 加锁规则
1. 概述
- 1）原则 1：加锁的基本单位是 next-key lock。希望你还记得，next-key lock 是前开后闭区间。
- 2）原则 2：查找过程中访问到的对象才会加锁。
- 3）优化 1：索引上的等值查询，给唯一索引加锁的时候，next-key lock 退化为行锁。
- 4）优化 2：索引上的等值查询，向右遍历时且最后一个值不满足等值条件的时候，next-key lock 退化为间隙锁。
- 5）一个 bug：唯一索引上的范围查询会访问到不满足条件的第一个值为止。
MySQL 后面的版本可能会改变加锁策略，所以这个规则只限于截止到现在的最新版本，即 5.x 系列 <=5.7.24，8.0 系列 <=8.0.13。
建表语句和初始化如下：
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);
2. 等值查询间隙锁
| sessionA | sessionB | sessionC |
|---|---|---|
| begin; | ||
| update t set d = d+1 where id = 7; | ||
| insert into t values(8,8,8);(blocked) | ||
| update t set d = d+1 where id =10;(Query OK) |
由于表 t 中没有 id=7 的记录，所以用我们上面提到的加锁规则判断一下的话：
- 1）根据原则 1，加锁单位是 next-key lock，session A 加锁范围就是 (5,10]；
- 2）同时根据优化 2，这是一个等值查询 (id=7)，而 id=10 不满足查询条件，next-key lock 退化成间隙锁，因此最终加锁的范围是 (5,10)。
所以，session B 要往这个间隙里面插入 id=8 的记录会被锁住，但是 session C 修改 id=10 这行是可以的
3. 非唯一索引等值锁
| sessionA | sessionB | sessionC |
|---|---|---|
| begin; | ||
| select id from t where c =5 lock in share mode; | ||
| update t set d = d+1 where id =5; | ||
| insert into t values(7,7,7);(blocked) |
这里 session A 要给索引 c 上 c=5 的这一行加上读锁。
- 1）根据原则 1，加锁单位是 next-key lock，因此会给 (0,5]加上 next-key lock。要注意 c 是普通索引，因此仅访问 c=5 这一条记录是不能马上停下来的，需要向右遍历，查到 c=10 才放弃。
- 2）根据原则 2，访问到的都要加锁，因此要给 (5,10]加 next-key lock。
- 3）但是同时这个符合优化 2：等值判断，向右遍历，最后一个值不满足 c=5 这个等值条件，因此退化成间隙锁 (5,10)。
- 4）根据原则 2 ，只有访问到的对象才会加锁，这个查询使用覆盖索引，并不需要访问主键索引，所以主键索引上没有加任何锁，这就是为什么 session B 的 update 语句可以执行完成。
但 session C 要插入一个 (7,7,7) 的记录，就会被 session A 的间隙锁 (5,10) 锁住。
5. 间隙锁带来的问题
1. 死锁 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;
insert into t values(0,0,0),(5,5,5),
(10,10,10),(15,15,15),(20,20,20),(25,25,25);
间隙锁的引入可能会导致死锁。前面提到了间隙锁只与往间隙里写入记录这个操作冲突。同一个间隙多次加锁是不会冲突的，于是问题来了。
| SessionA | SessionB |
|---|---|
| begin; | |
| select * from t where id = 9 for update; | |
| begin; | |
| select * from t where id = 9 for update; | |
| insert into values(9,9,9);(blocked) | |
| insert into values(9,9,9);(ERROR Deadlock found) |
分析如下：
- 1）session A 执行 select … for update 语句，由于 id=9 这一行并不存在，因此会加上间隙锁 (5,10);
- 2）session B 执行 select … for update 语句，同样会加上间隙锁 (5,10)，间隙锁之间不会冲突，因此这个语句可以执行成功；
- 3）session B 试图插入一行 (9,9,9)，被 session A 的间隙锁挡住了，只好进入等待；
- 4）session A 试图插入一行 (9,9,9)，被 session B 的间隙锁挡住了。
至此，两个 session 进入互相等待状态，形成死锁。当然，InnoDB 的死锁检测马上就发现了这对死锁关系，让 session A 的 insert 语句报错返回了。
2. 死锁 2
| sessionA | sessionB |
|---|---|
| begin； | |
| select id from t where c= 10 lock in share mode; | |
| update t set d= d+1 where c = 10;(blocked) | |
| insert into t values(8,8,8); | |
| ERROR Deadlock found when trying to get lock;try restaring transaction. |
现在，我们按时间顺序来分析一下为什么是这样的结果。
- 1）session A 启动事务后执行查询语句加 lock in share mode，在索引 c 上加了 next-key lock(5,10] 和间隙锁 (10,15)；
- 2）session B 的 update 语句也要在索引 c 上加 next-key lock(5,10] ，进入锁等待；然后 session A 要再插入 (8,8,8) 这一行，被 session B 的间隙锁锁住。由于出现了死锁，InnoDB 让 session B 回滚。
你可能会问，session B 的 next-key lock 不是还没申请成功吗？其实是这样的，session B 的“加 next-key lock(5,10] ”操作，实际上分成了两步：
- 1）先是加 (5,10) 的间隙锁，加锁成功；
- 2）然后加 c=10 的行锁，这时候才被锁住的。
也就是说，我们在分析加锁规则的时候可以用 next-key lock 来分析。但是要知道，具体执行的时候，是要分成间隙锁和行锁两段来执行的。
6. 小结
本文对 MySQL 事务隔离级别及其常见问题进行了分析，同时记录了 InnoDB 是如何解决幻读的。
最后一个问题，既然间隙锁能解决其他锁都解决不了的问题（幻读），那么为什么大部分业务还是使用 读提交事务隔离级别呢（只有在可重复读级别下间隙锁才生效）?
- 1）间隙锁的引入可能会出现死锁。
- 2）间隙锁的引入，可能会导致同样的语句锁住更大的范围，对并发度有一定影响。
- 3）在读提交隔离级别下，锁的范围更小，锁的时间更短。
- 因为读提交隔离级别下有一个优化：语句执行过程中加上的行锁，在语句执行完成后，就要把“不满足条件的行”上的行锁直接释放了，不需要等到事务提交
所以大部分业务都使用的读提交（Read Commit）级别，同时为了解决可能出现的数据和日志不一致问题，需要把 binlog 格式设置为 row。
7. 参考
- 《Mysql技术内幕InnoDB存储引擎》
- 极客专栏-<MySQL 实战45讲>