MySql行锁变表锁,性能下降?间隙锁(X,GAP),行锁(X,REC_NOT_GAP),区间锁(X)带你进阶。

本文详细介绍了MySQL中行锁如何根据索引类型进行加锁,包括主键等值查询时存在和不存在数据的情况,以及非主键唯一索引和普通索引的加锁行为。通过实例展示了不同查询如何影响锁的模式和数据范围,揭示了间隙锁、区间锁和行锁的概念。

摘要生成于 C知道 ,由 DeepSeek-R1 满血版支持, 前往体验 >

前言:接着上篇文章说,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。

普通字段

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

评论 1
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值