MySql行锁变表锁，性能下降？间隙锁（X,GAP），行锁（X,REC_NOT_GAP），区间锁（X）带你进阶。

前言：接着上篇文章说，mysql行锁是跟着索引走的，会根据不同的索引类别添加不同的锁，下面开始介绍。

准备工作

先创建一张用户表，id为主键，但是不要让id自动增长，id值不要全部连续。

CREATE TABLE `t_user` (
  `id` int NOT NULL,
  `name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL,
  `age` int DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `age_idx` (`age`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3;

BEGIN;
INSERT INTO `t_user` (`id`, `name`, `age`) VALUES (1, '碧凡', 21);
INSERT INTO `t_user` (`id`, `name`, `age`) VALUES (2, '夏菡', 21);
INSERT INTO `t_user` (`id`, `name`, `age`) VALUES (3, '曼香', 23);
INSERT INTO `t_user` (`id`, `name`, `age`) VALUES (4, '若烟', 27);
INSERT INTO `t_user` (`id`, `name`, `age`) VALUES (5, '半梦', 21);
INSERT INTO `t_user` (`id`, `name`, `age`) VALUES (6, '雅绿', 24);
INSERT INTO `t_user` (`id`, `name`, `age`) VALUES (7, '冰蓝', 22);
INSERT INTO `t_user` (`id`, `name`, `age`) VALUES (8, '灵槐', 18);
INSERT INTO `t_user` (`id`, `name`, `age`) VALUES (11, '翠风', 19);
INSERT INTO `t_user` (`id`, `name`, `age`) VALUES (12, '香巧', 22);
INSERT INTO `t_user` (`id`, `name`, `age`) VALUES (13, '代云', 27);
INSERT INTO `t_user` (`id`, `name`, `age`) VALUES (14, '友巧', 26);
INSERT INTO `t_user` (`id`, `name`, `age`) VALUES (15, '听寒', 22);
INSERT INTO `t_user` (`id`, `name`, `age`) VALUES (20, '凌萱', 21);
COMMIT;

单字段主键索引查询

主键等值查询已存在的数据

# 开启事务
begin;
# 给id=15的这条记录添加排它锁
select * from t_user where id = 15 for update;
# 查看加锁情况 注意不要提交事务
select * from performance_schema.data_locks;

可以看到存在一个表锁和一个行锁，不同的是这边表锁是意向锁。关于意向锁这里不做解释，需要注意的是作者用的mysql版本为8.0.28。
重点关注LOCK_MODE和LOCK_DATA两个字段的曲直。LOCK_MODE代表锁的类型，LOCK_DATA代表被加锁的数据（索引项）。

LOCK_MODE

IX：意向排它锁。上述案例中，给表添加了一个意向排它锁。当其他事务要对全表的数据进行加锁时，那么就不需要判断每一条数据是否被加锁了。
X,REC_NOT_GAP：X代表排他锁，REC_NOT_GAP代表行锁。综合起来就是对这条数据（索引项）添加了行级排他锁，别的事务都不能再对其添加任何锁。

LOCK_DATA

NULL：因为是表锁，锁定的是全表的数据，则显示为null。
6：因为是根据id主键索引查询的，索引6代表锁定id=6的数据（索引项）。

主键等值查询不存在的数据1

# 先提交上次的事务
commit;
# 开启事务
begin;
# 根据主键查询一条不存在表中的数据
select * from t_user where id = 666 for update;
# 查看加锁情况
select * from performance_schema.data_locks;

此时LOCK_MODE出现了一个新值X；LOCK_DATA也出现了一个新值supremum pseudo record，翻译过来为“上确界伪纪录”。
X：介于行锁和间隙锁之间的一种锁，我暂且先叫其区间锁（前开后闭），即id索引范围外的数据都将被加锁。
supremum pseudo record：上确界伪记录。即在id索引范围外的数据。上述案例中，id索引值的最小值为1，最大值为20，所以id索引的范围为[1，20]。而“上确界伪记录”代表小与1和大于20的id索引，即（-∞，1）∪（20，+∞）。此时其他事务无法插入id在这个区间的数据，也无法更新现有数据的id到这个区间。

# 重新打开一个客户端窗口 注意 不能提交上述事务
# 开启事务
begin;
# 插入一条存在于"上确界伪记录"范围内的数据
insert into t_user value(512, '幻读测试', 100);
# 结果 阻塞 然后超时 失败
# 修改一条现有的数据到"上确界伪记录"范围
update t_user set id = 256 where id = 20
# 结果 阻塞 然后超时 失败

以上操作就是X（区间锁）锁在发挥作用，锁冲突导致操作无法成功。细心的同学会发下述操作就可以成功。

# 重新打开一个客户端窗口 还是基于id = 666 这次锁定 注意不能提交这个事务
# 开启事务
begin;
# 插入一条不存在"上确界伪记录"范围内的数据
insert into t_user value(18, '张三', 21);
# 修改一条不存在的数据到"上确界伪记录"范围
update t_user set id = 256 where id = 512;
# 发现上述 sql可以正常执行

通过上述两个现象，就能很好的理解LOCK_MODE = X（区间锁），以及supremum pseudo record（上确界伪记录）代表的含义了。（上述案例还不能体现X的前开后闭，后续会讲到。）

主键等值查询不存在的数据2

或许有的同学通过主键查询不存在的数据出现了以下情况，也可能呢个是两个情况都考虑了一下。那我感觉这位同学挺细心的，继续看另一种情况。

# 先提交所有未提交的事务 确保没有其他锁干扰
commit;
# 开启事务
begin;
# 根据主键查询一条不存在表中的数据 但是在 id 在主键值范围内
select * from t_user where id = 18 for update;
# 查看加锁情况
select * from performance_schema.data_locks;

LOCK_MODE又出现了新的值X,GAP。X代表排他锁；GAP代表间隙锁（前开后开）。具体通过操作以及现象来解释间隙锁。
LOCK_DATE的取值为20，结合LOCK_MODE的取值代表着id值在20之前这个间隙的索引被加锁了，即（15，20）这个区间，因为是前开后开区间，所以也叫间隙。

# 重新打开一个客户端窗口 注意 不要提交 上次开启的事务
# 开启事务
begin;
# 插入一条 间隙内的数据
insert into t_user value(16, '幻读测试', 20);
# 结果 阻塞 然后超时 失败
# 更新一条数据到 间隙内
update t_user set id = 16 where id = 15;
# 结果 阻塞 然后超时 失败

通过上述现象，能够帮助我们理解LOCK_MODE取不同值的情况锁定的数据（索引项）；了解了行锁（LOCK_MODE=X,REC_NOT_GAP） ，区间锁（LOCK_MODE=X），间隙锁（LOCK_MODE=X,GAP）。需要注意的是，区间锁是作者为了方便区分而自己命名的，对外可不能之间说。后续还有根据主键索引范围查找情况，注意临界值情况，这个过程必须要亲自体验一遍，并且带着答案去执行，这样才有效果。

主键范围查询

主键等值查询，范围查询时情况则比较复杂：
8.0.17 版本是前开后闭，而 8.0.18 版本及以后，修改为了前开后开区间;

主键临界值范围查询

主键 <= 最大临界值 查询时，8.0.17 会锁住下一个 next-key 的前开后闭区间，而 8.0.18 及以后版本，修复了这个 bug。（备注：实测并没有修复）
例如，对小于等于最大临界值的数据加锁。此时相当于锁表，无法插入后修改任意一条数据。

begin;
# 对小于等于临界值数加锁
select * from t_user where id <= 20 for update;
# 查看加锁情况
select * from performance_schema.data_locks;

结果展示。思考：为什么会对id大于20的数据也加锁？

例如，对大于等于最小临界值的数据加锁。可以看到对id=1对数据添加了行锁，此时只能插入小于最小id的数据，表中已有数据无法修改。

# 开启事务
begin;
# 给id=15的这条记录添加排它锁
select * from t_user where id >= 1 for update;
# 查看加锁情况
select * from performance_schema.data_locks;

结果展示

非主键唯一索引

非主键唯一索引等值查询，数据存在，for update 是会在主键加锁的，而 for share 只有在走覆盖索引的情况下，会仅在自己索引上加锁;
非主键索引等值查询，数据不存在，无论是否索引覆盖，相当于一个范围查询，仅仅会在非主键索引上加锁，加的还是间隙锁，前开后开区间;
在非主键唯一索引范围查询时，不是覆盖索引的时候，会对相应的范围加前开后闭区间，并且如果存在数据，会对对应的主键加行锁;
在非主键唯一索引范围查询时，如果是覆盖索引时，会对所有的后闭区间对应的主键，加行锁;
在非主键唯一索引加锁时，还是存在 next-key 锁住下一个区间的 bug。

普通索引

普通索引等值查询，因为不能确定唯一性，所以即使定位到记录，也是会向后查询，直到查询到不为该值的记录，从而锁定该值的区间;

普通索引的锁也是加载该索引上的，如果涉及到存在的记录，会对该主键加行锁;

普通索引的范围查询，同样出现 next-key 查询下一个区间的 bug。

普通字段

普通字段查询，会查询全表，这里锁的话就会锁住主键的所有区间。