您当前的位置:首页 > 电脑百科 > 数据库 > MYSQL

快速解“锁”MySQL,拿下这7把钥匙,便能撬倒面试官

时间:2020-06-28 09:55:40  来源:  作者:

前言

MySQL 作为使用范围最广的开源关系型数据库,是每个后端开发人员都绕不开的一道坎。我在上一篇文章中也写了关于 MySQL 中的 MVCC 的细节及各个隔离级别如何使用 MVCC,有兴趣的可以查看。

这一篇文章则是跟 MySQL 中的锁有关,锁是在并发程序中最经常使用的手段之一,但是锁的滥用也会给程序的性能带来极大的负担。而我们平时使用 MySQL 做增删改查操作的时候,感觉不到我们有在使用锁,实际上是因为 MySQL 已经为我们使用了相关的锁。如果你想知道我们平时使用的 SQL 语句都使用了哪些锁?都是怎么加锁的?这些锁的作用是什么?那么可以继续往下看。

快速解“锁”MySQL,拿下这7把钥匙,便能撬倒面试官

 

普通锁

InnoDB 实现了标准行级锁,而行级锁有两种类型:

  • 共享锁(shared lock,以下将会简称为 S 锁):意在共享。也就是允许多个事务共同持有一个记录的共享锁,该锁主要用于读取操作。
  • 排他锁(exclusive lock,以下将会简称为 X 锁):意在排斥。只能允许一个事务持有一个记录的排他锁,该锁主要用于更新和删除操作。

如果你有了解过 JAVA 中的 JUC 包,那么你就会发现这有点像 JUC 中的读写锁 ReentrantReadWriteLock。它们的目的都是为了提高读取操作的并发性。

如果有一个事务 T1 持有行 r 的 S 锁,并且同时有另一个事务 T2 想要获取行 r 中的锁,T2 获取不同的锁将会有如下的情况发生:

  1. 假如 T2 想要获取行 r 的 S 锁,那么 T2 将会立刻得到该锁。
  2. 假如 T2 想要获取行 r 的 X 锁,那么 T2 则会被阻塞,直到 T1 释放了行 r 的 S 锁。

如果有一个事务 T1 持有性 r 的 X 锁,并且同时有另一个事务 T2 想要获取行 r 中的锁,不管 T2 获取什么锁都会被阻塞。

X 锁与 S 锁的兼容性如下图所示:

快速解“锁”MySQL,拿下这7把钥匙,便能撬倒面试官

 

最左边是持有的锁,最上面是想要申请的锁。从图中可以看出,只要跟 X 锁相关的,都会冲突,也就是会造成阻塞。

意向锁

InnoDB 允许多种粒度的锁共存,所以会有表锁和行锁共存的情况。为了让多种粒度的锁可以共存,InnoDB 使用了意向锁。意向锁是表级锁,它是为了表明有一个事务正在持有锁或者打算申请一个锁。

意向锁有两种类型:

  • 共享意向锁(intention shared lock,以下简称 IS):表示事务持有表中行的共享锁或者打算获取行的共享锁。
  • 共享排他锁(intention exclusive lock,以下简称 IX):表示事务持有表中行的排他锁或者打算获取行的排他锁。

IS 和 IX 只是为了表达出一种意图,它们除了全表请求之外,不会阻塞任何操作。它们的主要目的只是为了表示持有一个行锁,或者打算获取行锁。

意向锁的使用规则如下:

  • 事务在获取表中的共享行锁时,需要先获取表中的 IS 锁或者等级更高的锁。
  • 事务在获取表中的排他行锁时,需要先获取表中的 IX 锁。

这里有一个很重要的点:就是只有获取表中的行锁时,才会需要先申请意向锁。 如果是执行 ALTER TABLE 等需要锁定整个表的语句,是不需要申请意向锁的,可以直接去申请表级 X 锁。

表级别下的X锁、S锁、IS 锁和 IX 锁的兼容性如下:

快速解“锁”MySQL,拿下这7把钥匙,便能撬倒面试官

 

注意:这里的 X 锁、S 锁说的也是表级锁,不要理所当然的想成了行级锁。

为什么会有意向锁的出现呢?我们考虑如下场景(假设不存在意向锁):

一个事务 A 想要修改表 t 中的行 r,所以 A 获取行 r 的 X 锁,事r务 A 现在持有一个行锁。此时,有一个事务 B 想要使用 ALTER TABLE 语句修改表 t 的结构,该语句首先需要获取表 t 的 X 锁,但是此时事务 B 并不知道表中是否有行被锁住,所以它只能一行一行去遍历,然后把遍历的行也锁住,直到发现表中没有行在之前已经被锁住,现在它就可以修改表的结构了。但是它发现表中已经存在一些行被锁住,那么它就不能修改表结构,需要等这些锁都释放。

快速解“锁”MySQL,拿下这7把钥匙,便能撬倒面试官

 

这里有一个大问题,最坏的情况下,需要遍历所有的行才能知道是否有行被锁住,这是非常消耗性能的,而意向锁就可以解决这个问题。我们现在再来考虑相同场景下,意向锁如何解决这个问题:

一个事务 A 想要修改表 t 中的行 r,A 首先需要获取表 t 的 IX 锁,然后成功获取 IX 锁之后,再去申请行 r 的 X 锁,申请成功之后,事务 A 此时就持有两个锁,分别是表 t 的 IX 锁和行 r 的 X 锁。此时,有一个事务 B 想要使用 ALTER TABLE 语句修改表 t 的结构,该语句需要获取表 t 的 X 锁,事务 B 可以查看表 t 上是否存在锁来判断表中的行是否被上锁,当发现表 t 上存在 IX 锁,事务 B 就会被阻塞,因为它知道表中已经有行被锁定,所以无法申请到表 t 的 X 锁。

快速解“锁”MySQL,拿下这7把钥匙,便能撬倒面试官

 

我们看上面的兼容性表,也得知表级的 IX 锁和表级的 X 锁是冲突的,所以刚刚好对应上这个场景。

记录锁

记录锁是对索引记录的锁定,换句话说就是,记录锁只会锁定索引。每一个表必定会有一个主键索引(用户定义的主键、唯一索引、隐式生成),而该主键索引中的非叶子节点中的记录就是使用该记录锁进行锁定。

假设执行语句:select * from user where id = 10 for update;

如果 id 是 user 表中的主键,那么在主键索引中,id 为 10 的记录就会被锁定。并且其他事务想要更新、删除此条记录都会被阻塞,只有等该记录中的记录锁被释放之后,才可以执行其他操作。

快速解“锁”MySQL,拿下这7把钥匙,便能撬倒面试官

 

除了主键索引之外,InnoDB 中还会有二级索引。二级索引跟主键索引一样,在使用二级索引作为查询条件时,会将符合条件的二级索引的记录使用记录锁进行锁定,然后再回表将对应的主键索引也使用记录锁进行锁定。

假设执行语句:select * from user where name = 'c' for update;

如果 id 是 user 表中的主键,name 是 user 表中的二级索引。则会先将二级索引下的 name = ‘c’ 的索引锁定,然后再进行回表将主键索引为 9 的主键索引锁定。

快速解“锁”MySQL,拿下这7把钥匙,便能撬倒面试官

 

间隙锁

间隙锁(简称为 Gap)是对索引记录之间的间隙的锁定,或者是对第一条索引记录之前的间隙和对最后一条记录之后的间隙的锁。间隙锁是防止幻读的主要手段之一,幻读是同一个事务在不同的时间执行相同的查询语句,得出的结果集不同。那么间隙锁是如何防止幻读的呢?实际上就是通过锁定指定的间隙,使得这些间隙无法插入新的记录,从而防止了数据的增长。

假设我们执行此条语句:select * from user where id > 5 and id < 9 for update;

由于间隙锁的存在,其他事务如果想要插入 id 在 5 和 9 之间的记录是无法成功的,会被阻塞,直到间隙锁释放。比如想要插入 id 为 6 的记录,就会阻塞,如下图所示(省略部分无关的字段)。间隙锁跨越的间隙可能为一个值、多个值、甚至为空值。

快速解“锁”MySQL,拿下这7把钥匙,便能撬倒面试官

 

通过上图我们可以知道:

  • (5, 7]:id 为 5 的索引记录与 id 为 7 的索引记录之间的间隙被间隙锁锁定了
  • (7, 9]:id 为 7 的索引记录与 id 为 9 的索引记录之间的间隙被间隙锁锁定了

因为这两个间隙被间隙锁锁定了,所以在这两个间隙之间的记录是无法插入,只有等间隙锁释放之后才可以插入。我们还要注意到,id 为 7 的记录是被记录锁锁定的,所以在 id 为 7 的记录上执行更新、删除操作时会被阻塞的。

我们上面还说到,间隙锁还在第一条记录的前面和最后一条记录的后面加锁,我们来看看这是什么情况。

假设我们执行此条语句:select * from user for update;

因为该语句没有使用索引,所以会进行全表扫描。将扫描到的每一条记录都加上记录锁,并且将所有的间隙也加间隙锁。最终的加锁情况如下图所示(省略部分无关的字段):

快速解“锁”MySQL,拿下这7把钥匙,便能撬倒面试官

 

每个表中都会存在两个隐式记录:最小记录(infimum),最大记录(supermum)

我们通过上图,可以得出锁定的区间如下:

  • (-∞, 5]
  • (5, 7]
  • (7, 9]
  • (9, 10]
  • (10, 12]
  • (12, +∞)

并且所有的记录都被记录锁锁定。这个看起来就像是一个表锁,因为对该表的任何操作(快照读除外),都会被阻塞。

但是,间隙锁并不是在任何情况下都会使用,它在以下情况并不会使用:

  • 隔离级别为 RC、RU。
  • 使用唯一索引进行等值比较获取一条索引记录。这是因为唯一索引进行等值比较只能获取一条记录,不会出现多条记录的情况,那么也就不会出现多次读取出现不一致的情况。

间隙锁的主要目的是阻止事务往间隙中插入记录,并且间隙锁之间是可以共存的,多个事务可以同时获取得到相同间隙的锁。共享间隙锁和排他间隙锁之间并没有区别,它们是完全一样的东西。

Next-Key 锁

Next-Key 锁并不是一个难以理解的东西,它本质上就是索引记录上的记录锁和索引记录之间的间隙锁的结合。

InnoDB 在查找和扫描表的时候,会将扫描到的记录都加上记录锁,记录锁有可能是共享锁或者是排他锁。因此,行级锁实际上是索引记录锁。

在间隙锁的两个例子中的第二个例子,它实际上就是 Next-Key 锁,因为每一个括号括起来的内存包括一个索引记录锁和一个间隙锁,而 这完美符合 Next-Key 的定义。

在默认的 REPEATABLE READ 隔离级别下,InnoDB 在查找和扫描索引时,都会使用 Next-Key 锁,以此来防止幻读的发生。

插入意向锁

插入意向锁(简称为 II Gap)是一种特殊的间隙锁,只有在插入记录的时候才会使用,这个锁表示插入的意向。它与上面说到的表级意向锁是完全不同的,插入意向锁是属于行级锁,并且互相之间是兼容的,互不冲突,所以多个事务可以同时获取到相同间隙的 II Gap 锁。

官方示例:

假设有索引记录,其值分别为4和7,单独的事务分别尝试插入值5和6,在获得插入行的排他锁之前,每个事务都使用插入意图锁来锁定4和7之间的间隙,但不要互相阻塞,因为行是无冲突的。

插入意向锁只会和间隙锁和 Next-Key 锁冲突。因为间隙锁的主要作用是防止幻读的发生,而在插入操作执行前需要获取到插入意向锁,而插入意向锁和间隙锁之间是冲突的,可以阻塞插入操作,所以间隙锁可以防止幻读的发生。

AUTO-INC 锁

AUTO-INC 锁又称为自增锁(简称 AI 锁)。它是特殊的表锁,在插入数据到具有 AUTO_INCREMENT 列的表时使用。当插入数据的表中有自增列时,数据库需要自动生成自增值,在生成之前,它会先获取到相关表的 AUTO-INC 锁。其他事务的插入操作将会被阻塞,这样可以保证自增值的唯一性。

AUTO-INC 锁具有如下特点:

  • 每一张表都具有它自己的 AUTO-INC 锁,互相之间不兼容。
  • 不遵循二段锁协议,它并不是在事务提交时释放,而是在 insert 语句执行完成之后就释放,提高了并发插入的性能。
  • 自增值一旦分配了就会加一,即使回滚了,自增值也不会减一,而是继续使用下一个值,所以自增值有可能不是连续的。

因为在插入时会使用到该表锁,所以必然会造成并发插入性能的下降。因此 InooDB 提供了一个 innodb_autoinc_lock_mode 配置项用于控制自增锁的算法,该配置项可以使用户选择如何在可预测的自动增量值序列与插入操作的最大并发性之间进行权衡。

该配置有三个可选项:

  • 0:使用传统的锁定模式,并发性能最差。
  • 1:默认采用的模式。
  • 2:并发性能最高,但是不能保证同一条 insert 语句内的自增值是连续的。

想要了解更多关于此配置的内容可以查看 MySQL 的这篇文档。

总结

InnoDB 的四种行锁的兼容性,如下表所示:

快速解“锁”MySQL,拿下这7把钥匙,便能撬倒面试官

 

note: 第一列表示已经持有的锁,第一行表示要获取的锁。

从表中可以得出结论:

  • 插入意向锁不影响其他事务获取其他的锁。
  • 插入意向锁会受到 Gap 锁和 Next-Key 锁的影响。一个事务想要获取指定间隙的插入意向锁,那么该间隙中的 Gap 锁和 Next-Key 锁必须没有被其他事务持有,否则,将会被阻塞。

如果,我们除去插入意向锁的影响,那么兼容性表格如下:

快速解“锁”MySQL,拿下这7把钥匙,便能撬倒面试官

 

从表中我们可以得出以下结论:

  • 当两个事务的锁都涉及到记录锁,那么将会冲突。
  • 间隙锁与其他锁(不包括插入意向锁)都不会产生冲突。

作者:奋斗的小皇帝

原文:
https://juejin.im/post/5ef6d8355188252e5961a253



Tags:MySQL   点击:()  评论:()
声明:本站部分内容及图片来自互联网,转载是出于传递更多信息之目的,内容观点仅代表作者本人,如有任何标注错误或版权侵犯请与我们联系(Email:2595517585@qq.com),我们将及时更正、删除,谢谢。
▌相关推荐
作者:雷文霆 爱可生华东交付服务部 DBA 成员,主要负责Mysql故障处理及相关技术支持。爱好看书,电影。座右铭,每一个不曾起舞的日子,都是对生命的辜负。 本文来源:原创投稿 *爱可生...【详细内容】
2021-12-24  Tags: MySQL  点击:(7)  评论:(0)  加入收藏
一、为什么要搭建主从架构呢1.数据安全,可以进行数据的备份。2.读写分离,大部分的业务系统来说都是读数据多,写数据少,当访问压力过大时,可以把读请求给到从服务器。从而缓解数据...【详细内容】
2021-12-15  Tags: MySQL  点击:(12)  评论:(0)  加入收藏
生成间隙(gap)锁、临键(next-key)锁的前提条件 是在 RR 隔离级别下。有关Mysql记录锁、间隙(gap)锁、临键锁(next-key)锁的一些理论知识之前有写过,详细内容可以看这篇文章...【详细内容】
2021-12-14  Tags: MySQL  点击:(18)  评论:(0)  加入收藏
binlog 基本认识 MySQL的二进制日志可以说是MySQL最重要的日志了,它记录了所有的DDL和DML(除了数据查询语句)语句,以事件形式记录,还包含语句所执行的消耗的时间,MySQL的二...【详细内容】
2021-12-14  Tags: MySQL  点击:(13)  评论:(0)  加入收藏
为查询优化你的查询 大多数的MySQL服务器都开启了查询缓存。这是提高性最有效的方法之一,而且这是被MySQL的数据库引擎处理的。当有很多相同的查询被执行了多次的时候,这些查...【详细内容】
2021-12-09  Tags: MySQL  点击:(15)  评论:(0)  加入收藏
测试的目的和原因,公司有很多程序员,每个程序员对数据库和表结构都有自己的理解。而且每个程序员的理解往往是以效率考虑。既然都是为了效率考虑,那么我就来测试一下究竟哪种使...【详细内容】
2021-12-08  Tags: MySQL  点击:(14)  评论:(0)  加入收藏
当你们考虑项目并发的时候,我在部署环境,当你们在纠结使用ArrayList还是LinkedArrayList的时候,我还是在部署环境。所以啊,技术不止境,我在部环境。今天这篇文章缕一下在同一台服...【详细内容】
2021-12-08  Tags: MySQL  点击:(17)  评论:(0)  加入收藏
对于数据分析来说,MySQL使用最多的是查询,比如对数据进行排序、分组、去重、汇总及字符串匹配等,如果查询的数据涉及多个表,还需要要对表进行连接,本文就来说说MySQL中常用的查询...【详细内容】
2021-12-06  Tags: MySQL  点击:(21)  评论:(0)  加入收藏
在学习SQL语句之前,首先需要区分几个概念,我们常说的数据库是指数据库软件,例如MySQL、Oracle、SQL Server等,而本文提到的数据库是指数据库软件中的一个个用于存储数据的容器。...【详细内容】
2021-11-24  Tags: MySQL  点击:(23)  评论:(0)  加入收藏
概述以前参加过一个库存系统,由于其业务复杂性,搞了很多个应用来支撑。这样的话一份库存数据就有可能同时有多个应用来修改库存数据。比如说,有定时任务域xx.cron,和SystemA域...【详细内容】
2021-11-05  Tags: MySQL  点击:(31)  评论:(0)  加入收藏
▌简易百科推荐
作者:雷文霆 爱可生华东交付服务部 DBA 成员,主要负责Mysql故障处理及相关技术支持。爱好看书,电影。座右铭,每一个不曾起舞的日子,都是对生命的辜负。 本文来源:原创投稿 *爱可生...【详细内容】
2021-12-24  爱可生    Tags:MySQL   点击:(7)  评论:(0)  加入收藏
生成间隙(gap)锁、临键(next-key)锁的前提条件 是在 RR 隔离级别下。有关Mysql记录锁、间隙(gap)锁、临键锁(next-key)锁的一些理论知识之前有写过,详细内容可以看这篇文章...【详细内容】
2021-12-14  python数据分析    Tags:MySQL记录锁   点击:(18)  评论:(0)  加入收藏
binlog 基本认识 MySQL的二进制日志可以说是MySQL最重要的日志了,它记录了所有的DDL和DML(除了数据查询语句)语句,以事件形式记录,还包含语句所执行的消耗的时间,MySQL的二...【详细内容】
2021-12-14  linux上的码农    Tags:mysql   点击:(13)  评论:(0)  加入收藏
为查询优化你的查询 大多数的MySQL服务器都开启了查询缓存。这是提高性最有效的方法之一,而且这是被MySQL的数据库引擎处理的。当有很多相同的查询被执行了多次的时候,这些查...【详细内容】
2021-12-09  元宇宙iwemeta    Tags:mysql   点击:(15)  评论:(0)  加入收藏
测试的目的和原因,公司有很多程序员,每个程序员对数据库和表结构都有自己的理解。而且每个程序员的理解往往是以效率考虑。既然都是为了效率考虑,那么我就来测试一下究竟哪种使...【详细内容】
2021-12-08  吴彬的分享    Tags:Mysql数据库   点击:(14)  评论:(0)  加入收藏
当你们考虑项目并发的时候,我在部署环境,当你们在纠结使用ArrayList还是LinkedArrayList的时候,我还是在部署环境。所以啊,技术不止境,我在部环境。今天这篇文章缕一下在同一台服...【详细内容】
2021-12-08  秃头码哥    Tags:MySQL数据库   点击:(17)  评论:(0)  加入收藏
对于数据分析来说,MySQL使用最多的是查询,比如对数据进行排序、分组、去重、汇总及字符串匹配等,如果查询的数据涉及多个表,还需要要对表进行连接,本文就来说说MySQL中常用的查询...【详细内容】
2021-12-06  笨鸟学数据分析    Tags:MySQL   点击:(21)  评论:(0)  加入收藏
在学习SQL语句之前,首先需要区分几个概念,我们常说的数据库是指数据库软件,例如MySQL、Oracle、SQL Server等,而本文提到的数据库是指数据库软件中的一个个用于存储数据的容器。...【详细内容】
2021-11-24  笨鸟学数据分析    Tags:SQL语句   点击:(23)  评论:(0)  加入收藏
概述以前参加过一个库存系统,由于其业务复杂性,搞了很多个应用来支撑。这样的话一份库存数据就有可能同时有多个应用来修改库存数据。比如说,有定时任务域xx.cron,和SystemA域...【详细内容】
2021-11-05  Java云海    Tags:分布式锁   点击:(31)  评论:(0)  加入收藏
MySQL的进阶查询 一、 按关键字排序 使用ORDERBY语句来实现排序排序可针对一个或多个字段ASC:升序,默认排序方式 【升序是从小到大】DESC:降序 【降序是从大到小】ORDER BY的...【详细内容】
2021-11-05  Java热点    Tags:SQL语句   点击:(28)  评论:(0)  加入收藏
最新更新
栏目热门
栏目头条