程序员经验分享-hwhale

MySQL-SQL全部锁详解（下）

2023-11-11

♥️作者：小刘在C站

♥️个人主页： 小刘主页 

♥️努力不一定有回报，但一定会有收获加油！一起努力，共赴美好人生！

♥️学习两年总结出的运维经验，以及思科模拟器全套网络实验教程。专栏：云计算技术

♥️小刘私信可以随便问，只要会绝不吝啬，感谢CSDN让你我相遇！

前言

上章讲述了MySQL-SQL语言中的全部锁上章，本章完结继续上一张点这里上一张点这里         

目录

4 行级锁

4.1 介绍

​编辑

 4.2 行锁

1). 介绍

 2). 演示

示例演示

A. 普通的select语句，执行时，不会加锁。

 B. select...lock in share mode，加共享锁，共享锁与共享锁之间兼容。

C. 排它锁与排他锁之间互斥

D. 无索引行锁升级为表锁

4.3 间隙锁&临键锁

示例演示

介绍分析一下：

                                              

4 行级锁

4.1 介绍

行级锁，每次操作锁住对应的行数据。锁定粒度最小，发生锁冲突的概率最低，并发度最高。应用在InnoDB 存储引擎中。
InnoDB 的数据是基于索引组织的，行锁是通过对索引上的索引项加锁来实现的，而不是对记录加的锁。对于行级锁，主要分为以下三类：
行锁（ Record Lock ）：锁定单个行记录的锁，防止其他事务对此行进行 update 和 delete 。在
RC 、 RR 隔离级别下都支持。
间隙锁（ Gap Lock ）：锁定索引记录间隙（不含该记录），确保索引记录间隙不变，防止其他事
务在这个间隙进行 insert ，产生幻读。在 RR 隔离级别下都支持。
临键锁（ Next-Key Lock ）：行锁和间隙锁组合，同时锁住数据，并锁住数据前面的间隙 Gap 。
在 RR 隔离级别下支持。

 4.2 行锁

1). 介绍

InnoDB 实现了以下两种类型的行锁：
共享锁（S ）：允许一个事务去读一行，阻止其他事务获得相同数据集的排它锁。
排他锁（ X ）：允许获取排他锁的事务更新数据，阻止其他事务获得相同数据集的共享锁和排他
锁。
两种行锁的兼容情况如下 :

 常见的SQL语句，在执行时，所加的行锁如下：

 2). 演示

默认情况下， InnoDB 在 REPEATABLE READ 事务隔离级别运行， InnoDB 使用 next-key 锁进行搜
索和索引扫描，以防止幻读。
针对唯一索引进行检索时，对已存在的记录进行等值匹配时，将会自动优化为行锁。
InnoDB 的行锁是针对于索引加的锁，不通过索引条件检索数据，那么 InnoDB 将对表中的所有记
录加锁，此时 就会升级为表锁。
可以通过以下 SQL ，查看意向锁及行锁的加锁情况： 
select object_schema,object_name,index_name,lock_type,lock_mode,lock_data from
performance_schema.data_locks;

示例演示

数据准备 : 
CREATE TABLE `stu` (
`id` int NOT NULL PRIMARY KEY AUTO_INCREMENT,
`name` varchar(255) DEFAULT NULL,
`age` int NOT NULL
) ENGINE = InnoDB CHARACTER SET = utf8mb4;
INSERT INTO `stu` VALUES (1, 'tom', 1);
INSERT INTO `stu` VALUES (3, 'cat', 3);
INSERT INTO `stu` VALUES (8, 'rose', 8);
INSERT INTO `stu` VALUES (11, 'jetty', 11);
INSERT INTO `stu` VALUES (19, 'lily', 19);
INSERT INTO `stu` VALUES (25, 'luci', 25);
演示行锁的时候，我们就通过上面这张表来演示一下。

A. 普通的select语句，执行时，不会加锁。

 B. select...lock in share mode，加共享锁，共享锁与共享锁之间兼容。

 共享锁与排他锁之间互斥。

客户端一获取的是 id 为 1 这行的共享锁，客户端二是可以获取 id 为 3 这行的排它锁的，因为不是同一行数据。 而如果客户端二想获取 id 为 1 这行的排他锁，会处于阻塞状态，以为共享锁与排他锁之间互 斥。

C. 排它锁与排他锁之间互斥

当客户端一，执行 update 语句，会为 id 为 1 的记录加排他锁； 客户端二，如果也执行 update 语句更
新 id 为 1 的数据，也要为 id 为 1 的数据加排他锁，但是客户端二会处于阻塞状态，因为排他锁之间是互斥的。 直到客户端一，把事务提交了，才会把这一行的行锁释放，此时客户端二，解除阻塞。 

D. 无索引行锁升级为表锁

stu 表中数据如下 :

 我们在两个客户端中执行如下操作:

在客户端一中，开启事务，并执行 update 语句，更新 name 为 Lily 的数据，也就是 id 为 19 的记录 。
然后在客户端二中更新 id 为 3 的记录，却不能直接执行，会处于阻塞状态，为什么呢？
原因就是因为此时，客户端一，根据 name 字段进行更新时， name 字段是没有索引的，如果没有索引， 此时行锁会升级为表锁 ( 因为行锁是对索引项加的锁，而 name 没有索引 ) 。
接下来，我们再针对 name 字段建立索引，索引建立之后，再次做一个测试：

此时我们可以看到，客户端一，开启事务，然后依然是根据 name 进行更新。而客户端二，在更新 id 为 3的数据时，更新成功，并未进入阻塞状态。 这样就说明，我们根据索引字段进行更新操作，就可以避免行锁升级为表锁的情况。

4.3 间隙锁&临键锁

默认情况下， InnoDB 在 REPEATABLE READ 事务隔离级别运行， InnoDB 使用 next-key 锁进行搜
索和索引扫描，以防止幻读。
索引上的等值查询 ( 唯一索引 ) ，给不存在的记录加锁时 , 优化为间隙锁 。
索引上的等值查询 ( 非唯一普通索引 ) ，向右遍历时最后一个值不满足查询需求时， next-key
lock 退化为间隙锁。
索引上的范围查询 ( 唯一索引 )-- 会访问到不满足条件的第一个值为止。
注意：间隙锁唯一目的是防止其他事务插入间隙。间隙锁可以共存，一个事务采用的间隙锁不会阻止另一个事务在同一间隙上采用间隙锁。

示例演示

A. 索引上的等值查询 ( 唯一索引 ) ，给不存在的记录加锁时 , 优化为间隙锁 。

B. 索引上的等值查询 ( 非唯一普通索引 ) ，向右遍历时最后一个值不满足查询需求时， next-key
lock 退化为间隙锁。

介绍分析一下：

我们知道 InnoDB 的 B+ 树索引，叶子节点是有序的双向链表。 假如，我们要根据这个二级索引查询值为 18 的数据，并加上共享锁，我们是只锁定 18 这一行就可以了吗？ 并不是，因为是非唯一索引，这个结构中可能有多个 18 的存在，所以，在加锁时会继续往后找，找到一个不满足条件的值（当前案例中也 就是 29 ）。此时会对 18 加临键锁，并对 29 之前的间隙加锁。

 

 C. 索引上的范围查询(唯一索引)--会访问到不满足条件的第一个值为止。

查询的条件为 id>=19 ，并添加共享锁。 此时我们可以根据数据库表中现有的数据，将数据分为三个部分：
[19]
(19,25]
(25,+∞]
所以数据库数据在加锁是，就是将 19 加了行锁， 25 的临键锁（包含 25 及 25 之前的间隙），正无穷的临键锁 ( 正无穷及之前的间隙 ) 。

♥️关注，就是我创作的动力

♥️点赞，就是对我最大的认可

♥️这里是小刘，励志用心做好每一篇文章，谢谢大家

本文内容由网友自发贡献，版权归原作者所有，本站不承担相应法律责任。如您发现有涉嫌抄袭侵权的内容，请联系:hwhale#tublm.com(使用前将#替换为@)

MySQL入门到实战

数据库

mysql

sql

MySQL-SQL全部锁详解（下） 的相关文章

  • sql查询获取从一月到当月的所有数据，即使没有记录

    我不擅长 sql 所以任何帮助世界都很棒 我有一个 SQL 查询 可以获取从一月到当月注册的记录 我的代码示例 SELECT DatePart YEAR p createStamp as TheYear DatePart MONTH p c

  • Reporting Services 在哪里存储其日志文件

    最相关的谷歌结果似乎表明 为了访问日志 我们必须将您自己的日志表部署到数据库并制作报告服务写入它 http technet microsoft com en us library ms157403 aspx 简而言之 Reporting S

  • 在 jQuery AJAX 成功中从 MySql 获取特定响应

    好吧 我有这个 ajax 代码 它将在 Success 块中返回 MySql 的结果 ajax type POST url index php success function data alert data My Query sql SE

  • SQL Server 连接其他表中不存在的位置

    Service Asset AssetService Id Name Id Name AssetId ServiceId

  • 提高第一个查询的性能

    如果执行以下数据库 postgres 查询 则第二次调用要快得多 我猜第一个查询很慢 因为操作系统 linux 需要从磁盘获取数据 第二个查询受益于文件系统级别和 postgres 中的缓存 有没有一种方法可以优化数据库以快速获得结果fir

  • 使用子查询 select 创建新表

    我试图从子查询选择创建一个新表 但出现以下错误 附近的语法不正确 SELECT INTO foo FROM SELECT DATEPART MONTH a InvoiceDate as CalMonth DATEPART YEAR a In

  • 解析带下划线的 SQL Server 数字文字

    我想知道它为什么有效以及为什么它不返回错误 SELECT 2015 11 Result 11 2015 第二种情况 SELECT 2 1 a a 2 1 检查元数据 SELECT name system type name FROM sys

  • SQL 连接两个没有关系的表

    我有具有相同结构的不同表 我想通过其中一列将它们连接起来 问题是他们不共享该专栏中的信息 Table 1 Type A Name Value Table 2 Type B Name Value 结果表 在单列中 nameFromA name

  • posts_search 中的自定义查询

    如何使用此查询作为我的自定义搜索查询 add filter posts search my search is perfect 20 2 function my search is perfect search wp query sWord

  • 数字表与递归 CTE 生成一系列数字

    为什么使用数字表比使用递归 CTE 动态生成它们要快得多 在我的机器上 给定一张桌子numbers单列n 主键 包含从1到100000的数字 查询如下 select n from numbers 大约需要 400 毫秒才能完成 使用递归 C

  • SELECT max(x) 返回 null；我怎样才能让它返回0？

    运行以下命令时如何返回 0 而不是 null SELECT MAX X AS MaxX FROM tbl WHERE XID 1 假设没有XID 1的行 or SELECT coalesce MAX X 0 AS MaxX FROM tbl

  • 日期语句之间的 JPQL SELECT [关闭]

    Closed 这个问题是无法重现或由拼写错误引起 help closed questions 目前不接受答案 我想将此 SQL 语句转换为等效的 JPQL SELECT FROM events WHERE events date BETWE

  • MYSQL从每个类别中随机选择一条记录

    我有一个数据库Items表看起来像这样 id name category int 有几十万条记录 每个item可以是 7 种不同的之一categories 对应于categories table id category 我想要一个从每个类别

  • 从Django中具有外键关系的两个表中检索数据？ [复制]

    这个问题在这里已经有答案了 This is my models py file from django db import models class Author models Model first name models CharFie

  • Mysql插入表后不显示右单引号(’)

    我有一个名为 测试 的表 我插入了一行 其中包含unicode字符右单引号 0x2019在名称字段中 SQL insert into Testing values Sno Name Address insert into Testing v

  • Spark SQL 中的 SQL LIKE

    我正在尝试使用 LIKE 条件在 Spark SQL 中实现联接 我正在执行连接的行看起来像这样 称为 修订 Table A 8NXDPVAE Table B 4 8 NXD V 在 SQL Server 上执行联接 A revision

  • 快速将列的副本添加到 MySQL 表

    我需要一种快速的方法来复制表中的 DATETIME 列并为其指定一个新名称 我的表中有一个名为 myDate 的列 名为 myResults 我需要一个查询来在名为 newDate 的表中创建一个新列 该列的数据与 myDate 列完全相同

  • MySQL 转储未知选项“-no-beep”

    在旧服务器上我使用了mysql转储命令来备份 MySQL 数据库 在新服务器上 MySQL 版本为 5 6 相同的命令给出了错误 unknown option no beep 无论它插入什么 我也在互联网上搜索过 但找不到任何帮助 在 my

  • 内部 while 循环不工作

    这是我项目网页上的代码片段 这里我想显示用户选择的类别 然后想显示属于该类别的主题 在那里 用户可以拥有多个类别 这没有问题 我可以在第一个 while 循环中打印所有这些类别 问题是当我尝试打印主题时 结果只显示一行 但每个类别中有更多主

  • 我是否需要在外键上指定 ON DELETE NO ACTION？

    我有以下与 SQL Server 2012 一起使用的 DDL CREATE TABLE Subject SubjectId INT IDENTITY 1 1 NOT NULL Name NVARCHAR 50 Not NULL CONST

随机推荐

  • ARM Cortex-A7时钟树综合实战分析

    ARM Cortex A7时钟树综合实战分析 文章右侧广告为官方硬广告 与吾爱IC社区无关 用户勿点 点击进去后出现任何损失与社区无关 吾爱 IC 高端技术交流社区 知识星球目前已经拥有1040 位星球成员 目前数字 IC 领域最大的高端技

  • 判断玩家是否在敌人的可视范围的方法Physics.OverlapSphere

    Physics OverlapSphere Vector 3 position float radius 该方法判断以一个点为中心 半径为radius的球中的碰撞体集合 所以返回值可以用 var 如var colliders Physics

  • spring mvc 控制器返回 HTML 页面

    之前的页面都是 jsp的 后面需要改为html 如下代码 之前的Log 是 jsp 后面改为 html Log html 页面单独运行正常 但是在控制器跳转是出错 如下 RequestMapping value login out publ

  • HCIPR&S222-V2.5一些总结

    1 IP Precedence取值中 代表immediate的是2 2 VRRP中路由器已经为Master设备 不会被更高优先级的Backup设备抢占 如果出现故障的情况会被Backup设备抢占 故障恢复后重新抢占为Master设备 默认为

  • java jmf视频播放器无法播放视频问题

    初学java 谢了一个参照网上代码写了个java jmf视频播放器 运行调试代码没问题 可是被这个avi视频格式纠结的要死 换了n多个avi格式视频依然不行 经过查阅资料发现现在网上流行的avi格式并不是真正的avi格式 大都是其他格式转化

  • 原生AJAX 的基本使用

    1 准备工作 1 1 安装node js Node js 中文网 1 2 安装express 服务端框架 Express 基于 Node js 平台的 web 应用开发框架 Express 中文文档 Express 中文网 1 2 1 初始

  • MYSQL多表连查

    前言 多表查询 也称为关联查询 指两个或更多个表一起完成查询操作 前提条件 这些一起查询的表之间是有关系的 一对一 一对多 它们之间一定是有关联字段 这个关联字段可能建立了外键 也可能没有建立外键 比如 员工表和部门表 这两个表依靠 部门编

  • 诚邀您参加Go+1.0发布会！与大咖共同探索行业生态！

    你想参与iPhone13抽奖吗 你想和大咖面对面交流吗 你想收获最前沿的行业动态吗 10 月15 日 Go Together Go 1 0发布会暨Go 开发者基金会启动仪式重磅来袭 活动中除七牛云 CEO Go 语言发明人许式伟以及Go 开

  • 传奇私服游戏支付接口申请（已解决）

    传奇游戏是一款经典打怪升级 PK游戏 盛大游戏公司于2001年9月发布 随后出现了众多传奇私服游戏版本 到现在依然受很多人喜爱 经历了多次升级换代 现已转变为游戏体验更加细腻的页游和手游 成为了小成本高收益的热门游戏 受到各游戏服务商和玩家

  • IO流基础总结

    IO流 File 1 File类概述和构造方法 File 它是文件和目录路径名的抽象表示 文件和目录是可以通过File封装成对象的 对于File而言 其封装的并不是一个真正存在的文件 仅仅是一个路径名 而已 它可以是存在的 也可以是不存在的

  • Allegro PCB设计中：结构文件DXF导入、更新、PCB板框更改

    1 将结构工程师输出的DXF文件导入到Allegro PCB设计中 文章来源地址https www yii666 com blog 453846 html action onAll 2 结构文件DXF多次更改导致PCB板框尺寸涉及的修改 1

  • C++STL：vector

    C STL中的vector是一种动态分配的数组 可以在运行时动态增加和删除元素 它也是C 中最常用的容器之一 以下是vector的使用介绍 引入头文件 在使用vector之前 需要添加头文件 include

  • nrm 源管理

    什么是nrm nrm 是一个 npm 源管理器 你可以快速地在 npm源间切换 大家在开发中可能会经常切换 npm 源 我们会使用命令切换 如 npm set registry https registry npm taobao org 或

  • Vue style中的 scoped 属性

    Vue 中存在 scoped 属性 HTML5中也存在一个 scoped 属性 而且 这两者都是针对 css 样式处理的属性 所以很多文章在 解释 Vue scoped 的时候 都会把两者混为一谈 直接进把 HTML5 scoped 的定义

  • Login控件用法，用自己的数据库提供login控件的数据源

    一 在配置web config文件 web config 文件

  • 基于SuperSocket 1.6版本的自定义帧过滤的源码分析（实现MODBUS通信）

    一 SuperSocket 1 6 自定义帧过滤的官方文档地址 http docs supersocket net v1 6 zh CN Implement Your Own Communication Protocol with IReq

  • QT信号槽机制

    信号槽 信号槽是QT中用于对象间通信的一种机制 也是QT的核心机制 在GUI编程中 我们经常需要在改变一个组件的同时 通知另一个组件做出响应 例如 一开始我们的Find按钮是未激活的 用户输入要查找的内容后 查找按钮就被激活 这就是输入框与

  • 细线表格 border-collapse:collapse;/*细线表格，合并边框*/

  • vue3获取reactive代理的target原数组

    使用toRaw获取 可从vue引入toRaw const t3 reactive age name 3 console log toRaw t3 age 官方示例

  • MySQL-SQL全部锁详解（下）

    作者 小刘在C站 个人主页 小刘主页 努力不一定有回报 但一定会有收获加油 一起努力 共赴美好人生 学习两年总结出的运维经验 以及思科模拟器全套网络实验教程 专栏 云计算技术 小刘私信可以随便问 只要会绝不吝啬 感谢CSDN让你我相遇 前言

热门标签