当前位置： 首页 > article >正文

68 mysql 的 临键锁

article 2024/12/5 4:26:46

前言

我们这里来说的就是 我们在 mysql 这边常见的 一种锁, 行临键锁

虽然 在平时我们用到的不是很多, 我们这里 主要是 讲一下 它的主要的触发的场景

行临键锁 等价于 行锁 + 间隙锁, 行间隙锁是一个 左开右开的区间, 行临键锁 是一个左开右闭的区间

但是 它 和 行锁的差异仅仅在于 mode 的标记, 行锁这边是 LOCK_NOT_GAP + LOCK_X, 行临键锁 这边是 LOCK_ORDINARY + LOCK_X

 

我们这里测试表结构如下 

CREATE TABLE `tz_test_04` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `field1` varchar(128) DEFAULT NULL,
  `field2` varchar(128) DEFAULT NULL,
  PRIMARY KEY (`id`) USING BTREE,
  KEY `field_1_2` (`field1`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=utf8

 

测试数据如下, 之所以 留下一些区间 是为了产生 “间隙”

f5dc0cbf2d3eb40e06a0ff07bbba2ff3.png

 

 

添加临键锁

这里的整体流程, 以及相关上下文 基本上都和 添加 行锁类似

仅仅是在不同的语境中, 一部分语境使用的是 行锁, 一部分语境使用的 行临键锁

我们来看一下 两者的选择的区别

首先是默认值, 如果是 空间索引 或者 已提交读隔离级别以下 或者 根据查询条件唯一定位记录 等等情况默认值是 行锁, 否则是临键锁 

另外就是一个特殊的情况下使用使用行锁, 比如在 ”id >= 100” 的条件下, 匹配到了 “id = 100” 的记录, 则只在该记录上面添加行锁 

是使用行锁 还是 临键锁 的关键就是 查询结果是否唯一

1c1afaed1b26e9bbaeadfd8ccaa6b9bc.png

 

 

基于 主键/唯一索引 的查询

1. select * from tz_test_04 where id = 5 for update;

根据主键查询的一条存在的记录 

可以看到添加的是 行排他锁, 因为这个查询从理论上来说是可以最多定位到一条记录 

cbfc0dbe7acd29b5865ea93cad4fdc88.png

 

 

2. select * from tz_test_04 where id = 7 for update;

根据主键查询的一条不存在的记录 

可以看到添加的是 行间隙排他锁 

668043363f1a2434be440026dc499f0d.png

 

 

3. select * from tz_test_04 where id <> 5 for update;

不等于查询是转化为了 两个区间查询, 这两个 我们在 >, < 的地方查看具体的加锁处理 

select * from tz_test_04 where id < 5 for update;

select * from tz_test_04 where id > 5 for update;

 

第一个查询区间 

3c802db678f38c883bb89675ac0c07c5.png

 

第二个查询区间 

98e235abf5dd4b0cdc1f03a1d95b9054.png

 

 

4.select * from tz_test_04 where id > 5 for update;
5.select * from tz_test_04 where id >= 5 for update;

因为是非唯一查询, 在扫描过的记录上面增加 临键锁

  • sql, 会扫描 id=10, supremum 两条记录, 都加上临键锁, 锁定的区间是 (5, 正无穷]
  • sql, 会扫描 id=5, id=10, supremum 三条记录, id=5的记录增加行锁, 另外两条都加上临键锁, 锁定的区间是 [5, 正无穷]

dc9a9af061fc1697ef9a5a00f82c024f.png

 

select * from tz_test_04 where id >= 5 for update; 的 sql, 在 id=5 的记录上面增加行锁的处理如下 

从 rec + 0x11, 可以判断当前记录是 id=5, 然后从 mode, gap_mode 可以判断出当前是在该记录上面增加的 行排他锁

4e443f954543e35802833046ece43ab6.png

 

对于如下五条 sql, 第一条 都可以正常执行

第二条, 对于限定 id>5 的 sql, 这条 sql 会正常执行, 报错 主键重复, 对于限定 id>=5 的 sql, 这条 sql 会阻塞

对于 后面三条 sql, 都会阻塞

INSERT INTO `test_02`.`tz_test_04`(`id`, `field1`, `field2`) VALUES (4, 'field8', '8');
INSERT INTO `test_02`.`tz_test_04`(`id`, `field1`, `field2`) VALUES (5, 'field8', '8');

INSERT INTO `test_02`.`tz_test_04`(`id`, `field1`, `field2`) VALUES (8, 'field8', '8');
INSERT INTO `test_02`.`tz_test_04`(`id`, `field1`, `field2`) VALUES (10, 'field8', '8');
INSERT INTO `test_02`.`tz_test_04`(`id`, `field1`, `field2`) VALUES (15, 'field8', '8');

 

 

6.select * from tz_test_04 where id < 5 for update;
7.select * from tz_test_04 where id <= 5 for update;

这个和上面同理, 我们这里直接说结论了 

因为是非唯一查询, 在扫描过的记录上面增加 临键锁

第一条 sql, 会扫描 id=1, id=5 两条记录, 都加上临键锁, 锁定的区间是 (负无穷, 5]

第二条 sql, 会扫描 id=1, id=5, id=10 三条记录, 都加上临键锁, 锁定的区间是 (负无穷, 10]

 

对于如下五条 sql, 第一条, 第二条 都会阻塞

第三条, 第四条 对于 “id<5” 可以正常执行, 对于 “id<=5” 会阻塞 

对于第五条 sql, 都可以正常执行 

INSERT INTO `test_02`.`tz_test_04`(`id`, `field1`, `field2`) VALUES (4, 'field8', '8');
INSERT INTO `test_02`.`tz_test_04`(`id`, `field1`, `field2`) VALUES (5, 'field8', '8');

INSERT INTO `test_02`.`tz_test_04`(`id`, `field1`, `field2`) VALUES (8, 'field8', '8');
INSERT INTO `test_02`.`tz_test_04`(`id`, `field1`, `field2`) VALUES (10, 'field8', '8');
INSERT INTO `test_02`.`tz_test_04`(`id`, `field1`, `field2`) VALUES (15, 'field8', '8');

 

 

基于 普通索引 的查询

这里来看一下 以上的情况对于 普通索引 上面查询的执行情况

 

1. select * from tz_test_04 where field1 = 'field5' for update;

根据索引查询的存在的记录 

这个会增加 三个锁, 会在 field1=’field5’ 上面增加一个 临键锁, 在具体的数据行记录上面增加行排他锁, 在下一个索引记录上面增加一个间隙锁

所以 在 field1 索引上面, 锁定的是 (field1, field9), 在 id=5 数据行上面加的 行排他锁

但是因为 我们这里的普通索引是可能重复的, 因此 具体索引记录是添加在第一个 field9 之前, 还是最后一个 field9 之后, 这个取决于数据库的实现 

但是实际锁定的区间 还取决于带插入的索引记录在整体记录的排序

 

索引 field1=’field5’ 上面增加 临键锁

51caf89780fed8cdeb3871c3a5ab176e.png

 

id=5 的数据记录上面增加行锁 

1ae1316f757af719f1bda8768b4a65a8.png

 

索引 field1=’field5’ 上面增加 间隙锁, 但是 这里实际的功能是等价于一个 临键锁

00ac89c5bf04b4010caf11c89231dfda.png

 

对于如下四条 sql, 第一条, 第二条 都会阻塞

第三条 会阻塞, 第四条不会阻塞 

然后 第五条 不会阻塞

和 第四条的差异在于, 数据库中 索引记录的排序是 (field1, id), 我们锁定的记录是 (field9, 10), 第三条 sql 插入的记录是 (field9, 8) 是排在锁定记录之前的, 还在锁定的逻辑区间, 因此算冲突 

插入的记录是 (field9, 15) 是排在锁定记录之后的, 不在锁定的逻辑区间, 可以正常处理业务

因此 确定索引锁定的区间是 索引+聚簇索引 来确定的

INSERT INTO `test_02`.`tz_test_04`(`id`, `field1`, `field2`) VALUES (8, 'field1', '8');
INSERT INTO `test_02`.`tz_test_04`(`id`, `field1`, `field2`) VALUES (8, 'field5', '8');
INSERT INTO `test_02`.`tz_test_04`(`id`, `field1`, `field2`) VALUES (8, 'field9', '8');
INSERT INTO `test_02`.`tz_test_04`(`id`, `field1`, `field2`) VALUES (15, 'field9', '8');
INSERT INTO `test_02`.`tz_test_04`(`id`, `field1`, `field2`) VALUES (9, 'fielda', '8');

 

 

2. select * from tz_test_04 where field1 = 'field7' for update;

根据索引查询的不存在的记录 

可以看到添加的是 行间隙排他锁, 这里的 gap_mode 为 LOCK_GAP

逻辑上锁定的区间是 索引字段 field1 的区间 (field5, field9), 但是实际锁定的区间 还取决于带插入的索引记录在整体记录的排序

04de465c1c574b5ac03abd6be5f2558e.png

 

对于如下五条 sql, 第一条, 第二条 不会阻塞

第三条, 第四条 会阻塞

第五条, 第六条 不会阻塞 

具体的原因就和上面一致, 确定索引锁定的区间是 索引+聚簇索引 来确定的

INSERT INTO `test_02`.`tz_test_04`(`id`, `field1`, `field2`) VALUES (8, 'field1', '8');
INSERT INTO `test_02`.`tz_test_04`(`id`, `field1`, `field2`) VALUES (2, 'field5', '8');
INSERT INTO `test_02`.`tz_test_04`(`id`, `field1`, `field2`) VALUES (8, 'field5', '8');
INSERT INTO `test_02`.`tz_test_04`(`id`, `field1`, `field2`) VALUES (8, 'field9', '8');
INSERT INTO `test_02`.`tz_test_04`(`id`, `field1`, `field2`) VALUES (15, 'field9', '8');
INSERT INTO `test_02`.`tz_test_04`(`id`, `field1`, `field2`) VALUES (9, 'fielda', '8');

 

 

3. select * from tz_test_04 where field1 <> 'field5' for update;

不等于查询是转化为了 全表查询 或者 两个区间查询, 这两个 我们在 >, < 的地方查看具体的加锁处理 

如果是全表查询 则是在扫描的记录上面增加 行临键锁

我们这里的实际情况是 进行了全表的扫描, mysql 认为这样开销较小一些 

 

 

4. select * from tz_test_04 where field1 > 'field5' for update;
5. select * from tz_test_04 where field1 >= 'field5' for update;

第一条 sql 会扫描 field1='field9', supremum 两条记录, 都加上临键锁, 锁定的区间是 ((field5,5), 正无穷], 并会在 id=10 的数据记录上面增加 行排他锁

第二条 sql 会扫描 field1='field5', field1='field9', supremum 三条记录, 都加上临键锁, 锁定的区间是 ((field1,1), 正无穷], 并会在 id=5, id=10 的数据记录上面增加 行排他锁

 

对于如下 六条 sql 

对于 field1>’field5’ 前面两条可以正常执行, 后面四条会阻塞 

对于 field1>=’field5’ 这里的六条都会阻塞 

INSERT INTO `test_02`.`tz_test_04`(`id`, `field1`, `field2`) VALUES (8, 'field1', '8');
INSERT INTO `test_02`.`tz_test_04`(`id`, `field1`, `field2`) VALUES (2, 'field5', '8');
INSERT INTO `test_02`.`tz_test_04`(`id`, `field1`, `field2`) VALUES (8, 'field5', '8');
INSERT INTO `test_02`.`tz_test_04`(`id`, `field1`, `field2`) VALUES (8, 'field9', '8');
INSERT INTO `test_02`.`tz_test_04`(`id`, `field1`, `field2`) VALUES (15, 'field9', '8');
INSERT INTO `test_02`.`tz_test_04`(`id`, `field1`, `field2`) VALUES (9, 'fielda', '8');

 

 

6. select * from tz_test_04 where field1 < 'field5' for update;
7. select * from tz_test_04 where field1 <= 'field5' for update;

第一条 sql 会扫描 field1='field1', field1='field5' 两条记录, 都加上临键锁, 锁定的区间是 (负无穷, (field5,5)], 并会在 id=1 的数据记录上面增加 行排他锁

第二条 sql 会扫描 field1='field1', field1='field5', field1='field9' 三条记录, 都加上临键锁, 锁定的区间是 (负无穷, (field9,9)], 并会在 id=1, id=5 的数据记录上面增加 行排他锁

 

对于如下 六条 sql 

对于 field1<’field5’ 前面两条会阻塞, 后面四条会正常执行 

对于 field1<=’field5’ 前面四条会阻塞, 后面两条正常执行

INSERT INTO `test_02`.`tz_test_04`(`id`, `field1`, `field2`) VALUES (8, 'field1', '8');
INSERT INTO `test_02`.`tz_test_04`(`id`, `field1`, `field2`) VALUES (2, 'field5', '8');
INSERT INTO `test_02`.`tz_test_04`(`id`, `field1`, `field2`) VALUES (8, 'field5', '8');
INSERT INTO `test_02`.`tz_test_04`(`id`, `field1`, `field2`) VALUES (8, 'field9', '8');
INSERT INTO `test_02`.`tz_test_04`(`id`, `field1`, `field2`) VALUES (15, 'field9', '8');
INSERT INTO `test_02`.`tz_test_04`(`id`, `field1`, `field2`) VALUES (9, 'fielda', '8');

 

 

临键锁的使用 以及 释放

这个参见 行锁, 间隙锁 相关 

这里不再 赘述

 

 

完

 

 

 


http://www.kler.cn/a/419963.html

相关文章：

  • 互联网 Java 面试八股文汇总（2025 最新整理）
  • 泛化调用 ：在没有接口的情况下进行RPC调用
  • OpenCV圆形标定板检测算法findCirclesGrid原理详解
  • MATLAB —— 机械臂工作空间，可达性分析
  • SpringMVC（二）
  • Google Cloud 混合云部署连接方式最佳实践案例讲解
  • Unity开发FPS游戏之完结篇
  • RDIFramework.NET CS敏捷开发框架 SOA服务三种访问(直连、WCF、WebAPI)方式
  • Java程序员最新场景面试题总结
  • Brain.js（二）：项目集成方式详解——npm、cdn、下载、源码构建
  • 电子电气架构 --- 车载网关GW连接外部IP Tester
  • springboot371高校实习管理系统(论文+源码)_kaic
  • 鸿蒙Next星河版基础用例
  • Leetcode 第425场周赛分析总结
  • 力扣1382：将二叉搜索树便平衡
  • Scala的模式匹配变量类型
  • 方寸 i560 安全存储加密芯片 SoC 存储安全芯片技术手册
  • Ubuntu24.04配置DINO-Tracker
  • php+Mysql单页支持不同数据结构不同查询条件查搜多表实例
  • 006 MATLAB编程基础
  • ansible自动化运维（一）配置主机清单
  • 在html页面显示一个变量，而这个变量中有xss脚本，如何安全的把这个变量原样展示出来
  • 360笔试题之LINUX和UNIX篇
  • 数据结构——排序第三幕（深究快排（非递归实现）、快排的优化、内省排序，排序总结）超详细！！！！
  • 【Debug】hexo-github令牌认证 Support for password authentication was removed
  • Node.js-Mongodb数据库