透过实例看行锁：深入探讨加锁原则与优化（上）

本文为《MySQL归纳学习》专栏的第十八篇文章，同时也是关于《MySQL锁机制》知识点的第三篇文章。

相关文章：

锁的三重奏：MySQL全局锁、表级锁与行锁详解

从幻读看MySQL：读取方式解读与解决方案

欢迎来到这篇深度探索MySQL行锁使用的文章。在前文中，我们已经对MySQL的锁机制做了全面的介绍，接下来我们将更深入地剖析行锁的应用。在本篇文章中，我们将明确加锁的两大原则以及两种优化方法，并结合实例进行详细解析。内容丰富，为了确保各位读者能逐步理解和掌握，我们把本主题分为上下两篇进行讲述。本篇重点是关于主键索引等值锁、非唯一索引等值锁、主键索引范围锁以及非唯一索引范围锁的实际应用。让我们一起进入MySQL行锁的世界，揭开其神秘面纱。

MySQL 后面的版本可能会改变加锁策略，所以这个规则只限于截止到现在的最新版本，即 5.x 系列 <=5.7.24，8.0 系列 <=8.0.13。

加锁规则里面，包含了两个“原则”、两个“优化”和一个“bug”。

1. 原则 1：加锁的基本单位是 next-key lock，next-key lock 是前开后闭区间。
2. 原则 2：查找过程中访问到的对象才会加锁
3. 优化 1：索引上的等值查询，给唯一索引加锁的时候，next-key lock 退化为行锁。
4. 优化 2：索引上的等值查询，向右遍历时且最后一个值不满足等值条件的时候，next-key lock 退化为间隙锁。
5. 一个 bug：唯一索引上的范围查询会访问到不满足条件的第一个值为止。（mysql 8.0.18 开始，修复了这个 bug ）

关于上述 5条规则，进行补充说明：

• 原则2：对于未命中索引的查询要走全表扫描，这种在扫描前就会给全表加上next-key lock，等同于锁全表。
• 优化2：当使用普通索引，当访问到第一个符合条件的对象的时候，不能保证之后还有其他对象符合条件，就会一直访问到当前对象所在间隙的末尾（也就是右闭的行对象），判断末尾的对象是否满足条件，若不满足，临键锁退化为 间隙锁 ，因为末尾的对象并不符合条件，行锁也就没有必要，只保留间隙锁，防止插入即可。

上述提到访问的对象才会加锁，这个“对象”指的是列，不是记录行。加锁，是加在索引上的。 列上有索引，就加在索引上； 列上没有索引，就加在主键上。

接下来采用如下表结构进行测试，如无特殊说明，都是基于 RR 隔离级别进行测试，MySQL版本为 5.7.32。

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

主键索引等值锁

由于表 t 中没有 id=7 的记录，所以用我们上面提到的加锁规则判断一下的话：

• 根据原则 1，加锁单位是 next-key lock，session A 加锁范围就是 (5,10]；
• 同时根据优化 2，这是一个等值查询 (id=7)，而 id=10 不满足查询条件，next-key lock 退化成间隙锁，因此最终加锁的范围是 (5,10)。 （右侧的闭区间 10 是行锁，所以变为开区间）

所以，session B 要往这个间隙里面插入 id=8 的记录会被锁住，但是 session C 修改 id=10 这行是可以的。

当 session B blocked 时，执行下述语句，可以查看锁信息：

SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCKS\G

测试效果如下：

图中 LOCK_TYPE 中的 RECORD 表示记录锁的意思（type值还可能为 table，即表锁），通过 LOCK_MODE 可以确认是 next-key 锁，还是间隙锁，还是记录锁：

• 如果 LOCK_MODE 为 X，说明是 next-key 锁；
• 如果 LOCK_MODE 为 X, REC_NOT_GAP，说明是记录锁；
• 如果 LOCK_MODE 为 X, GAP，说明是间隙锁；

根据 lock_mode、lock_type、lock_index、lock_data，可以确认此时 id 上加的锁为间隙锁(5,10)。

非唯一索引等值锁

这里 session A 要给索引 c 上 c=5 的这一行加上读锁。

1. 根据原则 1，加锁单位是 next-key lock，所以加锁 (0,5]，又因为不满足优化2，所以没有退化为间隙锁。
2. 要注意 c 是普通索引，因此仅访问 c=5 这一条记录是不能马上停下来的，需要向右遍历，查到 c=10 才放弃，此时加锁(5,10]。
3. 但是同时这个符合优化 2：等值判断，向右遍历，最后一个值不满足 c=5 这个等值条件，因此退化成间隙锁 (5,10)。最终 sesion A 加的锁是，索引 c 上的 (0,5] 和 (5,10) 这两个 next-key lock。
4. 根据原则 2 ，只有访问到的对象才会加锁，这个查询使用覆盖索引，并不需要访问主键索引，所以主键索引上没有加任何锁，这就是为什么 session B 的 update 语句可以执行完成。

但 session C 要插入一个 (7,7,7) 的记录，就会被 session A 的间隙锁 (5,10) 锁住。

lock in share mode 只锁覆盖索引，但是如果是 for update 就不一样了。 执行 for update 时，系统会认为你接下来要更新数据，因此会顺便给主键索引上满足条件的行加上行锁。

这个例子说明，锁是加在索引上的；同时，它给我们的指导是，如果你要用 lock in share mode 来给行加读锁避免数据被更新的话，就必须得绕过覆盖索引的优化，在查询字段中加入索引中不存在的字段。

比如，将 session A 的查询语句改成 select d from t where c=5 lock in share mode 或者 select id from t where c=5 for update 。从测试效果可以看出，sessionB 和 sessionC 都会被阻塞住。

主键索引范围锁

下面这两条查询语句，加锁范围相同吗？

mysql> select * from t where id=10 for update;
mysql> select * from t where id>=10 and id<11 for update;

1. 开始执行的时候，要找到第一个 id=10 的行，因此本该是 next-key lock(5,10]。 根据优化 1， 主键 id 上的等值条件，退化成行锁，只加了 id=10 这一行的行锁。
2. 范围查找就往后继续找，找到 id=15 这一行停下来，因此需要加 next-key lock(10,15]。（注意，此处无法使用优化2，因为 <11 不是等值查询， 首次 session A 定位查找 id=10 的行的时候，是当做等值查询来判断的，而向右扫描到 id=15 的时候，用的是范围查询判断。 另外在 8.0.19版本锁定区域已经是（10，15）了。 ）

最终 session A 的加锁范围为：行锁 id =10，next-key lock(10,15]（8.0.19版本之后变为间隙锁）。

加上order by desc

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

explain select * from t where id<12 order by id desc for update;

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

-- blocked
insert into t values(4,4,4);
--  blocked
insert into t values(7,7,7);
-- success
update t set d=17 where id=15;
--  blocked
update t set d=7 where id=5;

执行效果图如下所示：

根据上述测试结果可知，加锁范围是主键索引上的 (0,5]、(5,10]和 (10, 15)。为什么呢？

1. 因为 order by id desc，要拿到满足条件的所有行，优化器必须先找到“第一个 id<12 的值”。
2. 这个过程是通过索引树的搜索过程得到的，此时采用的是等值查询搜索右边界值，这里的右边界值是 15，先加next-key lock(10,15]，然后根据优化2，所以得到了间隙锁(10,15)。
3. 向左遍历，查询到10这个值，这个时候加(5,10]，判断条件id > 9 成立，继续向左遍历；
4. 向左遍历，查询到5这个值，这个时候加(0,5]，这里没有满足优化2，个人理解是因为这里是向左遍历，所以才会不满足。

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

我们对上述查询语句再做一次变形，如下所示：

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

-- blocked
insert into t values(4,4,4);
mysql> insert into t values(0,0,0);
ERROR 1062 (23000): Duplicate entry '0' for key 'PRIMARY'
-- blocked
insert into t values(50,50,50);

经过测试发现，所有主键索引都会加锁了，等同于锁全表。

加上 order by asc

-- session A
begin;
select * from t where id>9 and id<12 order by id asc for update;
-- 等价于下面这条语句
select * from t where id>9 and id<12 for update;

-- success
insert into t values(4,4,4);
-- blocked
insert into t values(7,7,7);
-- success
update t set d=7 where id=5;
-- blocked
update t set d=17 where id=15;

如果上述 SQL 语句中按照升序排列，加锁范围又是怎样呢？我们都知道索引树是B+树，默认是升序的。

order by desc 语义下范围查询的第一步是找到右边界，此时是使用的等值查询去走索引树搜索查询边界值； 非desc的语义下是走的索引树链表。

1. 添不添加 order by id asc 语句，默认都是根据 id 升序查询的，扫描到 id =9，发现没有，开始向右遍历；
2. 向右遍历，进行范围查询，扫描到 id =10，加锁(5,10]，不满足优化1和2；扫描到 id=15，加锁 (10,15]，此时是范围查询，所以没法使用优化2。

非唯一索引范围锁

这次 session A 用字段 c 来判断，加锁规则跟案例三唯一的不同是：在第一次用 c=10 定位记录的时候，索引 c 上加了 (5,10]这个 next-key lock 后，由于索引 c 是非唯一索引，不满足优化2，所以没有退化为间隙锁，因此最终 sesion A 加的锁是，索引 c 上的 (5,10] 和 (10,15] 这两个 next-key lock。

为什么(5,10]不满足优化2，没有退化为间隙锁呢？

这点我在文章开头优化 2的补充说明中提到过，首先等值查询到 c=10，此时加锁(5,10]，向右遍历时获取到的最后一个值就是10，因为满足等值条件，所以就没有退化。

加上oder by desc

我们测试过查询条件是主键索引的情况，如果是普通索引呢？

当前表 t 的数据为：

insert into t values(0,0,0),(5,5,5),
(10,10,10),(15,15,15),(20,20,20),(25,25,25);

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

-- blocked
insert into t values(6,6,6);
-- blocked
insert into t values(23,23,23);
-- success
update t set d=d+1 where id =25;
-- success
update t set d=d+1 where id =5;

为什么 session B会被阻塞掉呢？(有些人 session B没被阻塞，是因为(30,10,30)这条记录的存在导致的）

看看 session A 的 select 语句加了哪些锁：

• 由于是 order by c desc，首先等值查询 c=22，没找到，就找右边界 c=25，加锁(20,25]，满足优化2，则退化为(20,25)，接着向左遍历 next-key lock (15,20]。
• 在索引 c 上向左遍历，要扫描到 c=10 才停下来，所以 next-key lock 会加到 (5,10]，这正是阻塞 session B 的 insert 语句的原因。
• 在扫描过程中，c=20、c=15、c=10 这三行都存在值，由于是 select *，需要回表，但因为 c=10 不需要回表查询，所以索引 c=10 上有一个行锁。最终效果：c上有20,15,10加锁，id上只有20,15加锁。

因此，session A 的 select 语句锁的范围就是：

• 索引 c 上 (5, 25)；
• 主键索引上 id=15、20 两个行锁。

因为 next-key lock 是左开右闭，所以从右到左扫描的过程中，扫描到 c=10 停下来，next-key lock就变成了[10,15]，这是不符合 next-key lock 格式的，所以就多了一个 next-key lock(5,10]。

加上 order by asc

-- session A
begin;
select * from t where c>9 and c<12 order by c asc for update;

-- success
insert into t values(4,4,4);
-- blocked
insert into t values(7,7,7);
-- success
update t set d=7 where id=5;
-- success
update t set d=17 where id=15;

上述语句的加锁范围为：(5,10]和(10,15)。

向右扫描，找到 id=15，加锁 (10,15]，此时是范围查询，所以没法使用优化2。

唯一索引范围锁 bug

session A 是一个范围查询，按照原则 1 的话，应该是索引 id 上只加 (10,15]这个 next-key lock，并且因为 id 是唯一键，所以循环判断到 id=15 这一行就应该停止了。

但是在实现上，InnoDB 会往前扫描到第一个不满足条件的行为止，也就是 id=20。而且由于这是个范围扫描，因此索引 id 上的 (15,20]这个 next-key lock 也会被锁上。