间隙锁 临键锁

间隙锁 临键锁

默认情况下，InnoDB在 REPEATABLE READ事务隔离级别运行，InnoDB使用 next-key 锁进行搜 索和索引扫描，以防止幻读。

• 索引上的等值查询(唯一索引)，给不存在的记录加锁时, 优化为间隙锁 。

• 索引上的等值查询(非唯一普通索引)，向右遍历时最后一个值不满足查询需求时，next-key lock 退化为间隙锁。

• 索引上的范围查询(唯一索引)--会访问到不满足条件的第一个值为止。

next-key 是一种锁的算法 就是record lock和gap lock的组合

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)

（注意：虽然是开区间，但是当对age=10但id>10的数据也算在区间内，age=20但id<20同理，所以这里我们要记住 间隙锁与临键锁在对于普通索引时 锁的是非聚集索引b+树的叶子节点区间 ）

逻辑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 退化成 行锁 或 间隙锁