行锁/间隙锁/Next-key Lock

官网文档地址：https://dev.mysql.com/doc/refman/8.0/en/innodb-locking.html

参考了 极客时间Mysql45讲 第20讲 21讲 30讲

record Lock：行锁，防止其它事务 新增/更新/删除 该行

gap Lock：间隙锁，锁住一个区间，防止其它事务新增记录

(间隙锁是不分排他锁和共享锁，它只阻塞写入，所以两个事务分别持有相同的间隙锁后，其中某个事务再再间隙里面新增记录，此时会死锁)

 Next-key Lock：间隙锁+行锁

注意：所有锁都是针对于索引的

1 准备数据

表User，id为主键，age为数值型；索引：id主键唯一索引，age普通索引

CREATE TABLE `emp` (
  `id` int NOT NULL AUTO_INCREMENT,
  `name` varchar(255) DEFAULT NULL,
  `age` int DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_user_age` (`age`)
) ENGINE=InnoDB AUTO_INCREMENT=111 DEFAULT CHARSET=utf8;

表的初始化数据：

id age name
1   1   1
5   5   5
10  10  10
15  15  15 
20  20  20
25  25  25

数据库版本：8.0.19  （注意，版本不同，执行结果可能也不同，但是越新的版本肯定优化的越符合逻辑）

2 加锁原则

查找索引，满足条件的索引都会加Next-key Lock

实际操作过程中

发现了一些规律：

1 唯一索引由于唯一性，查找所有满足条件的索引

2 普通索引查到满足条件的还不能停，需要找到下一个不满足的为止

3 条件里仅等值查询（例如id = 20）且该值不存在（20 不存在），此时会增加间隙锁

4 普通索引 + 排它锁的情况下，会回表增加符合条件的主键索引上的行锁（共享锁+覆盖索引时不会回表）

和锁范围优化：（退化成行锁或间隙锁，目的是减少锁的范围）

唯一索引：按照符合条件的逻辑，将Next-key锁退化成行锁

普通索引：按照未知的逻辑，将Next-key锁退化成间隙锁

3 测试数据

3.1 主键

3.1.1 主键存在时

select  * from user where id = 15 for update;

实际结果：

 行锁（id）： 15 

分析：

根据逻辑1 ，定位到符合条件的第一个索引id = 15，增加锁Next-key Lock：(10,15]，由于是唯一索引，不可能有重复的，固不需要继续找下一个索引（id = 20）；

优化 ，没有必要增加间隙锁，优化成只有 id = 15 的行锁（可查看官方文档）

测试：

update user set name =‘xxx’ where id = 15；// 有id= 15的行锁，阻塞

insert into user value (14,14,'14') ；// 不阻塞，由于没有间隙锁，不会阻塞id在10到15 和 15到20 的 insert的语句

update user set name =‘xxx’ where age = 15；// 阻塞，由于会更新id = 15 的这一行

select  * from user where id > 15 for update；

实际结果： + 代表最大值  -代表最小值 都是虚拟值

间隙锁（id）：(15,20) (20,25) (25,+)

行锁（id）：20, 25

分析：

找到符合条件的索引 20 增加锁（15，20]

找到符合条件的索引 25 增加锁（20，25]

增加剩下的间隙锁：(25,+)

测试：

update user set name =‘xxx’ where id = 20；// 阻塞 有id = 20和25的行锁

insert into user value (14,14,'14') ；// 不阻塞，由于id = 14 不在（15，+）的范围

insert into user value (14,16,'14')；// 不阻塞，不在id间隙锁范围（15，+），且age上无间隙锁

insert into user value (16,14,'14')；// 阻塞，由于id的间隙锁（15，+）

select  * from user where id >= 15 for update;

实际结果：

间隙锁（id）：(15,20) (20,25) (25,+)

行锁（id）：15 , 20, 25

分析：

根据逻辑1：找到符合条件的索引：15 ，20，25，增加锁 (10,15] (15,20] (20,25] (25,+)

优化：(10,15)间隙不符合where条件，缩小锁的范围，将(10,15] 可退化成 行锁 15

select  * from user where id < 15 for update;

实际结果：

间隙锁（id）：(-,1)(1,5) (5,10) (10,15) 

行锁（id）：1,5,10

分析:

逻辑1，找到符合条件的索引：1，5，10，15 ，增加锁(-,1] (1,5]  (5,10]  (10,15]

优化， id = 15 不符合where 条件，放宽锁的范围，(10,15] 退化成 (10,15）

（不要纠结为啥有这个优化，从结果来说，减少锁的范围，符合我们预期就对了）

select  * from user where id <= 15 for update;

实际结果：

间隙锁（id）：(-,1)  (1,5)  (5,10)  (10,15)

行锁（id）：1, 5, 10，15

分析:

逻辑1：找到符合条件的索引：1，5，10，15 ，增加锁(-,1] (1,5]  (5,10]  (10,15]

3.1.2 主键不存在时

select  * from user where id = 18 for update;

实际结果：

间隙锁（id）：（15，20）

分析：

逻辑3：由于18不存在，找不到符合条件的索引，但是找的到符合条件的间隙锁，故增加（15，20）

select  * from user where id > 18 for update;

实际结果：

间隙锁（id）：(15,20) (20,25)  (25,+)

行锁（id）：20,25

分析：

逻辑1 ：找到符合条件的索引：20，25 增加锁 (15,20] (20,25]  (25,+)

select  * from user where id >= 18 for update;

等同于 select  * from user where id > 18 for update;

 select  * from user where id < 18 for update;

实际结果：

间隙锁（id）：(-,1) (1,5) (5,10) (10,15) (15,20)

行锁（id）：1,5,10,15

分析：

逻辑1 ：找到符合条件的 1  5 10  15 增加锁(-,1](1,5] (5,10] (10,15]  和 符合条件的间隙锁 ( 15,20)

select  * from user where id <= 18  for update;

等同于  select  * from user where id < 18 for update;

3.2  普通索引

3.2.1 普通索引存在时

select  * from user where age = 15 for update; 

实际结果：

间隙锁（age ）：(10,15)  (15,20)

行锁（age ）：15

行锁（id）：15

分析：

逻辑2 ：找到符合条件的索引age = 15 ，增加锁 （10，15]

由于是普通索引，可能存在重复的，故需要找到不符合条件的索引 age =20，增加锁（15，20] 

优化：age = 20 的行锁可以去掉，(15,20] 退化成间隙锁 (15,20)

逻辑4 ：对符合条件的 age =  15 进行回表，增加主键行锁， id = 15

select  * from user where age > 15 for update; 

实际结果：

间隙锁（age ）：(15,20)  (20,25)  (25,+)

行锁（age ）：20 25

行锁（id）：20 25

分析：

找到符合条件的索引20 25  增加锁(15,20] (20,25]   (25,+)

select  * from user where age >= 15 for update; 

实际结果：

间隙锁（age ）：(10,15) (15,20) (20,25) (25,+)

行锁（age ）：15 20 25

行锁（id）：15 20 25

分析：

逻辑2 ：找到符合条件的索引15 20 25  增加锁 (10,15] (15,20] (20,25]  对剩下的增加间隙锁 (25,+)

select  * from user where age < 15 for update; 

实际结果：

间隙锁（age ）：(-,1)  (1,5)  (5,10)  (10,15)

行锁（age ）：1 5 10 15

行锁（id）：1 5 10

分析：

逻辑2 ：找到符合条件的索引1 5 10  增加锁 (-,1] (1,5] (5,10] 

找到不符合条件的索引 15 增加锁 (10,15]

逻辑4 ：回表对符合条件的主键增加行锁 1 5 10

select  * from user where age <= 15 for update; 

实际结果：

间隙锁（age ）：(-,1) (1,5) (5,10) (10,15) (15,20)

行锁（age ）：1 5 10 15 20

行锁（id）：1 5 10 15

分析：

逻辑2 ： 找到符合条件的索引1 5 10 15 增加锁 (-,1] (1,5] (5,10] (10,15]

找到不符合条件的索引 20 增加锁 (15,20]

逻辑4 ：回表对符合条件的主键增加行锁 1 5 10 15

3.2.2 普通索引不存在时

select  * from user where age = 18  for update; 

实际结果：

间隙锁（age ）： (15,20)

select  * from user where age > 18 for update; 

实际结果：

间隙锁（age ）：(15,20)  (20,25)  (25,+)

行锁（age ）：20 25

行锁（id）：20 25

分析：

逻辑2 ：找到符合条件的索引20 25  增加锁(15,20] (20,25]  对剩下的增加间隙锁 (25,+)

逻辑4：回表增加主键行锁：20 25

select  * from user where age >= 18 for update; 

等同于 select  * from user where age > 18 for update; 

select  * from user where age < 18 for update; 

实际结果：

间隙锁（age ）：(-,1) (1,5) (5,10) (10,15) (15,20)

行锁（age ）：1  5 10  15  20

行锁（id）：1 5 10 15

分析：

逻辑2 ：找到符合条件的索引1 5 10  15 增加锁 (-,1]  (1,5]  (5,10]  (10,15]

找到不符合条件的索引 20  增加锁 (15,20]

逻辑4 ：回表对符合条件的主键增加行锁 1 5 10 15

select  * from user where age <= 18 for update; 

等同于 select  * from user where age <18 for update; 

4 总结

加锁逻辑关键点：

1 按照 Next-key Lock 扫描索引 进行加锁（锁住行锁和其前面的间隙锁）

2 唯一索引只会对符合条件的索引加锁

3 普通索引：对符合条件的索引加锁且会回表去增加主键的行锁，扫描到下一个不满足条件的索引

4 对于等值不存在的条件（where age = 10，但不存在该记录）会加间隙锁

5 会有各种优化去缩小锁的范围，将Next-key Lock 退化成 行锁 或 间隙锁