【MYSQL】MYSQL 的学习教程（十二）之 MySQL 啥时候用记录锁，啥时候用间隙锁

在「读未提交」和「读已提交」隔离级别下，都只会使用记录锁；而对于「可重复读」隔离级别来说，会使用记录锁、间隙锁和 Next-Key 锁

那么 MySQL 啥时候会用记录锁，啥时候会用间隙锁，啥时候又会用 Next-Key 锁呢？

在这里插入图片描述

1. 影响因素

影响其使用哪种行级锁的因素有：

  1. 索引类型（聚簇索引、唯一二级索引、普通二级索引）
  2. 匹配类型（精确匹配、范围匹配）
  3. 事务隔离级别
  4. 是否开启 Innodb_locks_unsafe_for_binlog 系统变量
  5. 记录是否被标记删除
  6. 具体的执行语句类型（SELECT、INSERT、DELETE、UPDATE）

测试数据：

CREATE TABLE `price_test`  (
  `id` bigint NOT NULL AUTO_INCREMENT,
  `name` varchar(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL,
  `price` int NULL DEFAULT NULL,
  PRIMARY KEY (`id`) USING BTREE,
  UNIQUE INDEX `ind_price`(`price`) USING BTREE,
  INDEX `ind_name`(`name`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 51 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci ROW_FORMAT = Dynamic;
INSERT INTO `price_test` VALUES (1, 'apple', 10);
INSERT INTO `price_test` VALUES (2, 'orange', 30);
INSERT INTO `price_test` VALUES (50, 'perl', 60);

2. 8 种情况

在看死锁日志的时候，我们一般先把这个变量 innodb_status_output_locks 打开哈，它是MySQL 5.6.16 引入的：

set global innodb_status_output_locks =on;

2.1 读可提交隔离级别

2.1.1 查询条件是主键

加一个记录锁

2.1.2 查询条件是唯一索引

加两个记录锁：主键索引上的一条记录；唯一索引上的一条记录

2.1.3 查询条件是普通索引

对应的所有满足 SQL 查询条件的记录，都会加上锁。同时，这些记录对应主键索引，也会上锁

2.1.4 查询条件列无索引

MySQL 会走聚簇索引进行全表扫描过滤。每条记录都会加上 X 锁。但是，为了效率考虑，MySQL 在这方面进行了改进，在扫描过程中，若记录不满足过滤条件，会进行解锁操作。同时优化违背了2PL原则

2.2 可重复读隔离级别

2.2.1 聚簇索引 + 精确匹配

  1. 事务 A 执行下面命令：
begin;
select * from price_test where id = 2 for update;
  1. 事务 B 执行下面命令：
begin;
update price_test set price = 25 where id = 2;
  1. 执行 show engine innodb status\G; 查看锁信息如下图所示

在这里插入图片描述

可以看到，其是对 id 为 2 的索引加了一个记录锁

那如果聚簇索引的值找不到对应的记录呢，将会是一个什么样的结果呢？

  1. 事务 A 执行下面命令，其中 id 为 5 的记录是不存在的
begin;
select * from price_test where id = 5 for update;
  1. 执行 show engine innodb status\G; 查看锁信息如下图所示

在这里插入图片描述

加了一个间隙锁，该间隙锁是 (2, 50) 这个范围

  1. 事务 B 执行如下命令来测试下间隙锁的范围
begin;
// 执行下面任何一个命令，可以通过
update price_test set price = 25 where id = 2;
update price_test set price = 25 where id = 50;
// 执行下面任何一个命令，都将阻塞
insert into price_test(id,name,price) values(3,"test",25);
insert into price_test(id,name,price) values(5,"test",25);
insert into price_test(id,name,price) values(49,"test",25);

聚簇索引 + 精确匹配： 如果能够定位到唯一一条存在的记录，那么其会使用记录锁。如果该记录不存在，那么则会使用间隙锁

2.2.2 聚簇索引 + 范围匹配

  1. 事务 A 执行下面命令：
begin;
select * from price_test where id >= 2 for update;
  1. 执行 show engine innodb status\G; 查看锁信息如下图所示

在这里插入图片描述

事务 A 一共加了 3 个锁，其中 1 个记录锁，2 个 Next-Key 锁。其中 1 个记录锁是对 id 为 2 的索引加的锁，Next-Key 锁是对 (2, 50] 和 (50, 正无穷) 这两个区间加的锁

  1. 在事务 B 执行下面命令可以验证间隙锁的加锁区间：
begin;
// 执行下面任意一条语句，都会阻塞
update price_test set price = 25 where id = 2;
update price_test set price = 25 where id = 50;
insert into price_test(id,name,price) values(5,"test",25);
insert into price_test(id,name,price) values(60,"test",25);

如果范围匹配的值并不存在，那么会是什么情况呢？

  1. 事务 A 执行如下语句，其中 id 为 50 的记录是不存在的。
begin;
select * from price_test where id > 50 for update;
  1. 执行 show engine innodb status\G; 查看锁信息如下图所示

在这里插入图片描述

加了 1 个 Next-Key 锁，锁的范围应该是（50, + 无穷)

聚簇索引 + 范围匹配：存在匹配的值，会使用记录锁 + Next-Key 锁；不存在匹配的值，只会使用 Next-Key 锁

2.2.3 唯一二级索引 + 精确匹配

  1. 事务 A 执行下面命令
begin;
select * from price_test where price = 10 for update;
  1. 执行 show engine innodb status\G; 查看锁信息如下图所示

在这里插入图片描述

加的行级锁是 2 个记录锁，应该是 price = 10 这条索引记录的锁

  1. 此时，如果在事务 B 执行下面命令：
begin;
// 执行下面任意一条语句，都会阻塞
update price_test set name = 'test-name' where price = 10;

那如果唯一二级索引的值找不到对应的记录呢，将会是一个什么样的结果呢？

  1. 事务 A 执行下面命令，其中 price 为 11 的记录是不存在的
begin;
select * from price_test where price = 11 for update;
  1. 执行 show engine innodb status\G; 查看锁信息如下图所示

在这里插入图片描述

加了一个间隙锁，该间隙锁是 (10, 30) 这个范围

唯一二级索引 + 精确匹配：唯一二级索引与聚簇索引非常类似，如果能够定位到唯一一条存在的记录，那么其会使用记录锁。如果该记录不存在，那么则会使用间隙锁

2.2.4 唯一二级索引 + 范围匹配

  1. 事务 A 执行下面命令：
begin;
select * from price_test where price >= 30 for update;
  1. 执行 show engine innodb status\G; 查看锁信息如下图所示

在这里插入图片描述

一共加了 5 个行锁，2 个记录锁（price 为 30、60 的记录），3 个 Next-Key 锁， 。3 个 Next-Key 锁则是 (10, 30]、(30,60]、(60, 正无穷）三个范围

  1. 在事务 B 执行下面命令，每条 SQL 都会阻塞住：
begin;
// 执行下面任意一条语句，都会阻塞
update price_test set name = 'price30' where price = 30;
update price_test set name = 'price60' where price = 60;
insert into price_test(id,name,price) values(5,"test", 20);
insert into price_test(id,name,price) values(5,"test", 40);
insert into price_test(id,name,price) values(5,"test", 70);

如果范围匹配的值并不存在，那么会是什么情况呢？

  1. 事务 A 执行下面命令：
begin;
select * from price_test where price >= 70 for update;
  1. 执行 show engine innodb status\G; 查看锁信息如下图所示

在这里插入图片描述

加了一个 Next-key 锁 (60, 正无穷）

聚簇索引 + 范围匹配：存在匹配的值，会使用记录锁 + Next-Key 锁；不存在匹配的值，只会使用 Next-Key 锁

2.2.5 普通二级索引 + 精确匹配

  1. 事务 A 执行下面命令：
begin;
select * from price_test where name = 'apple' for update;
  1. 执行 show engine innodb status\G; 查看锁信息如下图所示

在这里插入图片描述

一个记录锁 (name = ‘apple’)、间隙锁（范围： (负无穷，orange)）、Next-key 锁（二级索引的记录锁 + 间隙锁）

  1. 事务 B 执行如下命令验证一下
begin;
// 执行下面任意一条语句，都会阻塞
update price_test set name = 'apple-new' where name = 'apple';
insert into price_test(id,name,price) values(5,"aa", 20);
insert into price_test(id,name,price) values(5,"ha", 20);
// 执行下面的语句正常执行
update price_test set name = 'orange-new' where name = 'orange';
insert into price_test(id,name,price) values(5,"orb", 20);

之所以二级索引的精确匹配会有间隙锁，是因为二级索引可能匹配到多个。因此当匹配到一个的时候，会继续往后匹配，直到匹配到一个不符合的记录，随后就会以该不符合的记录（这里是 orange）作为值做一个间隙锁

普通二级索引 + 精确匹配：若匹配到记录，则使用记录锁 + 间隙锁 + Next-Key 锁；否则，只使用间隙锁

2.2.6 普通二级索引 + 范围匹配

  1. 事务 A 执行下面命令：
begin;
select * from price_test where name >= 'orange' for update;
  1. 执行 show engine innodb status\G; 查看锁信息如下图所示

在这里插入图片描述

一共有 2 个记录锁，3 个 Next-Key 锁。其中 2 个记录锁应该是 orange 和 perl 两个记录，3 个 Next-Key 锁，应该是 (apple, orange]、[orange, perl)、[perl, 正无穷)

  1. 事务 B 执行如下命令验证一下：
begin;
// 执行下面任意一条语句，都会阻塞
// 验证记录锁
update price_test set price = 1 where name = 'orange';
update price_test set price = 1 where name = 'perl';
// 验证间隙锁
insert into price_test(id,name,price) values(5,"ba", 20);
insert into price_test(id,name,price) values(5,"orb", 20);
insert into price_test(id,name,price) values(5,"pes", 20);
// 执行下面的语句正常执行
update price_test set price = 1 where name = 'apple';
insert into price_test(id,name,price) values(5,"aa", 20);

普通二级索引 + 范围匹配：存在匹配的值，使用记录锁 + Next-Key 锁；若不存在，则使用 Next-Key 锁

2.2.7 无索引

如果查询条件列没有索引，主键索引的所有记录，都将加上 X 锁，每条记录间也都加上间隙 Gap 锁。大家可以想象一下，任何加锁并发的 SQL，都是不能执行的，全表都是锁死的状态。如果表的数据量大，那效率就更低

在这里插入图片描述

在这种情况下，MySQL 做了一些优化，即 semi-consistent read，对于不满足条件的记录，MySQL 提前释放锁，同时 Gap 锁也会释放。而 semi-consistent read 是如何触发的呢：要么在 Read Committed 隔离级别下；要么在 Repeatable Read 隔离级别下，设置了 innodb_locks_unsafe_for_binlog 参数。但是 semi-consistent read 本身也会带来其他的问题，不建议使用。

2.3 Serializable 串行化

在 Serializable 串行化的隔离级别下，对于写的语句，比如 update account set balance= balance-10 where name=‘Jay’;，跟RC和RR隔离级别是一样的。不一样的地方是，在查询语句，如 select balance from account where name = ‘Jay’;，在 RC 和 RR 是不会加锁的，但是在 Serializable 串行化的隔离级别，即会加锁

本文来自互联网用户投稿，该文观点仅代表作者本人，不代表本站立场。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如若转载，请注明出处：/a/313155.html

如若内容造成侵权/违法违规/事实不符，请联系我们进行投诉反馈qq邮箱809451989@qq.com，一经查实，立即删除！

相关文章

Apache OFBiz groovy 远程代码执行漏洞（CVE-2023-51467）复现

Apache OFBiz groovy 远程代码执行漏洞（CVE-2023-51467）复现

Apache OFBiz groovy 远程代码执行漏洞&#xff0c;攻击者可构造请求绕过身份认证&#xff0c;利用后台相关接口功能执行groovy代码&#xff0c;导致远程代码执行。 1.漏洞级别 高危 2.漏洞搜索 fofa app"Apache_OFBiz"3.影响范围 Apache OFBiz < 18.12.104…
阅读更多...
网站开发第一弹---HTML01

网站开发第一弹---HTML01

&#x1f389;欢迎您来到我的MySQL基础复习专栏 ☆* o(≧▽≦)o *☆哈喽~我是小小恶斯法克&#x1f379; ✨博客主页&#xff1a;小小恶斯法克的博客 &#x1f388;该系列文章专栏&#xff1a;网站开发flask框架 &#x1f379;文章作者技术和水平很有限&#xff0c;如果文中出现…
阅读更多...
【Spring Cloud】微服务架构演变及微服务架构介绍

【Spring Cloud】微服务架构演变及微服务架构介绍

文章目录 系统架构演变单体应用架构垂直应用架构分布式架构SOA 架构微服务架构 微服务架构介绍微服务架构的常见问题微服务架构的常见概念服务治理服务调用服务网关服务容错链路追踪 微服务架构的常见解决方案ServiceCombSpringCloudSpring Cloud Alibaba 总结 欢迎来到阿Q社区…
阅读更多...
让企业的招投标文件、生产工艺、流程配方、研发成果、公司计划、员工信息、客户信息等核心数据更安全。

让企业的招投标文件、生产工艺、流程配方、研发成果、公司计划、员工信息、客户信息等核心数据更安全。

PC端访问地址1&#xff1a;www.drhchina.com PC端访问地址2&#xff1a; https://isite.baidu.com/site/wjz012xr/2eae091d-1b97-4276-90bc-6757c5dfedee 全方位立体式防护　　让数据泄密无处遁形 信息防泄漏是一项系统的整体部署工程&#xff0c;加密监控已成为多数企事业单…
阅读更多...
序章 初始篇—转生到vue世界！

序章 初始篇—转生到vue世界！

Vue.js 是什么&#xff1f; Vue (读音 /vjuː/&#xff0c;类似于 view) 是一套用于构建用户界面的渐进式框架。与其它大型框架不同的是&#xff0c;Vue 被设计为可以自底向上逐层应用。Vue 的核心库只关注视图层&#xff0c;不仅易于上手&#xff0c;还便于与第三方库或既有项…
阅读更多...
Java异常处理--异常处理的方式1

Java异常处理--异常处理的方式1

文章目录 一、异常处理概述二、方式1&#xff1a;捕获异常&#xff08;try-catch-finally&#xff09;&#xff08;1&#xff09;抓抛模型&#xff08;2&#xff09;try-catch-finally基本格式1、基本语法2、整体执行过程3、try和catch3.1 try3.2 catch (Exceptiontype e) &…
阅读更多...
Arcgis10制图/建模小技巧：梯田地形

Arcgis10制图/建模小技巧：梯田地形

小编早年做城市设计的时候&#xff0c;还不知道怎么用gis生成地形&#xff0c;然后导入skechup&#xff1b;只会把cad的等高线导进su后一层层拉伸&#xff08;过程很繁琐&#xff09;&#xff0c;会得到梯田地形。梯田地形虽然不完全贴合实际&#xff0c;但也凑合能用&#xff…
阅读更多...
Jupyter Notebook

Jupyter Notebook

2017年左右在大学里都听说过Jupyter Notebook&#xff0c;并且也安装用了一段时间&#xff0c;后来不知道什么原因没有用了。估计是那时候写代码的时候多一些&#xff0c;因为它可以直接写代码并运行结果&#xff0c;现在不怎么写代码了。 介绍 后缀名为.ipynb的json格式文件…
阅读更多...
M-G552PJ1 IMU（惯性测量单元）CAN接口

M-G552PJ1 IMU（惯性测量单元）CAN接口

一般描述 M-G552PJ1是一个小的形状因子惯性测量单元&#xff08;IMU&#xff09;&#xff0c;具有6个自由度&#xff1a;三轴角速率和 线性加速度&#xff0c;并提供了高稳定性和高精度的测量能力与使用的高精度 补偿技术。通过控制器局域网&#xff08;CAN&#xff09;接口…
阅读更多...
计算机毕业设计----Springboot农业物资管理系统

计算机毕业设计----Springboot农业物资管理系统

项目介绍 农业物资管理系统&#xff0c;管理员可以对角色进行配置&#xff0c;分配用户角色&#xff1b; 主要功能包含&#xff1a;登录、注册、修改密码、零售出库、零售退货、采购订单管理、采购入库管理、采购退货管理、销售管理、财务管理、报表管理、物资管理、基本资料管…
阅读更多...
superset未授权访问漏洞（CVE-2023-27524）复现

superset未授权访问漏洞（CVE-2023-27524）复现

Superset是一个开源的数据探索和可视化平台。它由Apache软件基金会支持&#xff0c;旨在帮助用户通过直观的方式探索、分析和可视化复杂的数据集。Superset支持多种数据源&#xff0c;包括关系型数据库、NoSQL数据库和各种其他数据存储系统。Apache Superset 2.0.1 版本及之前版…
阅读更多...
springboot057洗衣店订单管理系统

springboot057洗衣店订单管理系统

&#x1f345;点赞收藏关注 → 私信领取本源代码、数据库&#x1f345; 本人在Java毕业设计领域有多年的经验&#xff0c;陆续会更新更多优质的Java实战项目希望你能有所收获&#xff0c;少走一些弯路。&#x1f345;关注我不迷路&#x1f345;一 、设计说明 1.1 研究背景 如…
阅读更多...
Java填充Execl模板并返回前端下载

Java填充Execl模板并返回前端下载

功能&#xff1a;后端使用Java POI填充Execl模板&#xff0c;并返回前端下载 Execl模板如下&#xff1a; 1. Java后端 功能&#xff1a;填充模板EXECL,并返回前端 controller层 package org.huan.controller;import org.huan.dto.ExcelData; import org.huan.util.ExcelT…
阅读更多...
DevOps搭建(十六)-Jenkins+K8s部署详细步骤

DevOps搭建(十六)-Jenkins+K8s部署详细步骤

​ 1、整体部署架构图 2、编写脚本 vi pipeline.yml apiVersion: apps/v1 kind: Deployment metadata:namespace: testname: pipelinelabels:app: pipeline spec:replicas: 2selector:matchLabels:app: pipelinetemplate:metadata:labels:app: pipelinespec:containers:- nam…
阅读更多...
计算机毕业设计——SpringBoot仓库管理系统(附源码)

计算机毕业设计——SpringBoot仓库管理系统(附源码)

1&#xff0c;绪论 1.2&#xff0c;项目背景 随着电子计算机技术和信息网络技术的发明和应用&#xff0c;使着人类社会从工业经济时代向知识经济时代发展。在这个知识经济时代里&#xff0c;仓库管理系统将会成为企业生产以及运作不可缺少的管理工具。这个仓库管理系统是由&a…
阅读更多...
Linux习题3

Linux习题3

解析&#xff1a; grep&#xff1a;查找文件内的内容 gzip&#xff1a;压缩文件&#xff0c;文件经压缩后会增加 gz&#xff1a;扩展名 find&#xff1a;在指定目录下查找文件 解析&#xff1a; A hosts文件是Linux系统上一个负责ip地址与域名快速解析的文件&#xff0c;以…
阅读更多...
自动化测试框架pytest系列之21个命令行参数介绍(二)

自动化测试框架pytest系列之21个命令行参数介绍(二)

第一篇 &#xff1a; 自动化测试框架pytest系列之基础概念介绍(一)-CSDN博客 接上文 3.pytest功能介绍 3.1 第一条测试用例 首先 &#xff0c;你需要编写一个登录函数&#xff0c;主要是作为被测功能&#xff0c;同时编写一个测试脚本 &#xff0c;进行测试登录功能 。 登…
阅读更多...
随机过程——卡尔曼滤波学习笔记

随机过程——卡尔曼滤波学习笔记

一、均方预测和随机序列分解 考虑随机序列 使用预测 定义 称为的均方可预测部分。 若相互独立&#xff0c;则是均方不可预测的。 定义随机序列的新息序列 V(k)基于样本观测的条件均值为0&#xff0c;即均方不可预测。 V(k)与是正交的&#xff0c;即。 二、卡尔曼滤波 …
阅读更多...
哪款台灯护眼效果最好？高品质的儿童护眼台灯推荐

哪款台灯护眼效果最好？高品质的儿童护眼台灯推荐

根据去年的报道&#xff0c;全国儿童青少年的整体近视率高达至52.7%&#xff0c;其中幼儿园及小学生患近视率为35.6%&#xff0c;初中生为71.1%&#xff0c;高中生和大学生为80.5%&#xff0c;大学生更是达到90%&#xff01;也就是说几乎绝大部分青少年都患有近视&#xff0c;而…
阅读更多...
SpringBoot知识02

SpringBoot知识02

1、快速生成mapper和service &#xff08;自动生成简单的单表sql&#xff09; 2、springboot配置swagger&#xff08;路径不用加/api&#xff09; &#xff08;1&#xff09;主pom导包&#xff08;子pom要引用&#xff0c;可选依赖&#xff09; <!-- swagger3…
阅读更多...
最新文章

Copyright @ 2022~2023