MySQL-SQL全部锁详解(上)

这篇具有很好参考价值的文章主要介绍了MySQL-SQL全部锁详解(上)。希望对大家有所帮助。如果存在错误或未考虑完全的地方,请大家不吝赐教,您也可以点击"举报违法"按钮提交疑问。

♥️作者:小刘在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 隔离级别下都支持。
MySQL-SQL全部锁详解(上)
间隙锁( Gap Lock ):锁定索引记录间隙(不含该记录),确保索引记录间隙不变,防止其他事
务在这个间隙进行 insert ,产生幻读。在 RR 隔离级别下都支持。
MySQL-SQL全部锁详解(上)
临键锁( Next-Key Lock ):行锁和间隙锁组合,同时锁住数据,并锁住数据前面的间隙 Gap
RR 隔离级别下支持。

MySQL-SQL全部锁详解(上)

 4.2 行锁

1). 介绍

InnoDB 实现了以下两种类型的行锁:
共享锁(S ):允许一个事务去读一行,阻止其他事务获得相同数据集的排它锁。
排他锁( X ):允许获取排他锁的事务更新数据,阻止其他事务获得相同数据集的共享锁和排他
锁。
两种行锁的兼容情况如下 :
MySQL-SQL全部锁详解(上)

 常见的SQL语句,在执行时,所加的行锁如下:

MySQL-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语句,执行时,不会加锁。

MySQL-SQL全部锁详解(上)

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

MySQL-SQL全部锁详解(上)

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

MySQL-SQL全部锁详解(上)

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

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

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

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

stu 表中数据如下 :
MySQL-SQL全部锁详解(上)

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

MySQL-SQL全部锁详解(上)

在客户端一中,开启事务,并执行 update 语句,更新 name Lily 的数据,也就是 id 19 的记录 。
然后在客户端二中更新 id 3 的记录,却不能直接执行,会处于阻塞状态,为什么呢?
原因就是因为此时,客户端一,根据 name 字段进行更新时, name 字段是没有索引的,如果没有索引, 此时行锁会升级为表锁 ( 因为行锁是对索引项加的锁,而 name 没有索引 )
接下来,我们再针对 name 字段建立索引,索引建立之后,再次做一个测试:
MySQL-SQL全部锁详解(上)
此时我们可以看到,客户端一,开启事务,然后依然是根据 name 进行更新。而客户端二,在更新 id 3的数据时,更新成功,并未进入阻塞状态。 这样就说明,我们根据索引字段进行更新操作,就可以避免行锁升级为表锁的情况。

4.3 间隙锁&临键锁

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

示例演示

A. 索引上的等值查询 ( 唯一索引 ) ,给不存在的记录加锁时 , 优化为间隙锁 。
MySQL-SQL全部锁详解(上)
B. 索引上的等值查询 ( 非唯一普通索引 ) ,向右遍历时最后一个值不满足查询需求时, next-key
lock 退化为间隙锁。

介绍分析一下:

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

 MySQL-SQL全部锁详解(上)

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

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

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

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

♥️这里是小刘,励志用心做好每一篇文章,谢谢大家文章来源地址https://www.toymoban.com/news/detail-501722.html

到了这里,关于MySQL-SQL全部锁详解(上)的文章就介绍完了。如果您还想了解更多内容,请在右上角搜索TOY模板网以前的文章或继续浏览下面的相关文章,希望大家以后多多支持TOY模板网!

本文来自互联网用户投稿,该文观点仅代表作者本人,不代表本站立场。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。如若转载,请注明出处: 如若内容造成侵权/违法违规/事实不符,请点击违法举报进行投诉反馈,一经查实,立即删除!

领支付宝红包 赞助服务器费用

相关文章

  • Mysql-SQL优化

    插入数据优化主要是对于insert进行批量插入时优化,下面我们就来开始分析一下 如果我们需要一次性往数据库表中插入多条记录,一条一条插入效率实在太慢了。 insert into tb_test values(1,\\\'tom\\\'); insert into tb_test values(2,\\\'cat\\\'); insert into tb_test values(3,\\\'jerry\\\'); ..... 可以从以下三个方面进

    2024年02月01日
    浏览(36)
  • MySQL-SQL视图详细

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

    2024年02月09日
    浏览(41)
  • MySQL-sql的优化

    表的设计优化 索引优化 SQL语句优化 主从复制、读写分离 分库分表 比如设置合适的数值(tinyint int bigint),要根据实际情况选择 比如设置合适的字符串类型(char和varchar) char定长效率高,varchar可变长度,效率稍低 SELECT语句务必指明字段名称(避免直接使用select * ) SQL语句要避免造

    2024年02月05日
    浏览(31)
  • MySQL-SQL InnoDB引擎 (中)

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

    2024年02月11日
    浏览(37)
  • MySQL-SQL InnoDB引擎 (上)

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

    2024年02月11日
    浏览(38)
  • mysql-sql性能分析工具

            MySQL 客户端连接成功后,通过 show [session|global] status 命令可以提供服务器状态信息。通过如下指令,可以查看当前数据库的INSERT、UPDATE、DELETE、SELECT的访问频次: -- session 是查看当前会话 ; -- global 是查询全局数据 ; SHOW GLOBAL STATUS LIKE \\\'Com_\\\'; 慢查询日志记录了所有执

    2024年02月12日
    浏览(49)
  • MySQL-SQL InnoDB引擎 (下)

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

    2024年02月12日
    浏览(44)
  • 万能的微信小程序个人主页:商城系统个人主页、外卖系统个人主页、购票系统个人主页等等【全部源代码分享+页面效果展示+直接复制粘贴编译即可】

            以下给出来四个常见的小程序个人主页,分别是商城系统个人主页,外卖系统个人主页,挂号系统个人主页,电影购票系统个人主页。包括完整的页面布局代码,完整的样式代码。使用的时候,只需要将页面代码和样式代码复制到自己项目对应的页面即可。而且可

    2024年02月11日
    浏览(64)
  • Kafka单机到集群- 自动化- SSL以及全部配置文件 作者精选

    本文实现kafka集群的配置和启动过程。并将脚本自动化,使得通用性、独立性更强。 用途 参考资料 kafka官方文档:包含安装部署、配置项、api使用等详细信息 附件: Kafka并不难学!:入门、进阶、商业实战 (邓杰) :包含kafka入门和linux快捷操作等系统化认识的内容。 名词 文

    2024年04月13日
    浏览(48)
  • 小刘同学的 virtuoso IC 618 版图初学

    前言        这个是我初步学习版图的一个记录帖。 快捷键 按键 功能 Q 修改元件参数 E 显示设置(设置最小移动步长) R 画矩形 O 画过孔 P 画path K 画尺子(shift+K 取消layout上所有尺子) M 移动器件 S 伸缩,调整器件长度 L 加标签(标记vdd gnd vin vout等) shift+F 显示元件内部图(

    2024年02月06日
    浏览(36)

觉得文章有用就打赏一下文章作者

支付宝扫一扫打赏

博客赞助

微信扫一扫打赏

请作者喝杯咖啡吧~博客赞助

支付宝扫一扫领取红包,优惠每天领

二维码1

领取红包

二维码2

领红包