《MySQL实战45讲》——学习笔记20 “幻读、全表扫描的加锁方式、间隙锁、next-key lock“

淡淡的烟草味﹌ 2023-09-23 17:34 62阅读 0赞

本篇介绍MySQL在可重复度RR隔离级别下，引入的一种锁机制：间隙锁 (Gap Lock)；间隙锁与事务相关的表锁、行锁不同，它锁的是“往这个间隙中插入一个记录”这个操作，除此之外间隙锁之间都不存在冲突关系（因而有可能发生死锁）；

间隙锁和行锁合称 next-key lock，每个next-key lock是**前开后闭区间**；如果使用 select \* from t for update 这种全表扫描的语句（不走二级索引），要把整个表的所有记录以及所有的间隙给锁起来，代价很大；因此建议**更新的时候能尽量走主键或者二级索引**；

本篇仅介绍间隙锁和next-key lock的概念以及引入的原因，但是并没有说明加锁规则；加锁规则参考下一篇文章专门介绍；

#### 可重复读隔离级别下"当前读"怎么加锁？ ####

为方便讨论加锁规则，这里用一个简单的表结构说明，建表和初始化语句如下：

# 3个字段的简单表：主键id、索引列c、非索引列d
    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;
    
    # 表中初始化6条数据
    insert into t values
    (0,0,0),
    (5,5,5),
    (10,10,10),
    (15,15,15),
    (20,20,20),
    (25,25,25);

（1）对指定主键的列加读锁；SQL语句如下：

select * from t where id=1 lock in share mode;

由于 id 上有索引，所以可以直接定位到 id=1 这一行，因此读锁也是只加在了这一行上；但如果是下面的 SQL 语句呢？

（2）对指定的非索引列加读锁；SQL语句如下：

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

比较好理解的是，这个语句会命中d=5的这一行记录(5,5,5)，对应的主键id=5，因此在select语句执行完成后，id=5这一行会加一个写锁，而且由于两阶段锁协议，这个写锁会在执行commit语句的时候释放；

**由于字段d上没有索引，因此这条查询语句会做全表扫描**；那么，其他**被扫描到的**，但是不满足条件d=5行记录上，会不会被加锁呢？(本文讨论的场景都是使用InnoDB的默认事务隔离级别，即可重复读)；

#### 假设1：假设只在 id=5 这一行加锁，而其他被扫描的行的不加锁 ####

**基于上面的假设**（这里是假设，不是真实情况！），尝试分析下下面的场景；

![e665eaf99ecd420f8d7e73b750d9f41e.png][]

现在分析下Q1/Q2/Q3这3次"当前查"分别会返回什么结果；

（1）Q1只返回id=5这一行；  
（2）在T2时刻，sessionB把id=0这一行的d值改成了5，因此T3时刻Q2查出来的是id=0和id=5这两行；  
（3）在T4时刻，sessionC又插入一行（1,1,5），因此T5时刻Q3查出来的是id=0、id=1和id=5的这三行；

其中，Q3读到id=1这一行的现象，被称为“幻读”；

> **幻读指的是一个事务在前后两次查询同一个范围的时候，后一次查询看到了前一次查询没有看到的"多出来的"行**；
> 
> 在可重复读隔离级别下，普通的查询是快照读（一致性读），是不会看到别的事务插入的数据的；因此，**幻读在“当前读”下才会出现**；

上面sessionB的修改结果，被sessionA之后的select语句用“当前读”看到，不能称为幻读；**幻读仅专指“新插入的行”**；

Q1/Q2/Q3这3个查询都是加了for update，都是当前读；而当前读的规则，就是要能读到所有已经提交的记录的最新值；并且，sessionB和sessionC的两条语句，执行后就会提交，所以Q2和Q3应该能看到sessionA和sessionB这两个事务的操作效果，这跟事务的可见性规则并不矛盾；

因此结论就是——**可能会导致幻读**！

#### 幻读有什么问题？ ####

**1. 破坏了加锁的语义**

首先是语义上，sessionA在T1时刻就声明了，“我要把所有d=5的行锁住，不准别的事务进行读写操作”；而实际上，这个语义被破坏了；sessionB和sessionC通过修改原数据行或新插入数据行，导致产生了新的满足d=5的行，这些新的数据行都没有被"锁住"；

**2. 破坏了数据和日志在逻辑上的一致性**

**锁的设计是为了保证数据的一致性**；而这个一致性，不止是数据库内部**数据状态在此刻的一致性**，还包含了**数据和日志在逻辑上的一致性**；

为了说明这个问题，给sessionA在T1时刻再加一个更新语句，即：update t set d=100 where d=5；再来看下整个执行流程；

![8f07a22121a84ff6bfbd355c8d4b2cc7.png][]

**SQL执行的过程**：（整个过程不存在锁等待）

（1）经过T1时刻，id=5这一行变成(5,5,100)，当然这个结果最终是在T6时刻正式提交的；  
（2）经过T2时刻，id=0这一行变成(0,5,5)；  
（3）经过T4时刻，表里面多了一行(1,5,5)；

总结下来，T6时刻后，id=0/id=1/id=5这3行的结果，变成了(0,5,5) / (1,,5,5) / (5,5,100)；

binlog是在commit时生成（两阶段提交），**日志逻辑如下**：

（1）T2时刻，sessionB事务提交，写入了两条语句，id=0这一行变成(0,5,5)；  
（2）T4时刻，sessionC事务提交，写入了两条语句，表里面多了一行(1,5,5)；  
（3）T6时刻，sessionA事务提交，写入了update t set d=100 where d=5这条语句；满足d=5的记录的d的值都被更新成100；

总结下来，T6时刻后，id=0/id=1/id=5这3行的结果，变成了(0,5,100) / (1,5,100) / (5,5,100)；

也就是说——id=0和id=1这两行，**发生了数据不一致****！**binlog恢复出来的结果和实际表里的数据不一致，这个问题很严重，是不行的；

#### 假设2：把扫描过程中碰到的行也都加上写锁 ####

经过上面的分析可知，假设1 "select \* from t where d=5 for update" 这条语句只给d=5这一行，也就是id=5的这一行加锁”是不行的，会导致幻读，破坏对d=5的记录加锁的语义，并且产生的binlog逻辑与实际的数据不一致；

现在假设把扫描过程中碰到的行也都加上写锁，来看下问题是否解决；

![606bb8dbb01245e89c5d8ec711fcff5a.png][]

分析：由于sessionA把所有的行都加了写锁，所以sessionB在执行第一个update语句的时候就被锁住了；需要等到T6时刻sessionA提交以后，sessionB才能继续执行；所以，id=0这一行的数据不一致的问题解决了；

但是，id=1这一行，在数据库里面的结果是(1,5,5)，而根据binlog的执行结果是(1,5,100)，也就是说幻读的问题还是没有解决；

原因很简单，在T1时刻，我们给所有行加锁的时候，id=1这一行还不存在，不存在也就加不上锁；也就是说，**即使把当前所有的记录都加上锁，还是阻止不了未来新插入的记录被"当前读"读到（幻读）**，这也是为什么“幻读”会被单独拿出来解决的原因；

#### 如何解决幻读？ ####

**产生幻读的原因是，行锁只能锁住当前的行，但是未来新插入记录锁不了**；为了解决幻读问题，InnoDB只好引入新的锁，也就是**间隙锁**(**Gap Lock**)；

由于未来新插入的数据只能插到当前表数据主键id之间的空隙中（主键唯一），因此间隙锁，顾名思义，锁的就是两个值之间的空隙；

比如文章开头的表t，初始化插入了6个记录，这就产生了7个间隙，如下；

![c4f44687d32a44509801345c1edf8c5f.png][]

这样，当执行 select \* from t where d=5 for update 的时候，就不止是给数据库中已有的6个记录加上了行锁（会全表扫描6条记录），还同时加了7个间隙锁；这样就确保了当前无法再插入新的记录；

也就是说这时候，在一行行扫描的过程中，不仅将给行加上了行锁，还给行两边的空隙，也加上了间隙锁；即**数据行是可以加上锁的实体，数据行之间的间隙，也是可以加上锁的实体**；

但是间隙锁跟我们之前碰到过的锁都不太一样；行锁锁冲突规则是：读读不冲突，其他的读写/写读/写写都会冲突，也就是说跟行锁有冲突关系的是“另外一个行锁”；

![2d31465a205b428f969c891a5ff72fdf.png][]

但是间隙锁与行锁不一样，跟间隙锁存在冲突关系的，是“往这个间隙中插入一个记录”这个操作；间隙锁之间都不存在冲突关系；举个例子：

![cef9ba47fa3b45eca14105bb52bdca14.png][]  
这里sessionB并不会被堵住；因为表t里并没有c=7这个记录，因此sessionA加的是间隙锁(5,10)；而sessionB也是在这个间隙加的间隙锁；它们有共同的目标，即：保护(5,10)这个间隙，不允许插入值；但这两个"当前读"语句之间是不冲突的；

**注意**：这里的(5,10)指的是c的值而非主键索引；因为c字段有索引，对条件所在的索引位置的前后间隙加锁；如果查询条件没走索引走全表扫描，则对全表所有行之间加间隙锁；关于加锁的详细规则在下篇介绍；

#### next-key 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，这样才符合前面说的“next-key lock都是前开后闭区间”）

#### 间隙锁gap lock和next-key lock带来的并发问题 ####

某个业务逻辑这样的：任意锁住一行，如果这一行不存在的话就插入，如果存在这一行就更新它的数据，代码如下：

begin;
    select * from t where id=N for update;
    
    /*如果行不存在*/
    insert into t values(N,N,N);
    /*如果行存在*/
    update t set d=N set id=N;
    
    commit;

你可能想到一种SQL写法 insert … on duplicate key update；但**MySQL官方文档有声明，在有多个唯一键的时候，insert … on duplicate key update 这个写法并发是不安全的****！**并且这种写法一般在公司的MySQL开发手册中是不推荐/禁止使用的，细节可参考下面的文章：[Mysql死锁排查：insert on duplicate死锁一次排查分析过程][Mysql_insert on duplicate]；另一种写法 replace 语句也有类似的作用，详情可参考[insert...on duplicate key update语法详解][insert...on duplicate key update]；

回到这个例子，现象是：**这个业务逻辑一旦有并发，就会碰到死锁**；你一定也觉得奇怪，这个逻辑每次操作前用for update锁起来，已经是最严格的模式了，怎么还会有死锁呢？这里，用两个session来模拟并发，并假设N=9；

![876ef8516e7641c698e15fcf39f4f92a.png][]

通过图中的分析，至此，两个session进入互相等待状态，**形成死锁**；当然，InnoDB的死锁检测马上就发现了这对死锁关系，两个事务回滚成本一样，选择让sessionA的insert语句报错返回了；

可见——**间隙锁的引入，可能会导致同样的语句锁住更大的范围，这其实是影响了并发度的**；

#### 小结 ####

1. 幻读指的是一个事务在前后两次查询同一个范围的时候，后一次查询看到了前一次查询没有看到的行；幻读仅专指“新插入的行”；

2. 在可重复读隔离级别下，普通的查询是快照读，是不会看到别的事务插入的数据的；因此，幻读在“当前读”下才会出现；

3. 幻读会导致对满足条件的行加锁这个语义被破坏，因为加锁后插入了新行；

4. 幻读会导致binlog恢复的数据与真实数据不一致，因为在提交事务之前，有新行插入，导致提交的事务中的更新语句作用到了预期之外的新行；

5. 为了解决幻读问题，InnoDB间隙锁 (Gap Lock)，锁的就是两个值之间的空隙，可以是主键，也可以是二级索引；

6. 与行锁不同，间隙锁之间都不存在冲突关系，跟间隙锁存在冲突关系的，是"往这个间隙中插入一个记录"这个操作；

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

8. 间隙锁和next-key lock的引入，解决了幻读的问题，但同时在并发情况下，可能导致死锁发生，原因是间隙锁获取不冲突导致多个线程都持有同一个间隙锁，但是执行插入时会冲突；

9. 为了解决幻读的问题引入间隙锁，但也带来了影响并发的问题；如果把隔离级别设置为读提交的话，就不存在幻读，也没有间隙锁了，如使用读提交隔离级别加 binlog\_format=row 的组合；但是就无法使用"一致性视图"来做到"边备份边更新数据"了；

本篇建议与下篇文章结合着一起看；

下篇文章：[《MySQL实战45讲》——学习笔记21 “加锁规则、加锁案例、死锁示例“][MySQL_45_21]

本章参考：[20 | 幻读是什么，幻读有什么问题？-极客时间][20 _ _-]

[e665eaf99ecd420f8d7e73b750d9f41e.png]: https://img-blog.csdnimg.cn/e665eaf99ecd420f8d7e73b750d9f41e.png
[8f07a22121a84ff6bfbd355c8d4b2cc7.png]: https://img-blog.csdnimg.cn/8f07a22121a84ff6bfbd355c8d4b2cc7.png
[606bb8dbb01245e89c5d8ec711fcff5a.png]: https://img-blog.csdnimg.cn/606bb8dbb01245e89c5d8ec711fcff5a.png
[c4f44687d32a44509801345c1edf8c5f.png]: https://img-blog.csdnimg.cn/c4f44687d32a44509801345c1edf8c5f.png
[2d31465a205b428f969c891a5ff72fdf.png]: https://img-blog.csdnimg.cn/2d31465a205b428f969c891a5ff72fdf.png
[cef9ba47fa3b45eca14105bb52bdca14.png]: https://img-blog.csdnimg.cn/cef9ba47fa3b45eca14105bb52bdca14.png
[Mysql_insert on duplicate]: https://www.cnblogs.com/sueyyyy/p/13047035.html
[insert...on duplicate key update]: https://blog.csdn.net/ZYC88888/article/details/104250372
[876ef8516e7641c698e15fcf39f4f92a.png]: https://img-blog.csdnimg.cn/876ef8516e7641c698e15fcf39f4f92a.png
[MySQL_45_21]: https://blog.csdn.net/minghao0508/article/details/128205386
[20 _ _-]: https://time.geekbang.org/column/article/75173