MySQL学习笔记：等值查询、范围查询、死锁、间隙锁的本质

环境

MySQL：5.7.26-log

前言

答疑文章（二）：用动态的观点看加锁

• 原则 1：加锁的基本单位是 next-key lock。希望你还记得，next-key lock 是前开后闭区间。
• 原则 2：查找过程中访问到的对象才会加锁。
• 优化 1：索引上的等值查询，给唯一索引加锁的时候，next-key lock 退化为行锁。
• 优化 2：索引上的等值查询，向右遍历时且最后一个值不满足等值条件的时候，next-key lock 退化为间隙锁。
• 一个 bug：唯一索引上的范围查询会访问到不满足条件的第一个值为止。（MySQL 8 已经修复）

不等号条件里的等值查询

begin;
select * from t where id>9 and id<12 order by id desc for update;

1. 首先这个查询语句的语义是 order by id desc，要拿到满足条件的所有行，优化器必须先找到“第一个 id<12 的值”；

2. 这个过程是通过索引树的搜索过程得到的，在引擎内部，其实是要找到 id=12 的这个值，只是最终没找到，但找到了 (10,15) 这个间隙。（这里就是等值查询，即：通过索引树搜索的就是等值查询）。

3. 然后向左遍历，在遍历过程中，就不是等值查询了，会扫描到 id=5 这一行，所以会加一个 next-key lock (0,5]。

说明：

①“查询语句order by id desc ”，说明是按照ID进行降序，所以其实先查询id<12的值，然后从右往左进行扫描。
②默认情况，一般都是升序，所以一般也就是从左往右扫描。

小结：也就是说，在执行过程中，通过树搜索的方式定位记录的时候，用的是“等值查询”的方法。

死锁

关于死锁的信息，MySQL 只保留了最后一个死锁的现场，但这个现场还是不完备的。

执行 show engine innodb status 命令得到的部分输出。这个命令会输出很多信息，有一节 LATESTDETECTED DEADLOCK，就是记录的最后一次死锁信息。

我们来看看这图中的几个关键信息。

1. 这个结果分成三部分：

• (1) TRANSACTION，是第一个事务的信息；
• (2) TRANSACTION，是第二个事务的信息；
• WE ROLL BACK TRANSACTION (1)，是最终的处理结果，表示回滚了第一个事务。

2. 第一个事务的信息中：

• WAITING FOR THIS LOCK TO BE GRANTED，表示的是这个事务在等待的锁信息；
• index c of table test.t，说明在等的是表 t 的索引 c 上面的锁；
• lock mode S waiting 表示这个语句要自己加一个读锁，当前的状态是等待中；
• Record lock 说明这是一个记录锁；
• n_fields 2 表示这个记录是两列，也就是字段 c 和主键字段 id；
• 0: len 4; hex 0000000a; asc ;; 是第一个字段，也就是 c。值是十六进制 a，也就是 10；
• 1: len 4; hex 0000000a; asc ;; 是第二个字段，也就是主键 id，值也是 10；
• 这两行里面的 asc 表示的是，接下来要打印出值里面的“可打印字符”，但 10 不是可打印字符，因此就显示空格。
• 第一个事务信息就只显示出了等锁的状态，在等待 (c=10,id=10) 这一行的锁。
• 当然你是知道的，既然出现死锁了，就表示这个事务也占有别的锁，但是没有显示出来。别着急，我们从第二个事务的信息中推导出来。

3. 第二个事务显示的信息要多一些：

• “ HOLDS THE LOCK(S)”用来显示这个事务持有哪些锁；
• index c of table test.t 表示锁是在表 t 的索引 c 上；
• hex 0000000a 和 hex 00000014 表示这个事务持有 c=10 和 c=20 这两个记录锁；
• WAITING FOR THIS LOCK TO BE GRANTED，表示在等 (c=5,id=5) 这个记录锁。

从上面这些信息中，我们就知道：
① “lock in share mode”的这条语句，持有 c=5 的记录锁，在等 c=10 的锁；
② “for update”这个语句，持有 c=20 和 c=10 的记录锁，在等 c=5 的记录锁。

因此导致了死锁。这里，我们可以得到两个结论：

1. 由于锁是一个个加的，要避免死锁，对同一组资源，要按照尽量相同的顺序访问；
2. 在发生死锁的时刻，for update 这条语句占有的资源更多，回滚成本更大，所以 InnoDB 选择了回滚成本更小的 lock in share mode 语句，来回滚。

怎么看锁等待 与 间隙锁的本质

上图的意思：sessionA先查询加读锁，然后sessionB先删除id=10记录，然后再插入ID=10的记录，发现被阻塞了。

sessionA：因为id>10,且是自然顺序，也就是升序。第一条记录是15，所以间隙锁(10, 15)。
sessionB: delete id=10以后，间隙锁，变为：(5,15)

也就是说，session A 执行完 select 语句后，什么都没做，但它加锁的范围突然“变大”了；

又如：当我们执行 select * from t where c>=15 and c<=20 order by c desc lock in share mode; 向左扫描到 c=10 的时候，要把 (5, 10]锁起来。

• 间隙锁的本质：也就是说，所谓“间隙”，其根本就是由“这个间隙右边的那个记录”定义的。
  
  
• 锁，作用于索引上的。

在看一个例子：update

sessionA：自然顺序，所以是升序，c>5，那么第一条记录就是c=10,所以间隙锁(5,10]、(10,15]、(15,20]、(20,25]、(25,无穷大]。

sessionB:第一个update，可以看做：

1. 删除 (c=5, id=5) 这个记录。
2. 插入 (c=1, id=5) 这个记录；

按照上面说的，间隙锁的本质由右边的记录也就是c=10决定的。所以sessionA的间隙锁变为了：(1,10]…后面不变

sessionB的第二个update，可以看做：

1. 删除 (c=1, id=5) 这个记录。
2. 插入 (c=5, id=5) 这个记录；

因为c=1没有被锁住，所以可以删除，然后插入c=5，被间隙锁(1,10]锁住了，所以被阻塞了。

在原文中，是将update看做，先插入，再删除。我觉得不合理，因为先插入会导致主键冲突。

举例思考

例1 等值查询与遍历区别

select * from t where c>=15 and c<=20 order by c desc in share mode;

这个语句是根据 c=20 来查数据的，所以加锁(20,25]的时候，可以使用优化2；

select * from t where id>10 and id<=15 for update；

这里的id=20，是用“向右遍历”的方式得到的，没有优化，按照“以next-key lock”为加锁单位来执行。

等值查询：通过树搜索的方式定位记录。

总结

• 等值查询：通过树搜索的方式定位记录。遍历就是遍历叶子节点。加锁应该是加在叶子节点上，在树搜索中遇到的，不会锁住。
• 间隙锁的本质：所谓“间隙”，其根本就是由“这个间隙右边的那个记录”定义的。