在开始本篇内容之前，你应当知道：本篇内容都是以篇首列出的表为基础进行测试的，MySQL 版本为 5.7.25，隔离级别为可重复读。如果有语句在这张表中添加或修改了值，应当复原后再继续下一轮测试。读者可以根据文章内容自行测试、验证。读者亦可以将文中的测试方法用于测试其他数据库，以确定其他数据库中加锁的规律。如有问题，欢迎在评论区交流。
你有没有遇到过这样一种情况：想给某个语句加锁，但不知道到底会影响哪几行？想给某几行记录加锁，却好像与预想中的范围不一样？所以MySQL加锁到底有没有规律，有什么规律？比如现在有如下一张表：
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);
那么，考验一下大家，给你如下语句，你能列出它们加锁的范围吗？
| 编号 | 语句 |
|---|---|
| 1 | begin; select * from t where id=15 for update; |
| 2 | begin; select * from t where id=16 for update; |
| 3 | begin; select * from t where c=15 for update; |
| 4 | begin; select * from t where id>=10 and id<=15 for update; |
| 5 | begin; select * from t where c>=10 and c<=15 for update; |
| 6 | begin; select * from t where c>=10 and c<=15 order by c desc for update; |
| 7 | insert into t values(30,10,30);begin; delete from t where c=10 limit 2; |
行锁、间隙锁、next key lock
在开始之前，先简短介绍一下「行锁」「间隙锁」「next key lock」。你应该知道，加锁需要使用 select ... for update 加写锁或使用 select ... lock in share mode 加读锁。根据锁的范围不同，形成了三种不同的锁：
行锁：如果锁加到某一行具体的记录上，比如锁加到了上表中 id 为 5 的行，那么这一行就被加了行锁。被加了行锁的行不可以被增删改。
间隙锁：如果锁加到具体的两行之间，比如锁加到了上表中 id 为 5 和 id 为 10 的两行之间，那么这一行就被加了间隙锁。被加了间隙锁后不能在索引上添加新的行。
next key lock：是一个间隙锁在前，行锁在后的锁组合。始终是左开右闭区间。也被称为临键锁。一般认为 MySQL 中以 next key lock 为加锁单位。
其实以上三种锁都是 MySQL 中 InnoDB 引擎提供的能力，如果你看得仔细，也可以发现建表语句中出现了 InnoDB 字样。虽然说是 MySQL 的加锁规则，其实上是 InnoDB 引擎的加锁规则。那么，接下来本文将以上文的七条语句为引子，从以下几个方面，为大家解密 InnoDB 的加锁逻辑：
- 对索引进行等值查询
- 对索引进行范围查询
- 对索引进行范围查询并逆序排序
- 锁范围扩大和缩小
- 特别情况
对索引进行等值查询
以下表格中，每一行代表一个时间点，越靠前的行，也就越早发生。后续都会使用这个格式讲解和举例。有些地方我省略了测试的例子，大家可以自行测试；有些测试流程涉及的语句太多，我会直接给出测试结果。
唯一索引查询不到值，测试如下：
| Session A | Session B |
|---|---|
begin; select * from t where id=16 for update; | |
update t set d=d+1 where id=15; Query OK, 1 row affected | |
update t set d=d+1 where id=20; Query OK, 1 row affected | |
insert into t values(16,16,16); (Blocked，需等待超时或使用 Ctrl + C 终结以继续执行，下文省略提示) | |
commit; 下文将默认省略最后的 commit |
注意！间隙锁只能用于阻塞插入语句，而不能阻塞更新语句，毕竟对不存在的数据执行更新就等于没更新。所以如下测试语句得出没有加间隙锁的结论是不对的：
| Session A | Session B |
|---|---|
begin; select * from t where id=16 for update; | |
update t set d=16 where id=16; Query OK, 0 rows affected | |
commit; 下文将默认省略最后的 commit |
唯一索引查询到值，测试如下：
| Session A | Session B |
|---|---|
begin; select * from t where id=15 for update; | |
insert into t values(16,16,16);Query OK, 1 row affected | |
insert into t values(14,14,14);Query OK, 1 row affected | |
update t set d=d+1 where id=15; (Blocked) |
可以得出结论：如果索引是唯一索引，且找不到查询值，对查询的值所在的间隙加间隙锁。否则，则只加行锁。
非唯一索引等值查询，测试结果如下：
| 语句 | 查询范围 | 加锁范围 |
|---|---|---|
begin; select * from t where c=15 for update; | [15, 15] | (10, 20) |
begin; select * from t where c=16 for update; | [10, 10] | (15, 20) |
结论：如果索引不是唯一索引，且找不到查询值，对查询的值所在的间隙加间隙锁。否则，还需要对查询值所在行加行锁（有例外情况，在后面特别情况中举例说明）。
对索引进行范围查询
如果使用唯一索引进行范围查询，查询范围与加锁范围如下所示：
| 语句 | 查询范围 | 加锁范围 |
|---|---|---|
begin; select * from t where id>=10 and id<=15 for update; | [10, 15] | [10, 20] |
begin; select * from t where id>=10 and id<15 for update; | [10, 15) | [10, 15] |
begin; select * from t where id>=10 and id<11 for update; | [10, 11) | [10, 15] |
begin; select * from t where id>10 and id<15 for update; | (10, 15) | (10, 15] |
begin; select * from t where id>9 and id<15 for update; | (9, 15) | (5, 15] |
begin; select * from t where id>7 and id<15 for update; | (7, 15) | (5, 15] |
得出结论：
- 如果查询的左边界正好落在一个间隙上，则锁的左边界扩展到这个间隙的左边界；否则锁的左边界与查询范围一致。
- 如果查询的右边界正好落在一个间隙上，则锁的右边界扩展到这个间隙所属的 next-key lock 右边界；否则锁的右边界扩展到下一个 next-key lock 右边界。
如果使用非唯一索引进行范围查询：
| 语句 | 查询范围 | 加锁范围 |
|---|---|---|
begin; select * from t where c>10 and c<=15 for update; | (10, 15] | (10, 20] |
begin; select * from t where c>10 and c<15 for update; | (10, 15) | (10, 15] |
begin; select * from t where c>=10 and c<15 for update; | [10, 15) | (5, 15] |
begin; select * from t where c>7 and c<13 for update; | (7, 13) | (5, 15] |
得出结论：
- 锁的左边界与查询的左边界所属的 next-key lock 左边界一致。
- 锁的右边界加锁逻辑与唯一索引一致。
另外，使用索引进行范围查询时，以 next-key lock 为单位加锁，且>或≥与<或≤的使用先后不影响加锁范围。
对索引进行范围查询并逆序排序
经测试，对于索引进行等值查询逆序排序，与对索引进行等值查询加锁逻辑一致。所以着重说明一下对索引进行范围查询逆序排序的情况。
唯一索引范围查询逆序排序，测试结果如下：
| 语句 | 查询范围 | 加锁范围 |
|---|---|---|
begin; select * from t where id>11 and id<14 order by id desc for update; | (11, 14) | (5, 15) |
begin; select * from t where id>10 and id<15 order by id desc for update; | (10, 15) | (5, 15) |
begin; select * from t where id>10 and id<=15 order by id desc for update; | (10, 15] | (5, 20) |
begin; select * from t where id>=10 and id<15 order by id desc for update; | [10, 15) | (0, 15) |
非唯一索引范围查询逆序排序，测试结果如下：
| 语句 | 查询范围 | 加锁范围 |
|---|---|---|
begin; select * from t where c>11 and c<14 order by c desc for update; | (11, 14) | (5, 15) |
begin; select * from t where c>10 and c<15 order by c desc for update; | (10, 15) | (5, 15) |
begin; select * from t where c>10 and c<=15 order by c desc for update; | (10, 15] | (5, 20) |
begin; select * from t where c>=10 and c<15 order by c desc for update; | [10, 15) | (0, 15) |
可见，无论对唯一索引还是非唯一索引进行范围查询逆序排序，加锁逻辑一致。由于是 desc 逆序排序，会先从大的值开始向小的检查，加锁顺序也如此，所以这可能是导致会给查询的左边界加多余的锁的原因（有点像对索引进行正序范围查询时，会对锁的右边界加多余行锁一样）。加锁逻辑总结如下（下述内容所谓的上下是以索引顺序排序为从上到下）：
- 对于查询的右边界来说，如果落在间隙上，则锁的右边界扩展到间隙锁的右边界，否则扩展到下一个间隙锁的右边界。
- 对于查询的左边界来说，如果落在间隙上，则锁的左边界扩展到上一个间隙锁的左边界，否则扩展到上上个间隙锁的左边界。
怎么说呢，丈二和尚摸不着头脑，很多锁看起来加的都很多余，但事实上他就是这么实现的。尤其是唯一索引的锁范围。如果说上述表格中的索引 c 的因为查询条件是 c<=15 且，索引 c 不是唯一索引，因此会担心在 c=15 和 c=20 之间又插入c=15 的记录，所以为索引 c 在 (15, 20) 这个区间加上了间隙锁。但是 id 是唯一索引，不会存在 id 在 (15, 20) 区间内出现重复的现象，但仍然加锁了。也许是为了处理逻辑上的方便，强行把这两种索引的加锁逻辑统一了。
锁范围扩大和缩小
索引字段和非索引字段一起查询
由于查询非索引字段不走索引，必须进行全表扫描，因此使用非索引字段查询，则对全表加锁。测试如下：
| Session A | Session B |
|---|---|
begin; select * from t where d=15 for update; | |
insert into t values(111,111,111); (Blocked) | |
update t set c=5 where id=5; (Blocked) |
使用索引字段和非索引字段一起查询，结果如下：
| 语句 | 范围 |
|---|---|
begin; select * from t where id=15 and d<15 for update;（查询结果为空） | [15, 15] |
begin; select * from t where c=15 and d>=15 for update;(15, 15, 15) | (10, 20) |
begin; select * from t where c>10 and c<=15 and d!=1 for update;(15, 15, 15) | (10, 20] |
可以看出：如果使用索引字段和非索引字段一起查询，则只会对有索引的字段所在行或周围的间隙加锁，没有索引的字段只会用作过滤条件，不影响锁范围。加锁方式遵从对索引进行等值查询和范围查询的加锁逻辑。所以 begin; select * from t where c>10 and c<=15 and d!=1 for update; 与 begin; select * from t where c>10 and c<=15 for update; 加锁范围一致。
导致锁范围扩大的情况
无论索引类型，如果删除锁的边界值，会扩大间隙锁的范围。测试如下：
| Session A | Session B |
|---|---|
begin; select * from t where c=10 for update; （加锁范围(5,15)） | |
delete from t where id=15;Query OK, 1 row affected | |
insert into t values(15,15,15); (Blocked) | |
insert into t values(19,19,19); (Blocked) |
允许修改锁的边界值使间隙锁范围扩大，但不允许缩小间隙锁范围。测试如下：
| Session A | Session B |
|---|---|
begin; select * from t where c=10 for update; | |
update t set c=c-1 where c=5; Query OK, 1 row affected | |
insert into t values(6,5,6);(Blocked) | |
update t set c=c+1 where c=5; (Blocked) |
想想也很合乎逻辑，锁边界没有在索引中被加锁，所以可以被删除。锁边界被删除了，间隙就扩大了，原来的锁边界也被包含在间隙中了。
对于索引字段的范围查询，由于其锁的右边界一定被加上了行锁，所以无法扩大其右边界的范围。比如 begin; select * from t where c>10 and c<=15 for update; 的加锁范围是 (10, 20] 由于右边界加了行锁，所以不会导致扩大锁的右边界的情况。
另外，如果由于某些情况，使用了索引，却导致没走索引，也会导致锁范围扩大。比如 MySQL 判断某个索引上的范围查询需要查询的数量很大，于是直接走全表扫描了，这就导致锁被加到了整个表的范围。
对索引进行查询并使用 limit
| Session A | Session B |
|---|---|
insert into t values(30,10,30);Query OK, 1 row affected | |
begin; delete from t where c=10 limit 2; | |
insert into t values(12,12,12);Query OK, 1 row affected |
因为 MySQL 是按照扫描顺序一行一行给记录加锁的，扫描到两条数据后，发现满足返回的条件了，因此锁就只加到了第二条记录上。也就是说索引 c 的加锁范围原本应该是 (5, 15) 但变成了是 (5, 10]。这里借用 《MySQL实战45讲》中的一张图：
可知，索引进行查询并使用 limit，锁范围缩小。因此，对于删除数据的情况，我们可以使用 limit 语句，限制加锁范围，提升删除效率。对于其他情况，使用 limit 并加锁应当慎重，防止锁没有被加上的情况。
特别情况
以下这两种情况比较刁钻，且部分程度上导致了锁范围的缩小，仅供大家作为了解。
覆盖索引时，使用 lock in share mode
示例一：
| Session A | Session B |
|---|---|
begin; select * from t where c=15 for update; （加锁范围(10,15)） | |
update t set d=d+1 where id=15;(Blocked) |
示例二：
| Session A | Session B |
|---|---|
begin; select id from t where c=15 for update; （加锁范围(10,15)） | |
update t set d=d+1 where id=15;(Blocked) |
示例三：
| Session A | Session B |
|---|---|
begin; select id from t where c=15 lock in share mode; （加锁范围(10,15)） | |
update t set d=d+1 where id=15;Query OK, 1 row affected |
经观察，可以发现：覆盖索引，使用 lock in share mode加锁，则只对该非唯一索引的字段加锁。
在《MySQL实战45讲》中是这么解释的：「因为 lock in share mode 是读操作，只锁覆盖索引，但是如果是 for update 就不一样了。 执行 for update 时，系统会认为你接下来要更新数据，因此会顺便给主键索引上满足条件的行加上行锁。」
非唯一索引进行范围查询并加锁
示例一：
| Session A | Session B |
|---|---|
begin; select * from t where c>10 and c<=15 for update; （加锁范围(10,20]） | |
update t set d=d+1 where id=20;Query OK, 1 row affected | |
update t set d=d+1 where id=15;(Blocked) | |
update t set c=d+1 where id=15;(Blocked) |
示例二：
| Session A | Session B |
|---|---|
begin; select * from t where c>10 and c<15 for update; （加锁范围(10,15]） | |
update t set d=d+1 where id=15;Query OK, 1 row affected |
示例三：
| Session A | Session B |
|---|---|
begin; select * from t where c>=10 and c<=15 order by c desc for update; （加锁范围(0,20)） | |
update t set d=d+1 where id=5;Query OK, 1 row affected （注意，加锁范围是在 c 字段索引上加 (0, 20) 的锁。如果把这里 id 字段换成 c 字段，是无法更新的） | |
update t set d=d+1 where id=15; (Blocked) |
经过观察总结，这一类情况，对于最后一次访问到的记录，都不会给主键加锁。因此依然可以使用主键更新其他字段。具体原因不详。
结尾
现在再回头看看篇首给出的几条语句，如果遮住下面的加锁范围，你能写出正确答案吗？
| 编号 | 语句 | 加锁范围 |
|---|---|---|
| 1 | begin; select * from t where id=15 for update; | [15, 15] |
| 2 | begin; select * from t where id=16 for update; | (15, 20) |
| 3 | begin; select * from t where c=15 for update; | (10, 20) |
| 4 | begin; select * from t where id>=10 and id<=15 for update; | [10, 20] |
| 5 | begin; select * from t where c>=10 and c<=15 for update; | (5, 20] |
| 6 | begin; select * from t where c>=10 and c<=15 order by c desc for update; | (0, 20) |
| 7 | insert into t values(30,10,30);begin; delete from t where c=10 limit 2; | (5, 10] |
本文内容主要参考自《MySQL实战45讲》
往期文章
什么是redo log？redo log 如何保证数据库不丢数据的？（MySQL两阶段提交详解） - 掘金 (juejin.cn)
