[TOC]
为了方便你理解，我们再一起复习一下加锁规则。这个规则中，包含了两个“原则”、两个“优化”和一个“bug”：
- 原则 1：加锁的基本单位是 next-key lock。希望你还记得，next-key lock 是前开后闭区间。
- 原则 2：查找过程中访问到的对象才会加锁。
- 优化 1：索引上的等值查询，给唯一索引加锁的时候，next-key lock 退化为行锁。
- 优化 2：索引上的等值查询，向右遍历时且最后一个值不满足等值条件的时候，next-key lock 退化为间隙锁。
- 一个 bug：唯一索引上的范围查询会访问到不满足条件的第一个值为止。
接下来，我们的讨论还是基于下面这个表 t：
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);
不等号条件里的等值查询
有同学对“等值查询”提出了疑问：等值查询和“遍历”有什么区别？为什么我们文章的例子里面，where 条件是不等号，这个过程里也有等值查询？
我们一起来看下这个例子，分析一下这条查询语句的加锁范围：
begin;
select * from t where id>9 and id<12 order by id desc for update;
利用上面的加锁规则，我们知道这个语句的加锁范围是主键索引上的 (0,5]、(5,10]和 (10, 15)。也就是说，id=15 这一行，并没有被加上行锁。为什么呢？
我们说加锁单位是 next-key lock，都是前开后闭区间，但是这里用到了优化 2，即索引上的等值查询，向右遍历的时候 id=15 不满足条件，所以 next-key lock 退化为了间隙锁 (10, 15)。
但是，我们的查询语句中 where 条件是大于号和小于号，这里的“等值查询”又是从哪里来的呢？
要知道，加锁动作是发生在语句执行过程中的，所以你在分析加锁行为的时候，要从索引上的数据结构开始。这里，我再把这个过程拆解一下。
如图 1 所示，是这个表的索引 id 的示意图。
image.png
-
首先这个查询语句的语义是 order by id desc，要拿到满足条件的所有行，优化器必须先找到“第一个 id<12 的值”。
-
这个过程是通过索引树的搜索过程得到的，在引擎内部，其实是要找到 id=12 的这个值，只是最终没找到，但找到了 (10,15) 这个间隙。
-
然后向左遍历，在遍历过程中，就不是等值查询了，会扫描到 id=5 这一行，所以会加一个 next-key lock (0,5]。
也就是说，在执行过程中，通过树搜索的方式定位记录的时候，用的是“等值查询”的方法。
等值查询的过程
与上面这个例子对应的，是 @发条橙子同学提出的问题：下面这个语句的加锁范围是什么？
begin;
select id from t where c in(5,20,10) lock in share mode;
这条查询语句里用的是 in，我们先来看这条语句的 explain 结果。
image.png
可以看到，这条 in 语句使用了索引 c 并且 rows=3，说明这三个值都是通过 B+ 树搜索定位的。
在查找 c=5 的时候，先锁住了 (0,5]。但是因为 c 不是唯一索引，为了确认还有没有别的记录 c=5，就要向右遍历，找到 c=10 才确认没有了，这个过程满足优化 2，所以加了间隙锁 (5,10)。
同样的，执行 c=10 这个逻辑的时候，加锁的范围是 (5,10] 和 (10,15)；执行 c=20 这个逻辑的时候，加锁的范围是 (15,20] 和 (20,25)。
通过这个分析，我们可以知道，这条语句在索引 c 上加的三个记录锁的顺序是：先加 c=5 的记录锁，再加 c=10 的记录锁，最后加 c=20 的记录锁。
你可能会说，这个加锁范围，不就是从 (5,25) 中去掉 c=15 的行锁吗？为什么这么麻烦地分段说呢？
因为我要跟你强调这个过程：这些锁是“在执行过程中一个一个加的”，而不是一次性加上去的。
理解了这个加锁过程之后，我们就可以来分析下面例子中的死锁问题了。
如果同时有另外一个语句，是这么写的：
select id from t where c in(5,20,10) order by c desc for update;
此时的加锁范围，又是什么呢？
我们现在都知道间隙锁是不互锁的，但是这两条语句都会在索引 c 上的 c=5、10、20 这三行记录上加记录锁。
这里你需要注意一下，由于语句里面是 order by c desc， 这三个记录锁的加锁顺序，是先锁 c=20，然后 c=10，最后是 c=5。
也就是说，这两条语句要加锁相同的资源，但是加锁顺序相反。当这两条语句并发执行的时候，就可能出现死锁。
关于死锁的信息，MySQL 只保留了最后一个死锁的现场，但这个现场还是不完备的。
现在，我就来简单分析一下上面这个例子的死锁现场。
怎么看死锁？
图 3 是在出现死锁后，执行 show engine innodb status 命令得到的部分输出。这个命令会输出很多信息，有一节 LATESTDETECTED DEADLOCK，就是记录的最后一次死锁信息。
image.png
我们来看看这图中的几个关键信息。
这个结果分成三部分：
(1) TRANSACTION，是第一个事务的信息；
(2) TRANSACTION，是第二个事务的信息；
WE ROLL BACK TRANSACTION (1)，是最终的处理结果，表示回滚了第一个事务。
第一个事务的信息中：
- 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；
第一个事务信息就只显示出了等锁的状态，在等待 (c=10,id=10) 这一行的锁。
当然你是知道的，既然出现死锁了，就表示这个事务也占有别的锁，但是没有显示出来。别着急，我们从第二个事务的信息中推导出来。
第二个事务显示的信息要多一些：
- “ 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 的记录锁。
因此导致了死锁。这里，我们可以得到两个结论：
- 由于锁是一个个加的，要避免死锁，对同一组资源，要按照尽量相同的顺序访问；
- 在发生死锁的时刻，for update 这条语句占有的资源更多，回滚成本更大，所以 InnoDB 选择了回滚成本更小的 lock in share mode 语句，来回滚。
怎么看锁等待？
看完死锁，我们再来看一个锁等待的例子。
image.png
可以看到，由于 session A 并没有锁住 c=10 这个记录，所以 session B 删除 id=10 这一行是可以的。但是之后，session B 再想 insert id=10 这一行回去就不行了。
image.png
我们来看几个关键信息。
- index PRIMARY of table
test.t，表示这个语句被锁住是因为表 t 主键上的某个锁。 - lock_mode X locks gap before rec insert intention waiting 这里有几个信息：
insert intention 表示当前线程准备插入一个记录，这是一个插入意向锁。为了便于理解，你可以认为它就是这个插入动作本身。
gap before rec 表示这是一个间隙锁，而不是记录锁。
- 那么这个 gap 是在哪个记录之前的呢？接下来的 0~4 这 5 行的内容就是这个记录的信息。
- n_fields 5 也表示了，这一个记录有 5 列：
因此，我们就知道了，由于 delete 操作把 id=10 这一行删掉了，原来的两个间隙 (5,10)、(10,15）变成了一个 (5,15)。
说到这里，你可以联合起来再思考一下这两个现象之间的关联：
- session A 执行完 select 语句后，什么都没做，但它加锁的范围突然“变大”了；
- 第 21 篇文章的课后思考题，当我们执行 select * from t where c>=15 and c<=20 order by c desc lock in share mode; 向左扫描到 c=10 的时候，要把 (5, 10]锁起来。
也就是说，所谓“间隙”，其实根本就是由“这个间隙右边的那个记录”定义的。
update 的例子
image.png
你可以自己分析一下，session A 的加锁范围是索引 c 上的 (5,10]、(10,15]、(15,20]、(20,25]和 (25,supremum]。
注意：根据 c>5 查到的第一个记录是 c=10，因此不会加 (0,5]这个 next-key lock。
之后 session B 的第一个 update 语句，要把 c=5 改成 c=1，你可以理解为两步：
- 插入 (c=1, id=5) 这个记录；
- 删除 (c=5, id=5) 这个记录。
按照我们上一节说的，索引 c 上 (5,10) 间隙是由这个间隙右边的记录，也就是 c=10 定义的。所以通过这个操作，session A 的加锁范围变成了图 7 所示的样子：
image.png
好，接下来 session B 要执行 update t set c = 5 where c = 1 这个语句了，一样地可以拆成两步：
- 插入 (c=5, id=5) 这个记录；
- 删除 (c=1, id=5) 这个记录。
第一步试图在已经加了间隙锁的 (1,10) 中插入数据，所以就被堵住了。
小结
在我看来，每个想认真了解 MySQL 原理的同学，应该都要能够做到：通过 explain 的结果，就能够脑补出一个 SQL 语句的执行流程。达到这样的程度，才算是对索引组织表、索引、锁的概念有了比较清晰的认识。你同样也可以用这个方法，来验证自己对这些知识点的掌握程度。
在分析这些加锁规则的过程中，我也顺便跟你介绍了怎么看 show engine innodb status 输出结果中的事务信息和死锁信息，希望这些内容对你以后分析现场能有所帮助。
FAQ
删除数据，导致锁扩大的描述：“因此，我们就知道了，由于 delete 操作把 id=10 这一行删掉了，原来的两个间隙 (5,10)、(10,15）变成了一个 (5,15)。”
我觉得这个提到的(5, 10) 和 (10, 15)两个间隙会让人有点误解，实际上在删除之前间隙锁只有一个(10, 15)，删除了数据之后，导致间隙锁左侧扩张成了5，间隙锁成为了(5, 15)。
嗯 所以我这里特别小心地没有写“锁“这个字。
间隙 (5,10)、(10,15）是客观存在的。
你提得也很对，“锁”是执行过程中才加的，是一个动态的概念。
问题二：
对于第21讲的思考题， select * from t where c>=15 and c<=20 order by c desc lock in share mode， 老师已经给出了答案，我这里再详细理解一下：
先定位索引c上最右边c=20的行，所以第一个等值查询会扫描到c=25，然后通过优化2，next-key lock退化为间隙锁，则会加上间隙锁（20，25），紧接着再向左遍历，会加 next-key lock (15, 20], (10, 15], 因为要扫描到c=10才停下来，所以也会加next-key lock (5,10]
理解的是否正确？
问题三：
对于上面的问题二的sql，在索引c上，把（10，25）这段区间锁上就应该是完备的了，理论上（5，10]这段区间是否锁上对结果应该没有影响呀。
是不是说MySQL就是这么实现的，next-key lock前开后闭，因为扫到了c=10，所以会加next-key lock (5,10]，这里MySQL的实现扩大了锁的区间范围，其实没有这个必要？
2. 对的
3. “因为扫到了c=10，所以会加next-key lock (5,10]”， 对的。
第3个问题，真的会让人觉得mysql很傻。但是 是符合老师的两个原则的。条件>=15,向左扫描到15时，范围查找>就结束了，因为不是唯一索引，所以还要继续向左扫描=15。直到扫描到10，根据原则2，扫描到的都加锁，所以10是要加锁的。但是原则1，加锁的单位是next-key lock，所以加了(5，10]。
感觉mysql傻，是因为优化2，只适用于向右遍历。这个例子是向左遍历的。而且next-key lock左开右闭有点死板了。应该ASC左开右闭，DESC左闭右开。这样优化2向左也能由新定义的next-key lock 退化为间隙锁了。加锁范围就是(10,25)。10这条数据也不用加锁，如果真被删了，根据间隙锁的定义，锁范围自然会扩大
嗯，赞同。感觉像是历史遗留问题，脑补了一下实现方案，从程序设计角度来说，这样实现起来设计思想统一，没有考虑到对desc的特殊优化。以左右存在的记录作为间隙加锁也扩大了间隙锁的范围，但这样的设计应该是最清晰的，并且平时的应用场景又大多是写少读多。综上感觉有些应该是权衡和历史遗留的问题。
问题2中扫描到c=10停下来已经不满足条件了，为啥还要继续向左扫描锁住（5，10]呢。
加锁的基本单位是next key lock
最后一个update的例子，为没有加（0，5）的间隙呢？我理解应该是先拿c＝5去b+树搜索，按照间隙索最右原则，应该会加（0，5]的间隙，然后c＝5不满足大于5条件，根据优化2原则退化成（0，5）的间隙索，我是这样理解的
根据c>5查到的第一个记录是c=10，因此不会加(0,5]这个next-key lock。
网友评论