MySQL面试题-锁(答案版)

news/2024/4/21 13:01:07/文章来源:https://blog.csdn.net/zhangzhanbin/article/details/136446969

在这里插入图片描述

1、MySQL 有哪些锁?

(1)全局锁

加了全局锁之后,整个数据库就处于只读状态了,这时其他线程执行以下操作,都会被阻塞:

对数据的增删改操作,比如 insert、delete、update等语句;
对表结构的更改操作,比如 alter table、drop table 等语句。

// 加全局锁
flush tables with read lock
// 解锁
unlock tables

使用场景:全局锁主要应用于做全库逻辑备份,这样在备份数据库期间,不会因为数据或表结构的更新,而出现备份文件的数据与预期的不一样。

缺点:加上全局锁,意味着整个数据库都是只读状态。那么如果数据库里有很多数据,备份就会花费很多的时间,关键是备份期间,业务只能读数据,而不能更新数据,这样会造成业务停滞。

如果数据库的引擎支持的事务支持可重复读的隔离级别,那么在备份数据库之前先开启事务,会先创建 Read View,然后整个事务执行期间都在用这个 Read View,而且由于 MVCC 的支持,备份期间业务依然可以对数据进行更新操作。
因为在可重复读的隔离级别下,即使其他事务更新了表的数据,也不会影响备份数据库时的 Read View,这就是事务四大特性中的隔离性,这样备份期间备份的数据一直是在开启事务时的数据。

(2)表级锁

2-1:表锁
表锁除了会限制别的线程的读写外,也会限制本线程接下来的读写操作。

//表级别的共享锁,也就是读锁;
lock tables t_student read;
//表级别的独占锁,也就是写锁;
lock tables t_stuent write;
// 解锁
unlock tables;
// 另外,当会话退出后,也会释放所有表锁。

2-2:元素据锁
我们不需要显示的使用 MDL,因为当我们对数据库表进行操作时,会自动给这个表加上 MDL:

01.对一张表进行 CRUD 操作时,加的是 MDL 读锁;
02.对一张表做结构变更操作的时候,加的是 MDL 写锁;

MDL 是在事务提交后才会释放,这意味着事务执行期间,MDL 是一直持有的。

申请 MDL 锁的操作会形成一个队列,队列中写锁获取优先级高于读锁,一旦出现 MDL 写锁等待,会阻塞后续该表的所有 CRUD 操作。

所以为了能安全的对表结构进行变更,在对表结构变更前,先要看看数据库中的长事务,是否有事务已经对表加上了 MDL 读锁,如果可以考虑 kill 掉这个长事务,然后再做表结构的变更。

2-3:意向锁
意向锁的目的是为了快速判断表里是否有记录被加锁。

加锁场景:

01.在使用 InnoDB 引擎的表里对某些记录加上「共享锁」之前,需要先在表级别加上一个「意向共享锁」;
02.在使用 InnoDB 引擎的表里对某些纪录加上「独占锁」之前,需要先在表级别加上一个「意向独占锁」;

意向共享锁和意向独占锁是表级锁,不会和行级的共享锁和独占锁发生冲突,而且意向锁之间也不会发生冲突,只会和共享表锁(lock tables … read)和独占表锁(lock tables … write)发生冲突。

如果没有「意向锁」,那么加「独占表锁」时,就需要遍历表里所有记录,查看是否有记录存在独占锁,这样效率会很慢。

那么有了「意向锁」,由于在对记录加独占锁前,先会加上表级别的意向独占锁,那么在加「独占表锁」时,直接查该表是否有意向独占锁,如果有就意味着表里已经有记录被加了独占锁,这样就不用去遍历表里的记录。

2-4 AUTO-INC 锁
表里的主键通常都会设置成自增的,这是通过对主键字段声明 AUTO_INCREMENT 属性实现的。

AUTO-INC 锁是特殊的表锁机制,锁不是再一个事务提交后才释放,而是再执行完插入语句后就会立即释放。

在 MySQL 5.1.22 版本开始,InnoDB 存储引擎提供了一种轻量级的锁来实现自增。
一样也是在插入数据的时候,会为被 AUTO_INCREMENT 修饰的字段加上轻量级锁,然后给该字段赋值一个自增的值,就把这个轻量级锁释放了,而不需要等待整个插入语句执行完后才释放锁。InnoDB 存储引擎提供了个 innodb_autoinc_lock_mode 的系统变量,是用来控制选择用 AUTO-INC 锁,还是轻量级的锁。当 innodb_autoinc_lock_mode = 0,就采用 AUTO-INC 锁,语句执行结束后才释放锁;
当 innodb_autoinc_lock_mode = 2,就采用轻量级锁,申请自增主键后就释放锁,并不需要等语句执行后才释放。
当 innodb_autoinc_lock_mode = 1:普通 insert 语句,自增锁在申请之后就马上释放;类似 insert … select 这样的批量插入数据的语句,自增锁还是要等语句结束后才被释放;
当 innodb_autoinc_lock_mode = 2 时,并且 binlog_format = statement的时候,会出现主备数据不一致的情况。
而采用binlog_format = row,既能提升并发性,又不会出现数据一致性问题。

(3)行锁

InnoDB 引擎是支持行级锁的,而 MyISAM 引擎并不支持行级锁。

前面也提到,普通的 select 语句是不会对记录加锁的,因为它属于快照读。如果要在查询时对记录加行锁,可以使用下面这两个方式,这种查询会加锁的语句称为锁定读。

//对读取的记录加共享锁
select ... lock in share mode;
//对读取的记录加独占锁
select ... for update;

当事务提交了,锁就会被释放

3-1:Record Lock
Record Lock 称为记录锁,锁住的是一条记录。而且记录锁是有 S 锁和 X 锁之分的。

3-2:Gap Lock
Gap Lock 称为间隙锁,只存在于可重复读隔离级别,目的是为了解决可重复读隔离级别下幻读的现象。

假设,表中有一个范围 id 为(3,5)间隙锁,那么其他事务就无法插入 id = 4 这条记录了,这样就有效的防止幻读现象的发生。

间隙锁虽然存在 X 型间隙锁和 S 型间隙锁,但是并没有什么区别,间隙锁之间是兼容的,即两个事务可以同时持有包含共同间隙范围的间隙锁,并不存在互斥关系,因为间隙锁的目的是防止插入幻影记录而提出的。

3-3:Next-Key Lock
Next-Key Lock 称为临键锁,是 Record Lock + Gap Lock 的组合,锁定一个范围,并且锁定记录本身。

假设,表中有一个范围 id 为(3,5] 的 next-key lock,那么其他事务即不能插入 id = 4 记录,也不能修改 id = 5 这条记录。

next-key lock 即能保护该记录,又能阻止其他事务将新纪录插入到被保护记录前面的间隙中。

next-key lock 是包含间隙锁+记录锁的,如果一个事务获取了 X 型的 next-key lock,那么另外一个事务在获取相同范围的 X 型的 next-key lock 时,是会被阻塞的。

3-4:插入意向锁
一个事务在插入一条记录的时候,需要判断插入位置是否已被其他事务加了间隙锁(next-key lock 也包含间隙锁)。

如果有的话,插入操作就会发生阻塞,直到拥有间隙锁的那个事务提交为止(释放间隙锁的时刻),在此期间会生成一个插入意向锁,表明有事务想在某个区间插入新记录,但是现在处于等待状态。

MySQL 加锁时,是先生成锁结构,然后设置锁的状态,如果锁状态是等待状态,并不是意味着事务成功获取到了锁,只有当锁状态为正常状态时,才代表事务成功获取到了锁

插入意向锁名字虽然有意向锁,但是它并不是意向锁,它是一种特殊的间隙锁,属于行级别锁。

如果说间隙锁锁住的是一个区间,那么「插入意向锁」锁住的就是一个点。因而从这个角度来说,插入意向锁确实是一种特殊的间隙锁。

插入意向锁与间隙锁的另一个非常重要的差别是:尽管「插入意向锁」也属于间隙锁,但两个事务却不能在同一时间内,一个拥有间隙锁,另一个拥有该间隙区间内的插入意向锁(当然,插入意向锁如果不在间隙锁区间内则是可以的)。

2、MySQL是怎么加行锁的?

行级锁加锁规则比较复杂,不同的场景,加锁的形式是不同的。

加锁的对象是索引,加锁的基本单位是 next-key lock,它是由记录锁和间隙锁组合而成的,next-key lock 是前开后闭区间,而间隙锁是前开后开区间。

但是,next-key lock 在一些场景下会退化成记录锁或间隙锁。

在能使用记录锁或者间隙锁就能避免幻读现象的场景下, next-key lock 就会退化成退化成记录锁或间隙锁。

3、 next-key lock退化场景

(1)唯一索引等值查询

当查询的记录是「存在」的,在索引树上定位到这一条记录后,将该记录的索引中的 next-key lock 会退化成「记录锁」。
当查询的记录是「不存在」的,在索引树找到第一条大于该查询记录的记录后,将该记录的索引中的 next-key lock 会退化成「间隙锁」。

(2)唯一索引范围查询

当唯一索引进行范围查询时,会对每一个扫描到的索引加 next-key 锁,然后如果遇到下面这些情况,会退化成记录锁或者间隙锁:

01.针对「大于等于」的范围查询,因为存在等值查询的条件,那么如果等值查询的记录是存在于表中,那么该记录的索引中的 next-key 锁会退化成记录锁。
02.针对「小于或者小于等于」的范围查询,要看条件值的记录是否存在于表中:
(2-1)当条件值的记录不在表中,那么不管是「小于」还是「小于等于」条件的范围查询,扫描到终止范围查询的记录时,该记录的索引的 next-key 锁会退化成间隙锁,其他扫描到的记录,都是在这些记录的索引上加 next-key 锁。
(2-2)当条件值的记录在表中,如果是「小于」条件的范围查询,扫描到终止范围查询的记录时,该记录的索引的 next-key 锁会退化成间隙锁,其他扫描到的记录,都是在这些记录的索引上加 next-key 锁;如果「小于等于」条件的范围查询,扫描到终止范围查询的记录时,该记录的索引 next-key 锁不会退化成间隙锁。其他扫描到的记录,都是在这些记录的索引上加 next-key 锁。

(3)非唯一索引等值查询

当我们用非唯一索引进行等值查询的时候,因为存在两个索引,一个是主键索引,一个是非唯一索引(二级索引),所以在加锁时,同时会对这两个索引都加锁,但是对主键索引加锁的时候,只有满足查询条件的记录才会对它们的主键索引加锁。

当查询的记录「存在」时,由于不是唯一索引,所以肯定存在索引值相同的记录,于是非唯一索引等值查询的过程是一个扫描的过程,直到扫描到第一个不符合条件的二级索引记录就停止扫描,然后在扫描的过程中,对扫描到的二级索引记录加的是 next-key 锁,而对于第一个不符合条件的二级索引记录,该二级索引的 next-key 锁会退化成间隙锁。同时,在符合查询条件的记录的主键索引上加记录锁
当查询的记录「不存在」时,扫描到第一条不符合条件的二级索引记录,该二级索引的 next-key 锁会退化成间隙锁。因为不存在满足查询条件的记录,所以不会对主键索引加锁。

(4)非唯一索引范围查询

非唯一索引和主键索引的范围查询的加锁也有所不同,不同之处在于非唯一索引范围查询,索引的 next-key lock 不会有退化为间隙锁和记录锁的情况,也就是非唯一索引进行范围查询时,对二级索引记录加锁都是加 next-key 锁。

(5)没有加索引的查询

如果锁定读查询语句,没有使用索引列作为查询条件,或者查询语句没有走索引查询,导致扫描是全表扫描。那么,每一条记录的索引上都会加 next-key 锁,这样就相当于锁住的全表,这时如果其他事务对该表进行增、删、改操作的时候,都会被阻塞。

不只是锁定读查询语句不加索引才会导致这种情况,update 和 delete 语句如果查询条件不加索引,那么由于扫描的方式是全表扫描,于是就会对每一条记录的索引上都会加 next-key 锁,这样就相当于锁住的全表。

因此,在线上在执行 update、delete、select … for update 等具有加锁性质的语句,一定要检查语句是否走了索引,如果是全表扫描的话,会对每一个索引加 next-key 锁,相当于把整个表锁住了,这是挺严重的问题。

解决上述锁表的方案:将 MySQL 里的 sql_safe_updates 参数设置为 1,开启安全更新模式。
大致的意思是,当 sql_safe_updates 设置为 1 时。update 语句必须满足如下条件之一才能执行成功:

01.使用 where,并且 where 条件中必须有索引列;
02.使用 limit;
03.同时使用 where 和 limit,此时 where 条件中可以没有索引列;

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

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

相关文章

JavaScript极速入门(2)

JQuery W3C标准给我们提供了一系列函数,让我们可以操作: 网页内容 网页结构 网页样式 但是原生的JavaScript提供的API操作DOM元素时,代码比较繁琐,冗长.我们学习使用JQuery来操作页面对象. JQuery是一个快速,简洁且功能丰富的JavaScript框架,于2006年发布.它封装JavaScript常…

用开发CesiumJS模拟飞机飞行应用(一,基本功能)

本部分向您展示如何构建您的第一个 Cesium 应用程序,以可视化模拟从旧金山到哥本哈根的真实航班,并使用 FlightRadar24收集的雷达数据。您将学习如何: 在网络上设置并部署您的 Cesium 应用程序。 添加全球 3D 建筑物、地形和图像的基础图层。…

微服务架构 | 多级缓存

INDEX 通用设计概述2 优势3 最佳实践 通用设计概述 通用设计思路如下图 内容分发网络(CDN) 可以理解为一些服务器的副本,这些副本服务器可以广泛的部署在服务器提供服务的区域内,并存有服务器中的一些数据。 用户访问原始服务器…

【2024.03.05】定时执行专家 V7.1 发布 - TimingExecutor V7.1 Release

目录 ▉ 软件介绍 ▉ 新版本 V7.1 下载地址 ▉ V7.1 新功能 ▼2024-03-03 V7.1 - 更新日志 ▉ V7.0 新UI设计 ▉ 软件介绍 《定时执行专家》是一款制作精良、功能强大、毫秒精度、专业级的定时任务执行软件。软件具有 25 种【任务类型】、12 种【触发器】触发方式&#x…

【Python】成功解决TypeError: ‘float‘ object is not iterable

【Python】成功解决TypeError: ‘float’ object is not iterable 🌈 个人主页:高斯小哥 🔥 高质量专栏:Matplotlib之旅:零基础精通数据可视化、Python基础【高质量合集】、PyTorch零基础入门教程👈 希望得…

2024 GoLand激活,分享几个GoLand激活的方案

文章目录 GoLand公司简介我这边使用GoLand的理由GoLand 最新变化GoLand 2023.3 最新变化AI Assistant 正式版GoLand 中的 AI Assistant:_Rename_(重命名)GoLand 中的 AI Assistant:_Write documentation_(编写文档&…

Kubernetes(k8s第四部分之servers)

1,为什么不使用round-robin DNS? 因为DNS有缓存,不会清理,无法负载均衡 ipvs代理模式,这种模式,kube-proxy会监视Kubernetes Service 对象和Endpoints,调用netlink接口以相应地创建ipvs规则并…

HTML使用

文章目录 一、简介二、HTML快速入门三、基础标签四、图片、音频、视频标签五、超链接标签六、列表标签七、表格标签八、布局标签九、表单标签十、表单向标签 一、简介 二、HTML快速入门 ​ <html><head><title>你好</title></head><body>再…

Linux mmap系统调用

文章目录 前言一、mmap()函数简介二、代码演示2.1 mmap使用场景2.2 私有匿名映射2.3 私有文件映射2.4 共享匿名映射2.5 共享文件映射 参考 前言 NAMEmmap, munmap - map or unmap files or devices into memorySYNOPSIS#include <sys/mman.h>void *mmap(void *addr, siz…

【金三银四的季节看下Java ORM的走向和性能对比】总结

写在最后 经过将近一周时间的框架收集、学习、实验、编码、测试市面上常见的ORM框架&#xff0c;过程中拜读了很多作者的博文、样例&#xff0c;学习很多收获很多。 重新梳理下整理的框架&#xff1a;mybatis-plus、lazy、sqltoy、mybatis-flex、easy-query、mybatis-mp、jpa、…

关于 typeof 与 instanceof 区别引出的原型对象问题

一、关于 typeof 与 instanceof 区别&#xff1a; typeof 和 instanceof 是 JavaScript 中用于检查变量类型的两个不同操作符&#xff0c;它们在使用上有着明显的区别和不同的适用场景。 typeof typeof 是一个一元操作符&#xff0c;用于返回一个变量或表达式的数据类型的字符…

常见BUG如何在测试过程中分析定位

前言 在测试的日常工作中&#xff0c;相信经常有测试的小伙伴遇到类似的情况&#xff1a;在项目上线时&#xff0c;只要出现问题&#xff08;bug&#xff09;&#xff0c;就很容易成为“背锅侠”。 软件测试人员在工作中是无法避免的要和开发人员和产品经理打交道的&#xff…

每日一题——LeetCode2575.找出字符串的可整除数组

方法一 暴力&#xff08;不可行&#xff09; 直接循序字符串&#xff0c;每次多取一位&#xff0c;转为整数型然后去除m&#xff0c;但是后面word的长度会很长&#xff0c;转为整数时会丢失精度&#xff0c;得到的结果不准确&#xff0c;如果使用大数字型BigInt()也不行&#…

开发一套小程序所需的费用取决于多个因素

随着移动互联网的发展&#xff0c;小程序已经成为许多企业和个人推广业务和服务的重要工具。 不过&#xff0c;对于很多想要开发小程序的人来说&#xff0c;最大的疑问就是开发一套小程序要花多少钱。 这个问题的答案并不是固定的&#xff0c;因为开发一个小程序的成本取决于几…

Kubernetes(K8S第三部分之资源控制器)

资源控制器 什么是控制器 Kubernetes中内建了很多controller&#xff08;控制器&#xff09;&#xff0c;这些相当于一个状态机&#xff0c;用来控制Pod的具体状态和行为。 控制器类型 ReplicationController和ReplicaSet Deployment DaemonSet StateFulSet Job/CronJob…

【ArcPy】游标访问几何数据

访问质心坐标相关数据 结果展示 代码 import arcpy shppath r"C:\Users\admin\Desktop\excelfile\a2.shp" with arcpy.da.SearchCursor(shppath, ["SHAPE","SHAPEXY","SHAPETRUECENTROID","SHAPEX","SHAPEY",&q…

前端canvas项目实战——简历制作网站(五):右侧属性栏(字体、字号、行间距)

目录 前言一、效果展示二、实现步骤1. 优化代码&#xff0c;提取常量2. 实现3个编辑模块3. 实现updateFontProperty方法4. 一个常见的用法&#xff1a;仅更新当前选中文字的样式 三、Show u the code后记 前言 上一篇博文中&#xff0c;我们扩充了线条对象&#xff08;fabric.…

MongoDB Java实战

&#x1f4d5;作者简介&#xff1a; 过去日记&#xff0c;致力于Java、GoLang,Rust等多种编程语言&#xff0c;热爱技术&#xff0c;喜欢游戏的博主。 &#x1f4d7;本文收录于MongoDB系列&#xff0c;大家有兴趣的可以看一看 &#x1f4d8;相关专栏Rust初阶教程、go语言基础…

蓝色经典免费wordpress模板主题

蓝色经典配色的免费wordpress建站主题&#xff0c;万能的wordpress建站主题。 https://www.wpniu.com/themes/24.html

S4---FPGA-K7板级原理图硬件实战

视频链接 FPGA-K7板级系统硬件实战01_哔哩哔哩_bilibili FPGA-K7板级原理图硬件实战 基于XC7K325TFFG900的FPGA硬件实战框图 基于XILINX 的KINTEX-7 芯片XC7K325FPGA的硬件平台&#xff0c;FPGA 开发板挂载了4 片512MB 的高速DDR3 SDRAM 芯片&#xff0c;另外板上带有一个SODIM…