Python微信订餐小程序课程视频

https://edu.csdn.net/course/detail/36074

Python实战量化交易理财系统

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

0|*1***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;

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;

初始化数据

下面让我们开启实验

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

sql

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

1|*0***实验结果(锁冲突等待)**

结果分析：

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

示意图如下

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 ='吴八';

1|*0***实验结果(锁冲突等待)**

结果分析：

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

示意图如下

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

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 ='王五';

1|*0***实验结果(锁等待冲突)**

结果分析

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

示意图如下:

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

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

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

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

关注我的公众号

__EOF__

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-eZGsdkBE-1648228948703)(https://blog.csdn.net/qiuzhiqiang/p/16056034.html)]邱志强 本文链接：https://blog.csdn.net/qiuzhiqiang/p/16056034.html关于博主：I am a good person版权声明：本博客所有文章除特别声明外，均采用 BY-NC-SA 许可协议。转载请注明出处！声援博主：如果您觉得文章对您有帮助，可以点击文章右下角【推荐】一下。您的鼓励是博主的最大动力！

转载请注明：xuhss » Mysql学习笔记-临键锁实验