正文

还是上一节的 sql

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

加锁的规则

两个原则、两个优化、一个BUG

两个原则：

1. 加锁的基本单位是 next-key lock。
2. 查找过程中访问到的对象才会加锁。（这个过程中需要考虑的是普通索引和主键索引的扫描范围）

两个优化

1. 索引上使用等值查询，给唯一索引加锁的时候，next-key lock 会退化为行锁。
2. 索引上使用等值查询，向右遍历到第一个与查询值不等的值的时候 next-key lock 退化为间隙锁。而范围查询不会。
   • 比如说有索引的值为 1、2、3、4、5，我现在要查询 id = 3 ，那么扫描的第一个与等值不等的值 4 时候就会停止，并在之间加上锁（3,4）。

一个 BUG

注意

InnoDB（5.x 系列 <=5.7.24，8.0 系列 <=8.0.13。）的 BUG，实测在 8.3 版本复现不了这个问题。

• 唯一索引上的范围查询扫描到不满足条件的第一个值。

案例 1：等值查询间隙锁

Session A	Session B	Session C
begin;
update t set d = d+1 where id = 7;
	insert into t values(8,8,8);// block
		update t set d = d+1 where id = 10;// success

因为加了 next-key lock，所以 (5,10] 被锁，但是由于唯一索引的，向右遍历的索引中第一个与 7 不等的值为 10 所以在加了间隙锁 (7-10)，10 这条数据并没有加锁，所以 session c 执行成功了。

案例 2：非唯一索引等值锁

Session A	Session B	Session C
begin;
select id from t where c = 5 lock in share mode;
	update t set d = d+1 where id =5; // success
		insert into t values(7,7,7);

1. 因为 c = 5，所以加的锁是 (0,5]，又因为 c 是个普通的索引，所以要向右扫描大第一个不等于 5 的值，所以加的锁是 (5,10];
2. 因为优化 2，用的是等值查询，所以 next key lock 退化为间隙锁变成 (5,10)；所以 Session C 被阻塞；
3. 由于原则 2，对扫描到的对象才加锁，因为 c 是普通索引查询的是 id，所以用到了覆盖索引，不需要扫描整行数据，所以 Session B 的操作才能成功。

lock in share mode 和 for update 是不一样的，对于 lock in share mode 如果有覆盖索引优化，没有访问到主键索引，那么就不会对主键索引上的数据加锁，for update 默认会认为你要进行 update 操作了，会对主键索引上对应的条件的行进行加锁。

案例 3：主键索引范围锁

select * from t where id=10 for update; 			/**Q1**/
select * from t where id>=10 and id<11 for update;	/**Q2**/

Session A	Session B	Session C
begin;
select * from t where id=10 for update;
	insert into t values(7,7,7)//success insert into t values(13,13,13)//blocking
		update t set d=d +1 where id = 15;

对于 Q1 来说，是个等值为 10 的查询，由于原则 1 会加上一个 (5,10] 的锁（而不是 (10,15]，因为这个根据 next-key lock 的定义已经不包括 10 了），由于是唯一索引，所以退化成为了 id=10 的行锁。

对于 Q2 来说，根据上述会首先有个 10 的行锁，然后根据优化 2 会对扫描到下一个索引所以是 (10,15]，所以锁是 [10,15]。

案例 4：非唯一索引的范围锁

Session A	Session B	Session C
begin;
select * from t where c >=10 and c<11 for update;
	insert into t values(8,8,8)//blocking
		update t set d=d +1 where c = 15;//blocking

首先会加上一个 (5,10] 的锁，由于 c 不是唯一索引不满足优化 1 无法退化成行锁，对于一个非唯一索引来说根据优化 2 需要扫到下一个索引 (10,15]，因为是范围查询无法退化成间隙锁。所以所的范围是 (5,15]。

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

这个语句加的锁首先是根据原则 1 加上 (5,10] 和 (10,15]，然后根据优化 2 索引上找到下一个不等于范围的值退化成上间隙锁，所以 (10,15] 退化成 (10,15)，因为又是倒序所以是向左扫描根据 BUG 会加上 (0,5] 的锁。

所以整个锁是 (0,5](5,10](10,15)=>(0,15)，但还是有前提得在上面说的有 bug 的 MySQL 版本中会出现，8.3 实测是没有 (0,5] 的锁的。

案例 5：唯一索引范围锁 BUG

Session A	Session B	Session C
begin;
select * from t where id >10 and id <=15 for update ;
	update t set d = d+1 where id = 20 ;//blocking
		insert into t values(16,16,16)//blocking

首先要说明的是这个题是有历史性问题的，5.x 系列 <=5.7.24，8.0 系列 <=8.0.13。我使用的 8.3 版本并无法复现这个问题。以下说明是在版本范围内的解释。

Session A 根据原则一加上一个 (10,15] 的 next-key lock，然后根据 BUG 会向后扫描到第一个不在范围内的数据就是 (15,20]，所以 B C 都会被阻塞。

案例 6：非唯一索引上存在等值的例子

insert into t values(30,10,30);

Session A	Session B	Session C
begin;
delete from t where c= 10;
	insert into t values(12,12,12)//blocking
		update t set d =d+1 where c=15;//success

这个时候会有两条 c=10 的数据，c 是普通索引所以会回表，根据原则 2，对 id=10 和 30 的这两条主键索引的数据加锁，然后根据原则 1，Session A 会加上一个 (5,10] 的锁，根据优化 2 向后扫描，加上 (10,15) 的锁，所以整个锁的范围是 (5,15) 所以 B 阻塞、c 成功。

案例 7：limit 语句加锁

Session A	Session B
begin;
delete from t where c= 10 limit 2;
	insert into t values(12,12,12)//blocking

这个和上面的语义是一样的，因为数据库里只有两条 c=10 的数据，但是锁的范围是不同的。

• 相同点
  • 根据原则 1 加上 (5,10] 的锁。
• 不同点
  • 因为是 limit 2 如果扫描到第二条相符的就停止了，并不会向后再扫描了。

锁的范围是 (5,5,5) 到 (30,10,30)。所以，在删除数据的时候尽量加 limit。

案例 8：一个死锁的例子

Session A	Session B
begin;
select id from t where c = 10 lock in share mode;
	update t set d = d+1 where c = 10; // blocking
insert into t values(8,8,8); //success
	Deadlock found when trying to get lock; try restarting transaction

首先 Session A 已经分析过了加了两个锁，原则 1 和优化 2 ，(5,10]，(10,15)。(5,15)。Session B 也要在 (5,10] 加锁。但是只成功了一个。

next-key lock 是间隙锁 + 行锁。是分成两步骤加的，(5,10) 和 10 ，并且间隙锁之间不互斥。

session B (5,10) 的锁成功加上，但是 10 的行锁由于互斥，需要等待 A 的释放。然后 A 进行插入操作，A 需要等待 B 的间隙锁释放，而 B 则需要等待 A 的行锁释放，所以产生死锁，InnoDB 让 B 回滚。

总结

在可重复读的前提下，所有加锁的资源都是在事务提交或者回滚的情况下才释放的。

在读已提交的级别下，去掉了间隙锁，只留下了行锁，并且读已提交还有一个优化，就是查询到的不满足条件的行，在语句执行完成后就会释放锁，不用等待事务提交。

上一篇的答案

Session A	Session B
begin; select * from t where c>=15 and c<=20 order by c desc lock in share mode;
	insert into t values(6,6,6);// blocking

这个主要的原因是 order by desc，首先根据原则 1 和优化 2，加上了一个 (10,20] 的锁，然后后半部分加上了一个 (20,25) 的锁，如果对这个索引进行降序排序，那么就会使用到 Backward index scan 。这种扫描方式是基于双向链表的，可以理解为从右向左的扫描，然后扫描到 = 10 才会停下来，但是 10 上有一个 next-key lock (5,10]，所以在插入 6,6,6 的时候会阻塞。