前言:接着上篇文章说,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。
普通字段
普通字段查询,会查询全表,这里锁的话就会锁住主键的所有区间。