Mysql学习笔记-临键锁实验

前言
昨天同事跟我聊到一个问题:InnoDB里面间隙锁锁住的数据可以update么?我们经常都说间隙锁是InnoDB在RR隔离级别下防止幻读的一种处理手段。它可以防止数据在间隙范围中insert数据,但是对于update?很多资料都没有明显说明,今天咱们就通过几个实验来揭开间隙锁的神秘面纱。


mysql命令

  • 查看自动提交事务开关状态:show variables like ‘autocommit’;
  • 关闭自动事务:set autocommit = 0;
  • 查看事务隔离级别:show variables like ‘transaction%’;
  • 设置事务隔离级别:set session transaction isolation level read committed;
  • 查看当前服务器锁情况:select * from performance_schema.data_locks;

环境

Mysql Server 8.0.28 / InnoDB / RR

实验表

CREATE TABLE `user` (
  `id` bigint NOT NULL AUTO_INCREMENT,
  `card` varchar(18) DEFAULT NULL,
  `name` varchar(20) DEFAULT NULL,
  `sex` int DEFAULT NULL,
  `age` int DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
初始化数据

image


下面让我们开启实验

实验一:foru update查询下,主键更新

sql
-- 事务一:主键写锁查询,左开右开
select * from user where id > 3 and id < 8 for update;
-- 事务二:范围内更新,根据主键更新值
update user set name = 1 where id = 7;

实验结果(锁冲突等待)

image

结果分析:
  • 红框部分为更新事务,持有IX锁,等待行锁获取,行锁加锁对象为主键索引7;
  • 非红框部分为查询事务,范围内记录均持有X锁(临键锁),锁定的都是LOCK_DATA值前面的间隙和值本身,但是主键索引为8的数据,不包含本身。
示意图如下

image

ps:
x:间隙锁(左开右闭),即临键锁
x,GAP:间隙锁(双端开口)
X,REC_NOT_GAP:记录锁即行锁

实验二:foru update查询下,不使用索引

sql
-- 事务一:非索引写锁查询,左开右闭
select * from user where age > 18 and age <= 40 for update;

-- 事务二:非索引更新,此条记录主键:6,age:33
update user set sex = 1 where name ='吴八';

实验结果(锁冲突等待)

image

结果分析:

1、由于查询和更新均未使用索引,导致锁全表。
2、查询事务全部获取临键锁
3、更新事务由于全表锁,所以从主键最小的节点开始等待获取锁

示意图如下

image
注意:由于查询事务会把主键最小节点前面间隙也都的都锁上

实验三:辅助索引写锁查询,非索引更新

sql
-- 事务一:二级索引写锁查询,左开右闭
select * from user where card > '0002' and card <= '0008' for update;3
-- 事务二:非索引更新,此条记录主键:3,card:0005
update user set sex = 1 where name ='王五';

实验结果(锁等待冲突)

image

结果分析
  • 查询事务对于辅助索引增加临键锁,对于辅助索引对应的主键增加行锁。
  • 由于我们查询区间为左开右闭,查询事务还会给临界索引数据的后一个节点加上临键锁。然后由于000x不在查询范围内,所以主键锁定不包含000x辅助索引对应的主键。
  • 更新事务由于无索引,走全表,从主键最小节点开始逐步加锁,1和2都获取到了,3开始等待。
示意图如下:

image


小结:
1、间隙锁范围内部数据其他事务需要等待锁,否则无法修改、插入、删除。
2、对于查询或者更新条件包含主键索引的数据会加行锁或临键锁(行锁+间隙锁)。
3、对于查询或者更新条件为辅助索引的数据会加行锁或临键锁(行锁+间隙锁)。对于左开右闭区间的索引范围查询更改,间隙锁边界会后移一个节点,参考实验二。
4、对于查询或者更新条件不包含索引的数据会锁全表(所有行记录的临键锁),等待事务会从主键最小节点逐步尝试加锁,获取不到则进入等待。
5、等值查询带索引使用行锁,范围查询或者无索引情况下,使用临键锁,全表锁也是体现在每一行上面加临键锁。
6、lock in share mode和for update类似,对于上述例子基本没有区别,唯一区别只是查询事务获取的意向锁由IX变为IS。(如果两个都是查询的话,lock in share mode不会被阻塞,共享锁,也就是读锁可以重入)。

ps:大家常说的 next-key lock其实就是临键锁,左边的间隙锁 + 右边行的行锁。


还有很多场景没有去做实验,一是太多实验写起来费事,看起来也费事,二是我觉得绝大场景都可以通过以上三个例子推导出来,比如查有索引、更新也有索引,那就是锁定指定的索引,如果是二级索引还需要锁定对应的主键索引。如果大家对间隙锁还有不明白的,可以留言一起讨论下。有需要实验的也可以留言给我,我帮你实现图片!

愿每一个人都能找到自己心中的方向!

关注我的公众号
image

页面下部广告