Mysql学习笔记-临键锁实验

﹏ヽ暗。殇╰゛Y 2023-09-29 15:56 18阅读 0赞

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

--------------------

## 0|1mysql命令 ##

*  查看自动提交事务开关状态：show variables like 'autocommit';
 *  关闭自动事务：set autocommit = 0;
 *  查看事务隔离级别:show variables like 'transaction%';
 *  设置事务隔离级别：set session transaction isolation level read committed;
 *  查看当前服务器锁情况:select \* from performance\_schema.data\_locks;

--------------------

## 0|2环境 ##

> Mysql Server 8.0.28 / InnoDB / RR

## 0|3实验表 ##

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;

## 初始化数据 ##

![01a30745fd029ad98977e58954bd8f92.png][]

--------------------

下面让我们开启实验

## 1|0实验一：foru update查询下,主键更新 ##

## sql ##

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

## 实验结果(锁冲突等待) ##

![b170cb12692ba5e51e441169539e5e1f.png][]

## 结果分析： ##

> 红框部分为更新事务，持有IX锁，等待行锁获取，行锁加锁对象为主键索引7;非红框部分为查询事务，范围内记录均持有X锁(临键锁)，锁定的都是LOCK\_DATA值前面的间隙和值本身，但是主键索引为8的数据，不包含本身。

## 示意图如下 ##

![ee06c619c6238db45bd658038a10d546.png][]

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

## 2|0实验二：foru update查询下,不使用索引 ##

## sql ##

-- 事务一：非索引写锁查询,左开右闭
    select * from user where age > 18 and age <= 40 for update;
    
    -- 事务二：非索引更新，此条记录主键:6，age:33
    update user set sex = 1 where name ='吴八';

## 实验结果(锁冲突等待) ##

![e2bb0caabe7c37cfb7374b5a28b041bb.png][]

## 结果分析： ##

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

## 示意图如下 ##

![eddcd11219a37bb3bfedd4a6795b5aca.png][]

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

## 3|0实验三:辅助索引写锁查询，非索引更新 ##

## sql ##

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

## 实验结果(锁等待冲突) ##

![3783f9d5f42a0ad12f6bb035f84f9289.png][]

## 结果分析 ##

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

## 示意图如下: ##

![41366f7633489cff6ef11c98525eda5d.png][]

--------------------

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

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

[01a30745fd029ad98977e58954bd8f92.png]: https://img-blog.csdnimg.cn/img_convert/01a30745fd029ad98977e58954bd8f92.png
[b170cb12692ba5e51e441169539e5e1f.png]: https://img-blog.csdnimg.cn/img_convert/b170cb12692ba5e51e441169539e5e1f.png
[ee06c619c6238db45bd658038a10d546.png]: https://img-blog.csdnimg.cn/img_convert/ee06c619c6238db45bd658038a10d546.png
[e2bb0caabe7c37cfb7374b5a28b041bb.png]: https://img-blog.csdnimg.cn/img_convert/e2bb0caabe7c37cfb7374b5a28b041bb.png
[eddcd11219a37bb3bfedd4a6795b5aca.png]: https://img-blog.csdnimg.cn/img_convert/eddcd11219a37bb3bfedd4a6795b5aca.png
[3783f9d5f42a0ad12f6bb035f84f9289.png]: https://img-blog.csdnimg.cn/img_convert/3783f9d5f42a0ad12f6bb035f84f9289.png
[41366f7633489cff6ef11c98525eda5d.png]: https://img-blog.csdnimg.cn/img_convert/41366f7633489cff6ef11c98525eda5d.png