MySQL8 间隙锁在11种情况下的锁持有情况分析

测试环境及相关必要知识

测试环境为mysql 8 版本

间隙锁（Gap Lock）：用于锁定索引范围之间的间隙，防止其他事务在此间隙中插入新记录。间隙锁主要用于防止幻读问题。

在可重复读的隔离级别下默认打开该锁机制，解决幻读问题，也可手动修改配置文件关闭该锁机制，该锁机制为innodb自动决定间隙范围上锁，无需SQL显式声明锁。

表结构如下
在这里插入图片描述
数据如下

Mysql 运行中事务可以在表 information_schema.INNODB_TRX 中查看

Mysql 运行中事务持有的锁可以在表 performance_schema.data_locks 中查看
（一行分割两张图了）
在这里插入图片描述

Mysql 运行中事务等待持有锁的事务可以在表 performance_schema.data_lock_waits 中查看
在这里插入图片描述

LOCK MODE（锁模式）将出现的锁模式值和解释

S：共享锁（Shared Lock），允许其他事务获取相同对象的共享锁，但不允许排它锁。
X：排它锁（Exclusive Lock），在事务持有排它锁期间，其他事务无法获取相同对象的共享锁或排它锁。
IS：意向共享锁（Intention Shared Lock），表示事务准备获取一个表中某些行的共享锁。
IX：意向排它锁（Intention Exclusive Lock），表示事务准备获取一个表中某些行的排它锁。
SIX：共享意向排它锁（Shared Intention Exclusive Lock），表示事务准备获取一个表中某些行的共享锁，但也准备获取这些行的排它锁。
REC_NOT_GAP是MySQL InnoDB存储引擎中的一个特殊锁模式，用于锁定记录（行）而非间隙（gap）

注意：接下来将看到很多意向锁，意向排他锁并不等于持有了排它锁，只是表明有持有该锁的意向！
LOCK TYPE(锁类型)可能出现的是RECORD （行锁）、TABLE（表锁）

下面的各种情况下MySQL InnoDB锁持有情况的结构是先展示SQL，后展示持有锁的截图
持有锁截图中重要的字段为INDEX_NAME 索引名 LOCK_TYPE 锁类型 LOCK_MODE 锁模式 LOCK_STATUS 锁状态 LOCK_DATA 锁数据

走唯一索引的主键id+指定值+有值情况

begin ;
select * from t_gap_demo where id  = 3 for update ;

结果：对id=3的行记录上了行排他锁，当前表的意向排他锁
在这里插入图片描述

走唯一索引的主键id+指定值+无值情况

begin ;
select * from t_gap_demo where id  = 4 for update ;

插入id=4数据被阻塞，证明(3,]间隙已被锁定，插入id=8数据成功，插入id=2数据成功
结果：对数据7持有了排他锁、间隙锁范围为(3,7] 左开右闭原则
在这里插入图片描述

走唯一索引的主键id+指定范围+有值情况

begin ;
select * from t_gap_demo where id  between 3 and 8 for update ;

结果：持有了主键索引上的1、3、7、15四行的行锁且无间隙锁，持有表排它锁意向锁，额外还持有了普通索引age上的所有值的排它锁及间隙锁（0到正无穷都锁住了，这个字段没有负数）
这个结果挺让人震惊的，这句SQL居然持有了这么多锁，而且我测试插入id=2,age=1的记录是无法插入的，一直被阻塞。证明age整个范围的间隙锁都是被锁住了的，无法插入任何数据。
在这里插入图片描述
额外我还测试了一下该情况下update语句的锁情况

begin ;
update   t_gap_demo set age = 1 where id  between 3 and 8  ;

结果是锁住了(3,7] (7,15]间隙锁，3、7、15行锁，附带基本的表排他锁意向锁，至于为啥select语句时锁住了整个age间隙，还需要后续研究，有懂的大佬评论一下
在这里插入图片描述

走唯一索引的主键id+指定范围+无值情况

begin ;
select * from t_gap_demo where id  between 18 and 28 for update ;

id = 14可以插入，id = 16无法插入，阻塞
结果：持有了排它锁，锁住最后一行记录15到正无穷
在这里插入图片描述

begin ;
select * from t_gap_demo where id  between 10 and 12 for update ;

于是我又好奇假设这个区间无值，但是属于某个间隙呢
结果：持有了这个范围所在间隙的间隙锁 (7,15]，15这个行记录的排它锁，表意向排它锁
在这里插入图片描述

走普通索引的age+指定值+有值情况

begin ;
select * from t_gap_demo where age  = 3 for update ;

结果：主键id=1的记录行锁排它锁锁定、普通索引上3,1 6,3全部锁定行记录加间隙，表意向排它锁
在这里插入图片描述

走普通索引的age+指定值+无值情况

begin ;
select * from t_gap_demo where age  = 4 for update ;

结果：持有了这个不存在的id处于的这个间隙的间隙锁和两个行记录的排它锁，表意向排它锁
在这里插入图片描述

走普通索引的age+指定范围+有值情况

begin ;
select * from t_gap_demo where age  between 20 and 40 for update ;

结果：持有了整个范围涉及的间隙的间隙锁、涉及行的排他锁，涉及行记录的主键记录的排它锁（不带间隙锁），表意向排它锁
在这里插入图片描述

走普通索引的age+指定范围+无值情况

begin ;
select * from t_gap_demo where age  between 78 and 88 for update ;

结果：持有age记录最大值到正无穷的间隙锁，表意向排它锁
在这里插入图片描述

拓展：非索引字段范围锁定

begin ;
select * from t_gap_demo where age  between 1 and 15 for update ;

结果：持有了整个id范围内所有行记录的排它锁和所在区间（包括左右无穷）的间隙锁，尝试插入id=2，id=16记录都是阻塞状态
在这里插入图片描述

总结

经过各种情况下事务对数据的加排它锁测试发现，mysql 8 innodb引擎会对涉及的数据和间隙都加上排他、间隙锁，甚至在普通索引时还会对涉及数据的主键索引也加上不带间隙锁的排它锁（X,REC_NOT_GAP），在非索引字段范围锁定时会对整个数据和间隙锁定，这告诉我们在使用时要注意，追求性能时少使用悲观锁，尽量避免锁竞争、避免死锁，尽量走主键索引或索引、尽量少锁定资源或只锁定最小范围数据、尽量精确操作某行数据，尽量让事务执行时间短，尽量避免同时事务并发操作同间隙内数据以避免死锁。