mysql间隙锁的具体使用

小史生物菌肥

概述

通常用的mysql都是innodb引擎；
一般在update的时候用id都会认为是给行记录加锁；
在使用非唯一索引更新时，会遇到临键锁（范围锁）；
临键锁和表中的数据有关；

1. mysq版本

复制代码

:

1. 8

复制代码

1. 隔离级别

复制代码

：

1. RR

复制代码

可重复读

mysql的lockMode

查看锁的活动信息

1. -- 当前活动的锁信息
   
2. SELECT * FROM performance_schema.data_locks;

复制代码

• X,REC_NOT_GAP：
  1. X代表排他锁

  复制代码
  ，
  1. REC_NOT_GAP代表行锁

  复制代码
  。综合起来就是对这条数据（索引项）添加了行级排他锁;
  
• IX：意向排它锁。上述案例中，给表添加了一个意向排它锁。当其他事务要对全表的数据进行加锁时，那么就不需要判断每一条数据是否被加锁了。
  
• X,GAP:
  1. X

  复制代码
  代表排他锁；
  1. GAP

  复制代码
  代表间隙锁（前开后开,即当前的
  1. lock_data

  复制代码
  中的索引值对应的
  1. id

  复制代码
  到
  1. 最近的上一个id值

  复制代码
  之间的空隙被锁定了）
  
• supremum pseudo-record： 是
  1. InnoDB

  复制代码
  中定义的一种特殊记录，我们可以理解为
  1. +∞

  复制代码

X,GAP demo说明

假设表中的数据如下,

1. age有普通BTree

复制代码

索引，然后

1. 事务1

复制代码

更新其中

1. age为24

复制代码

的数据；
可以看到

1. age=24

复制代码

对应的

1. id=3

复制代码

加上了

1. X排它锁

复制代码

,

1. id=3

复制代码

的加上了行级排它锁；然后对

1. age=24

复制代码

后面的

1. age

复制代码

索引加了一个排他间隙锁，锁住了

1. id

复制代码

范围为

1. (3,5)

复制代码

时间的间隙;
综上获得了

1. id

复制代码

范围为

1. [3,5)

复制代码

之间的锁

1. -- 事务1操作
   
2. UPDATE user SET name = 'Vladimir' WHERE age = 24;

复制代码

临键锁

假设有如下表；且表中数据如下；

1. -- 表结构如下
   
2. CREATE TABLE `user` (
   
3.   `id` int NOT NULL AUTO_INCREMENT COMMENT '主键',
   
4.   `name` varchar(255) DEFAULT NULL COMMENT '姓名',
   
5.   `age` int DEFAULT NULL COMMENT '年龄',
   
6.   PRIMARY KEY (`id`),
   
7.   KEY `idx_age` (`age`)
   
8. ) ENGINE=InnoDB COMMENT='用户表';

复制代码

捞点网图,

1. 间隙锁

复制代码

可以看做一个

1. 左开右闭的区间

复制代码

那么在

1. 事务A

复制代码

中

1. 执行sql

复制代码

;
先

1. select查询

复制代码

，查询到age=24的记录，然后继续向后查询，发现后一条是

1. age=32,age不等于24，向后查询结束

复制代码

，所以在获取间隙锁

1. (24,32]

复制代码

，同时当前

1. age=24

复制代码

的记录会加上排它锁;
然后再向前查询前一条记录是

1. age=10，不等于24，向前查询结束

复制代码

，然后会获取

1. (10，24]

复制代码

的间隙锁;
综上所述，

1. INNDB

复制代码

加锁首先定位到等于或者第一个大于目标值的叶子节点
事务A

1. -- 根据非唯一索引列 UPDATE 某条记录
   
2. UPDATE user SET name = 'Vladimir' WHERE age = 24;
   
3. -- 或根据非唯一索引列 锁住某条记录
   
4. SELECT * FROM user WHERE age = 24 FOR UPDATE;

复制代码

在

1. 事务B

复制代码

中执行

1. sql

复制代码

；会遇到间隙锁，会阻塞

1. INSERT INTO user VALUES(100, 'Ezreal', 30);

复制代码

试验结果如下

1. -- 当前活动的锁信息
   
2. SELECT * FROM performance_schema.data_locks;

复制代码

案例


案例一：间隙锁简单案例

步骤事务A事务B1begin;
select * from t where id = 11 for update;-2-insert into user value(12,12,12)

3commit;-当有如下事务A和事务B时，事务A会对数据库表增加（10，15]这个区间锁，这时insert id = 12 的数据的时候就会因为区间锁（10，15]而被锁住无法执行。

案例二： 间隙锁死锁问题

步骤事务A事务B1begin;
select * from t where id = 9 for update;-2-begin;
select * from t where id = 6 for update;3-insert into user value(7,7,7)

4insert into user value(7,7,7)

-不同于写锁相互之间是互斥的原则，间隙锁之间不是互斥的，如果一个事务A获取到了（5,10]之间的间隙锁，另一个事务B也可以获取到（5,10]之间的间隙锁。这时就可能会发生死锁问题，如下案例。
事务A获取到（5,10]之间的间隙锁不允许其他的DDL操作，在事务提交，间隙锁释放之前，事务B也获取到了间隙锁（5,10]，这时两个事务就处于死锁状态

案例三： 等值查询—唯一索引

步骤事务A事务B事务C1begin;
update u set d= d+ 1 where id = 7;--2-insert into u (8,8,8);

-4--update set d = d+ 1 where id = 101.加锁的范围是（5,10]的范围锁
2.由于数据是等值查询，并且表中最后数据id = 10 不满足id= 7的查询要求，故id=10 的行级锁退化为间隙锁，（5,10）
3.所以事务B中id=8会被锁住，而id=10的时候不会被锁住

案例四： 等值查询—普通索引

步骤事务A事务B事务C1begin;
select id form t where c = 5 lock in share mode;--2-update t set d = d + 1 where id = 5-4--insert into values (7,7,7)

1.加锁的范围是（0,5]，（5,10]的范围锁
2.由于c是普通索引，根据原则4，搜索到5后继续向后遍历直到搜索到10才放弃，故加锁范围为（5,10]
3.由于查询是等值查询，并且最后一个值不满足查询要求，故间隙锁退化为（5,10）
4.因为加锁是对普通索引c加锁，而且因为索引覆盖，没有对主键进行加锁，所以事务B执行正常
5.因为加锁范围（5,10）故事务C执行阻塞
6.需要注意的是，lock in share mode 因为覆盖索引故没有锁主键索引，如果使用for update 程序会觉得之后会执行更新操作故会将主键索引一同锁住

案例五： 范围查询—唯一索引

步骤事务A事务B事务C1begin;
select * form t where id >= 10 and id <11 for update--2-insert into values(8,8,8)
insert into values(13,13,13)

-4--update t set d = d+ 1 where id = 15

• next-key lock 增加范围锁（5,10]
  
• 根据原则5，唯一索引的范围查询会到第一个不符合的值位置，故增加（10，15]
  3.因为等值查询有id =10 根据原则3间隙锁升级为行锁，故剩余锁[10,15]
  4.因为查询并不是等值查询，故[10,15]不会退化成[10,15)
  5.故事务B（13,13,13）阻塞，事务C阻塞
  

案例六： 范围查询—普通索引

步骤事务A事务B事务C1begin;
select * form t where c >= 10 and c <11 for update--2-insert into values(8,8,8)

-4--update t set d = d+ 1 where c = 15

• next-key lock 增加范围锁（5,10]，（10，15]
  2.因为c是非唯一索引，故（5,10]不会退化为10
  3.因为查询并不是等值查询，故[10,15]不会退化成[10,15)
  4.所以事务B和事务C全部堵塞
  

案例八： 普通索引-等值问题

上面的数据增加一行（30,10,30），这样在数据库中存在的c=10的就有两条记录

步骤事务A事务B事务C1begin;
delete from t where c = 10--2-insert into values(12,12,12)

-4--update t set d = d+ 1 where c = 15

• next-key lock 增加范围锁（5,10]，（10，15]
  2.因为是等值查询故退化为（5,10]，（10，15），故事务B阻塞，事务C执行成功
  加锁的范围如下图
  

案例九： 普通索引-等值Limit问题

步骤事务A事务B事务C1begin;
delete from t where c = 10 limit 2--2-insert into values(12,12,12)

-4--update t set d = d+ 1 where c = 15

1.根据上面案例8改造，将delete增加limit操作2的操作
2.因为知道了数据加锁值加2条，故在加锁（5，10]之后发现已经有两条数据，故后面不在向后匹配加锁。所以事务B执行成功，加锁范围如下

到此这篇关于mysql间隙锁的具体使用的文章就介绍到这了,更多相关mysql间隙锁内容请搜索脚本之家以前的文章或继续浏览下面的相关文章希望大家以后多多支持脚本之家！

来源:https://www.jb51.net/database/315206cn7.htm
免责声明：由于采集信息均来自互联网，如果侵犯了您的权益，请联系我们【E-Mail:cb@itdo.tech】 我们会及时删除侵权内容，谢谢合作！