MySQL是怎么加行级锁的？

假设有一张表 user，只有三个字段 id (主键索引) 、 age(非唯一索引)和name(无索引)。表中现有数据：

id: 10, age: 10_v1, name: aaa
id: 11, age: 10_v2, name: bbb
id: 20, age: 20, name: ccc
id: 30, age: 30, name: ddd

1. 唯一索引等值查询

1.1 等值查询存在

1	`select * from user where id = 10 for update`

先给(-∞, 10]加上next-key锁，然后退化成id = 10处的记录锁

1.2 等值查询不存在

1	`select * from user where id = 15 for update`

先给(11, 20]加上next-key锁，然后退化成(11, 20)的间隙锁

2. 唯一索引范围查询

1	`select * from user where id >=20 and id < 25 for update`

id = 20的记录锁：从(11, 20]的next-key锁退化而来
(20, 30)的间隙锁：从(20, 30]的next-key锁退化而来

1	`select * from user where id > 15 and id <= 30 for update`

(20, 30]的next-key锁
(11, 20]的next-key锁

3. 非唯一索引等值查询

3.1 等值查询存在

1	`select * from user where age = 10 for update`

(-∞, 10_v1]的next_key锁
(10_v1, 10_v2]的next_key锁
(10_v2, 20)的间隙锁
id = 10和id = 11的记录锁

[!CAUTION]

由于这里是select *，所以会触发回表，去查找主键索引的B+树，也就会给id = 10和id = 11这两条记录加上记录锁

如果是select id且用的不是for update而是lock in share mode，会出现索引覆盖，不会触发回表，也就不会在主键上加锁

3.2 等值查询不存在

1	`select * from user where age = 15 for update`

(10_v2, 20)的间隙锁

4. 非唯一索引范围查询

1	`select id from user where age >= 10 for update`

(-∞, 10_v1]的next-key锁

[!CAUTION]

这里貌似很反常识：为什么比10要小的区间也要锁起来呢？这是因为age不是唯一索引，如果不锁这个区间，那么就很有可能出现一条：id = 9(< 10), age = 10_v0的记录插进来，这时候再执行这条语句就会出现三条记录，导致幻读
(10_v1, 10_v2]的next-key锁
(10_v2, 20]的next-key锁
(20, 30]的next-key锁
(30, +∞]的next-key锁
id = 10, 11, 20, 30的记录锁（虽然发生了索引覆盖，但是是for update，系统会认为你接下来要更新数据，因此会顺便给主键上满足条件的行加锁）

1	`select * from user where age >= 10 and age < 15 for update`

(-∞, 10_v1]的next-key锁
(10_v1, 10_v2]的next-key锁
(10_v2, 20]的next-key锁
id = 10, 11的记录锁
[!CAUTION]

注意，这里并没有退化成(10_v2, 20)的间隙锁，而是依然是(10_v2, 20]的next-key锁；所以如果此时有另一个事务想要执行：insert into user values (16, 16, eee)也会被阻塞，这似乎也很反直觉……

为什么不像等值查询那样退化？
你可能会问：“引擎明明已经判断出 20 >=15 了，为什么不把 20上的锁释放掉，只留间隙锁？”

这是因为 MySQL 在代码实现上，“等值查询”和“范围查询”走的是不同的加锁逻辑分支：
1. 等值查询 (Equality Search)：
  - MySQL 专门做了一个优化：如果查找的是 age = 20，当扫描到 age = 30 时，发现值不匹配，判定这是边界，且搜索类型是等值，于是将 Next-Key Lock 专门退化为 Gap Lock。
2. 范围查询 (Range Search)：
  - 对于 age >= 10 AND age < 15，MySQL 将其视为一个范围扫描。
  - 在非唯一索引上，MySQL 的设计原则是“宁可多锁，不可漏锁”。当扫描到 age = 20 时，虽然它不满足 < 15，但它作为扫描停止的边界，InnoDB 并没有为“非唯一索引的范围查询”做类似于等值查询的那种“退化优化”。
    - 我的猜想：可能是因为在单边的范围查询如：上面的age >= 10的情况下，锁全都是next-key锁，那么在这种双边的范围查询就不特殊处理了，和单边范围查询保持一致，也是全用next-key锁，处理起来更简便一些

因此要记住：非唯一索引和主键索引的范围查询的加锁有所不同，不同之处在于非唯一索引范围查询，next-key lock 不会退化为间隙锁和记录锁。

5. 没有加索引的查询

如果select for update查询语句，没有使用索引列作为查询条件，或者查询语句没有走索引查询，导致扫描是全表扫描。那么，每一条记录的索引上都会加上next-key锁（主键索引，因为扫描的是主键的B+树），这样就相当于锁住全表。