【MYSQL锁】透彻地理解MYSQL锁

🔥作者主页:小林同学的学习笔录

🔥mysql专栏:小林同学的专栏

目录

1.锁

1.1  概述 

1.2  全局锁

1.2.1  语法

1.2.1.1   加全局锁

1.2.1.2   数据备份

1.2.1.3   释放锁

1.2.1.4   特点

1.2.1.5   演示

1.3   表级锁

1.3.1  介绍

1.3.2  表锁

1.3.2.1  语法

1.3.2.2  特点

1.3.2.3  结论

1.3.3  元数据锁

1.3.4  意向锁

1.3.4.1  介绍

1.3.4.2  分类

1.3.4.3  演示

1.4  行级锁

1.4.1  介绍

1.4.2  行锁

1.4.3  演示

1.4.4  间隙锁&临键锁

1.4.4.1  示例演示

1.锁


1.1  概述 


锁是计算机协调多个进程或线程并发访问某一资源的机制。在数据库中,除传统的计算资源

(CPU、RAM、I/O)的争用以外,数据也是一种供许多用户共享的资源。

如何保证数据并发访问的一致性、有效性是所有数据库必须解决的一个问题,锁冲突也是影响数据

库并发访问性能的一个重要因素。从这个角度来说,锁对数据库而言显得尤其重要,也更加复杂。

MySQL中的锁,按照锁的力度分,分为以下三类:

  • 全局锁:锁定数据库中的所有表。
  • 表级锁:每次操作锁住整张表。
  • 行级锁:每次操作锁住对应的行数据。


在介绍锁之前先回顾一下DML、DQL、DDL分别代表什么?


数据操作语言(DML):DML 用于对数据库中的数据执行操作,例如插入、更新、删除、查询数

据,简称增删改查,尽管 SELECT 语句通常被归类为 DQL,但它也可以在某种程度上被认为是

DML,因为它允许检索数据。


数据查询语言(DQL):DQL 用于从数据库中检索数据。它专门用于执行查询操作


数据定义语言(DDL):DDL 用于定义数据库的结构,包括创建、修改和删除数据库对象,

例如(表、索引、视图等)。DDL 的操作影响数据库的整体结构。

1.2  全局锁


全局锁(Global Lock)是数据库管理系统中的一种锁定机制,通常用于锁定整个数据库实例,而

不是单个表或行。全局锁可以阻止对整个数据库的写入操作,但通常不会阻止读取操作。这种锁定

机制在某些情况下可以用于数据库备份、恢复或维护操作。

其典型的使用场景是做全库的逻辑备份,对所有的表进行锁定,从而获取一致性视图,保证数据的

完整性.

为什么全库逻辑备份,就需要加全就锁呢?

①.先来分析一下不加全局锁,可能存在的问题。

假设在数据库中存在这样三张表: tb_stock 库存表,tb_order 订单表,tb_orderlog 订单日志表。

在进行数据备份时,先备份了tb_stock库存表。

然后接下来,在业务系统中,执行了下单操作,扣减库存,生成订单(更新tb_stock表,插入

tb_order表)。

然后再执行备份 tb_order表的逻辑。

业务中执行插入订单日志操作。

最后,又备份了tb_orderlog表。

此时备份出来的数据,是存在问题的。因为备份出来的数据,tb_stock表与tb_order表的数据不一

致(有最新操作的订单信息,但是库存数没减)。

②.再来分析一下加了全局锁后的情况

对数据库进行进行逻辑备份之前,先对整个数据库加上全局锁,一旦加了全局锁之后,其他的

DDL、DML全部都处于阻塞状态,但是可以执行DQL语句,也就是处于只读状态,而数据备份就

是查询操作。那么数据在进行逻辑备份的过程中,数据库中的数据就是不会发生变化的,这样就保

证了数据的一致性和完整性。

1.2.1  语法


1.2.1.1   加全局锁

flush tables with read lock ;

1.2.1.2   数据备份


mysqldump -uroot –p1234 itcast > itcast.sql

mysqldump -uXxx –pXxx 数据库名 > 磁盘地址  +  数据库名.sql

例如:mysqldump -uXxx –pXxx db_01 > D:/db_01

1.2.1.3   释放锁


unlock tables ;

1.2.1.4   特点


数据库中加全局锁,是一个比较重的操作,存在以下问题:

如果在主库上备份,那么在备份期间都不能执行更新,业务基本上就得停摆。

如果在从库上备份,那么在备份期间从库不能执行主库同步过来的二进制日志(binlog),会导致

主从延迟。


在InnoDB引擎中,我们可以在备份时加上参数 --single-transaction 参数来完成不加锁的一致性数

据备份。

mysqldump --single-transaction -uroot –p123456 itcast > itcast.sql

1.2.1.5   演示

开启两个命令行

第一个命令行


第二个命令行

会处于阻塞状态,光标一直在闪,只有全局锁被释放才可以执行相应的操作

1.3   表级锁


1.3.1  介绍


表级锁,每次操作锁住整张表锁定力度大,发生锁冲突的概率最高,并发度最低。应用在

MyISAM、InnoDB、BDB等存储引擎中。


对于表级锁,主要分为以下三类:

  • 表锁
  • 元数据锁(meta data lock,MDL)
  • 意向锁


1.3.2  表锁


对于表锁,分为两类:

①.表共享读锁(read lock)

②.表独占写锁(write lock)


1.3.2.1  语法


加锁:lock tables 表名... read/write。

释放锁:unlock tables /  客户端断开连接 。


1.3.2.2  特点


①.读锁

左侧为客户端一,对指定表加了读锁,不会影响右侧客户端二的读,但是会阻塞右侧客户端的写。


②.写锁

左侧为客户端一,对指定表加了写锁,会阻塞右侧客户端的读和写。


1.3.2.3  结论


读锁不会阻塞其他客户端的读,但是会阻塞写。写锁既会阻塞其他客户端的读,又会阻塞其他客户

端的写。


1.3.3  元数据锁


MDL(meta data lock)加锁过程是系统自动控制,无需显式使用,在访问一张表的时候会自动加

上。MDL锁主要作用是维护表元数据的数据一致性,在表上有活动事务的时候,不可以对元数据进

行写入操作为了避免DML与DDL冲突,保证读写的正确性。

注意:有元数据锁只有在事务开启,才有的锁,当事务提交之后相应的锁会被释放.


这里的元数据,大家可以简单理解为就是一张表的表结构。 也就是说,某一张表涉及到未提交的

事务时,是不能够修改这张表的表结构的。

在MySQL5.5中引入了MDL,当对一张表进行增删改查的时候,加MDL读锁(共享);当对表结构进

行变更操作的时候,加MDL写锁(排他),另外共享锁是兼容的。


常见的SQL操作时,所添加的元数据锁:


1.3.3.1  演示


①.当执行SELECT、INSERT、UPDATE、DELETE等语句时,添加的是元数据共享锁

(SHARED_READ / SHARED_WRITE),之间是兼容的,所以不会出现阻塞状态。


②.当执行SELECT语句时,添加的是元数据共享锁(SHARED_READ),会阻塞元数据排他锁

(EXCLUSIVE),之间是互斥的,所以出现了阻塞状态。

我们可以通过下面的SQL,来查看数据库中的元数据锁的情况:

select object_type,object_schema,object_name,lock_type,lock_duration from

performance_schema.metadata_locks ;


1.3.4  意向锁


1.3.4.1  介绍


为了避免DML在执行时,加的行锁与表锁的冲突在InnoDB中引入了意向锁,使得表锁不用检查

每行数据是否加行锁,使用意向锁来减少表锁的检查。

假如没有意向锁,客户端一对表加了行锁后,客户端二如何给表加表锁呢?

来通过示意图简单分析一下:

首先客户端一,开启一个事务,然后执行DML操作,在执行DML语句时,会对涉及到的行加锁。

当客户端二,想对这张表加表锁时,会检查当前表是否有对应的行锁,如果没有,则添加表锁,此

时就会从第一行数据,检查到最后一行数据,效率较低。

有了意向锁之后 :

客户端一,在执行DML操作时,会对涉及的行加行锁,同时也会对该表加上意向锁。

而其他客户端,在对这张表加表锁的时候,会根据该表上所加的意向锁来判定是否可以成功加上表

锁,而不用逐行判断行锁情况了。


1.3.4.2  分类


①.意向共享锁(IS): 由语句select ... lock in share mode添加 。 与 表锁共享锁(read)兼容,与表锁

排他锁(write)互斥。

②.意向排他锁(IX): 由insert、update、delete、select...for update添加 。与表锁共享锁(read)及排

他锁(write)都互斥,意向锁之间不会互斥。


一旦事务提交了,意向共享锁、意向排他锁,都会自动释放。


可以通过以下SQL,查看意向锁及行锁的加锁情况:

select object_schema,object_name,index_name,lock_type,lock_mode,lock_data from

performance_schema.data_locks;


1.3.4.3  演示


①.意向共享锁与表读锁是兼容的

由于select语句不会自动加行锁,需要手动加行锁

select * from score where id = 1 lock in share mode;

②.意向排他锁与表读锁、写锁都是互斥的

insert、update、delete语句会自动加行锁


1.4  行级锁


1.4.1  介绍


行级锁,每次操作锁住对应的行数据。锁定力度最小,发生锁冲突的概率最低,并发度最高。应用

在InnoDB存储引擎中。

InnoDB的数据是基于索引组织的,行锁是通过对索引上的索引项加锁来实现的,而不是对记录加

的锁。对于行级锁,主要分为以下三类:

①.行锁(Record Lock):锁定单个行记录的锁,防止其他事务对此行进行update和delete。

在RC、RR隔离级别下都支持。

②.间隙锁(Gap Lock):锁定索引记录间隙(不含该记录),确保索引记录间隙不变,防止其他

事务在这个间隙进行insert,产生幻读。在RR隔离级别下都支持。

③.临键锁(Next-Key Lock):行锁和间隙锁组合,同时锁住数据,并锁住数据前面的间隙Gap。

在RR隔离级别下支持。

1.4.2  行锁


共享锁(S):允许一个事务去读一行,阻止其他事务获得相同数据集的排它锁.就是允许一个事务

select,另外事务可以获得相同数据的共享锁,但是不能获得相同数据集的排他锁。


排他锁(X):允许获取排他锁的事务更新数据,阻止其他事务获得相同数据集的共享锁和排他

锁。


两种行锁的兼容情况如下:

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


1.4.3  演示


①.针对唯一索引进行检索时,对已存在的记录进行等值匹配时,将会自动优化为行锁。

②.InnoDB的行锁是针对于索引加的锁,不通过索引条件检索数据,那么InnoDB将对表中的所有记录加锁,此时 就会升级为表锁。


可以通过以下SQL,查看意向锁及行锁的加锁情况:

select object_schema,object_name,index_name,lock_type,lock_mode,lock_data from

performance_schema.data_locks;

  • 加共享锁,共享锁与共享锁之间兼容。
  • 共享锁与排他锁之间互斥。

  • 排它锁与排他锁之间互斥

  • 无索引行锁升级为表锁

注意:这里面经常会手动开启事务的原因是为了演示效果,如果是自动开启事务会自动提交事务,

会把锁给释放,因此看不出效果,但是原理还是一样的.

1.4.4  间隙锁&临键锁


默认情况下,InnoDB在 REPEATABLE READ事务隔离级别运行,InnoDB使用 next-key 锁进行搜

索和索引扫描,以防止幻读

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


②.索引上的等值查询(非唯一普通索引),向右遍历时最后一个值不满足查询需求时,next-key lock

退化为间隙锁。

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


注意:间隙锁唯一目的是防止其他事务插入间隙。间隙锁可以共存,一个事务采用的间隙锁不会阻

止另一个事务在同一间隙上采用间隙锁。


1.4.4.1  示例演示


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


②.索引上的等值查询(非唯一普通索引),向右遍历时最后一个值不满足查询需求时,next-key lock

退化为间隙锁。

介绍分析一下:


我们知道InnoDB的B+树索引,叶子节点是有序的双向链表。 假如,我们要根据这个二级索引查询

值为18的数据,并加上共享锁,我们是只锁定18这一行就可以了吗? 

并不是,因为是非唯一索引,这个结构中可能有多个18的存在,所以在加锁时会继续往后找,找

到一个不满足条件的值(当前案例中也就是29)。此时会对18加临键锁,并对29之前的间隙加锁

这里的临键锁(行锁+间隙锁),还会锁住age=3的行,并且还会锁住主键为1-3之间的间隙


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

所以数据库数据在加锁是,就是将19加了行锁,25的临键锁(包含25及25之前的间隙),正无穷

的临键锁(正无穷及之前的间隙)。

麒麟而非淇淋,不是干货不制作https://blog.csdn.net/2301_77358195

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

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

相关文章

spring-cloud微服务openfeign

Spring Cloud openfeign对Feign进行了增强,使其支持Spring MVC注解,另外还整合了Ribbon和Nacos,从而使得Feign的使用更加方便 优势,openfeign可以做到使用HTTP请求远程服务时就像洞用本地方法一样的体验,开发者完全感…

计算机视觉 | 基于 ORB 特征检测器和描述符的全景图像拼接算法

Hi,大家好,我是半亩花海。本项目实现了基于 ORB 特征检测器和描述符的全景图像拼接算法,能够将两张部分重叠的图像拼接成一张无缝连接的全景图像。 文章目录 一、随机抽样一致算法二、功能实现三、代码解析四、效果展示五、完整代码 一、随机…

如何合理利用Vue 3中的ref和reactive

🤍 前端开发工程师、技术日更博主、已过CET6 🍨 阿珊和她的猫_CSDN博客专家、23年度博客之星前端领域TOP1 🕠 牛客高级专题作者、打造专栏《前端面试必备》 、《2024面试高频手撕题》 🍚 蓝桥云课签约作者、上架课程《Vue.js 和 E…

uni-app实现分页--(1)准备工作,首页下拉触底加载更多

实现流程如下: 分析:需要在滚动容器中添加滚动触底,在猜你喜欢中获取数据。难点:如何在父页面调用子组件内的方法。父组件中用ref,并定义组件实例类型,子组件中暴露方法 具体代码如下: 1.在父组件中添加…

去中心化社交媒体:分析 Facebook 在区块链平台上的角色

在当今数字时代,社交媒体已经成为人们日常生活中不可或缺的一部分。然而,随着人们对数据隐私和信息控制的关注不断增加,传统的中心化社交媒体平台也面临着越来越多的质疑和挑战。为了应对这些挑战,越来越多的人开始探索去中心化社…

关于Renesas R7 的选项字节开关看门狗

Renesas看门狗的模式是在选项字节中进行配置的,OPBT0的寄存器说明如下, 关于看门狗模式 : 和看门狗喂狗方式: 我们选择关闭看门狗(也就是配置31位为软件触发看门狗开始,然后不启动就相当于关闭&#xff09…

【动手学深度学习】15_汉诺塔问题

注: 本系列仅为个人学习笔记,学习内容为《算法小讲堂》(视频传送门),通俗易懂适合编程入门小白,需要具备python语言基础,本人小白,如内容有误感谢您的批评指正 汉诺塔(To…

2024谷歌Google广告推广投放怎么做?如何收费?

当今全球市场,谷歌作为全球最大的搜索引擎,其广告服务——Google Ads,已成为企业触达全球消费者、提升品牌知名度、驱动业务增长的首选渠道之一。尤其是在2024年,随着数字营销环境的持续演进,精准、高效地运用Google A…

MySQL-创建和管理表:基础知识、创建和管理数据库、创建表、修改表、重命名表、删除表、清空表、拓展

创建和管理表 1. 基础知识1.1 一条数据存储的过程1.2 标识符命名规则1.3 MySQL中的数据类型 2. 创建和管理数据库2.1 创建数据库2.2 使用数据库2.3 修改数据库2.4 删除数据库 3. 创建表3.1 创建方式13.2 创建方式23.3 查看数据表结构 4. 修改表4.1 追加一个列4.2 修改一个列4.3…

Prometheus+Grafana监控K8S集群(基于K8S环境部署)

目录 一.环境信息二.部署提前工作三.部署Prometheus监控系统四.部署Node_exporter组件五.部署Kube_state_metrics组件六.部署Grafana可视化平台七.Grafana接入Prometheus数据八.Grafana添加监控模板九.拓展 一.环境信息 1.服务器及k8s版本信息 IP地址主机名称角色版本192.168…

SAM功能改进VRP-SAM论文解读VRP-SAM: SAM with Visual Reference Prompt

现已总结SAM多方面相关的论文解读,具体请参考该专栏的置顶目录篇 一、总结 1. 简介 发表时间:2024年3月30日 论文: 2402.17726.pdf (arxiv.org)https://arxiv.org/pdf/2402.17726.pdf代码: syp2ysy/VRP-SAM (github.com)htt…

JVM面试整理--对象的创建和堆

文章目录 对象的创建过程是怎样的?对象在内存中的结构是怎样的(专业的叫法:对象的内存布局)对象在内存分配时使用的哪种方式(有的地方也称为:分配算法)知道什么是“指针碰撞”吗?知道什么是“空…

电商技术揭秘十八:电商平台的云计算与大数据应用小结

电商技术揭秘相关系列文章 电商技术揭秘一:电商架构设计与核心技术 电商技术揭秘二:电商平台推荐系统的实现与优化 电商技术揭秘三:电商平台的支付与结算系统 电商技术揭秘四:电商平台的物流管理系统 电商技术揭秘五&#xf…

【产品】ANET智能通信管理机 物联网网关 电力监控/能耗监测/能源管理系统

产品概述 本系列智能通信管理机是一款采用嵌入式硬件计算机平台,具有多个下行通信接口及一个或者多个上行网络接口,用于将一个目标区域内所有的智能监控/保护装置的通信数据整理汇总后,实时上传主站系统,完成遥信、遥测等能源数据…

遥感图像处理:从畸变消除到专题信息提取

​ ​ ​在遥感技术的应用中,图像处理是不可或缺的关键步骤。从消除各种辐射畸变和几何畸变,到利用增强技术突出景物的光谱和空间特征,再到进一步理解、分析和判别处理后的图像,这一过程为我们呈现了一幅幅更为真实、清晰的…

Elasticsearch:从 ES|QL 到 PHP 对象

作者:来自 Elastic Enrico Zimuel 从 elasticsearch-php v8.13.0 开始,你可以执行 ES|QL 查询并将结果映射到 stdClass 或自定义类的 PHP 对象。 ES|QL ES|QL 是 Elasticsearch 8.11.0 中引入的一种新的 Elasticsearch 查询语言。 目前,它在…

基于GRU实现评论文本情感分析

一、问题建模 在线评论的细粒度情感分析对于深刻理解商家和用户、挖掘用户情感等方面有至关重要的价值,并且在互联网行业有极其广泛的应用,主要用于个性化推荐、智能搜索、产品反馈、业务安全等。此博文,共包含6大类20个细粒度要素的情感倾…

SpringBoot中的Redis的简单使用

在Spring Boot项目中使用Redis作为缓存、会话存储或分布式锁等组件,可以简化开发流程并充分利用Redis的高性能特性。以下是使用Spring Boot整合Redis的详细步骤: 1. 环境准备 确保开发环境中已安装: Java:用于编写和运行Spring…

RISC-V特权架构 - 中断注入

中断注入 1 中断注入的作用2 mip寄存器3 中断注入后的处理过程 本文属于《 RISC-V指令集基础系列教程》之一,欢迎查看其它文章。 1 中断注入的作用 中断注入,就是在M模式下,手动向S模式去产生一个中断。 比如:向mip寄存器的bit5…

✌2024/4/6—力扣—最长公共前缀✌

代码实现&#xff1a; char *longestCommonPrefix(char **strs, int strsSize) {if (strsSize 0) {return "";}for (int i 0; i < strlen(strs[0]); i) { // 列for (int j 1; j < strsSize; j) { // 行if (strs[0][i] ! strs[j][i]) { // 如果比较字符串的第…