社区编辑申请
注册/登录
记录锁、间隙锁与 Next-Key Lock
数据库 MySQL
现在 Gap Lock 间隙锁,就是要把这些记录之间的间隙也给锁住,间隙锁住了,就不用担心幻读问题了,这也是 Gap Lock 存在的意义。

有小伙伴在微信上表示面试时被问到了 Next-Key Lock 是啥,结果一脸懵逼,那么今天我们来捋一捋 MySQL 中的记录锁、间隙锁以及 Next-Key Lock。

1. Record LockRecord

Lock 也就是我们所说的记录锁,记录锁是对索引记录的锁,注意,它是针对索引记录,即它只锁定记录这一行数据。

例如如下一条 SQL:

select * from user where id=1 for update;

注意,id 是索引,id 如果不是索引,上面这条 SQL 所加的排他锁就不是一个 Record Lock。

我们来看如下一个例子:

首先我们将系统变量 innodb_status_output_locks 设置为 ON,如下:

接下来我们执行如下 SQL,锁定一行数据,此时会自动为表加上 IX 锁:

接下来我们在一个新的会话中执行如下指令来查看 InnoDB 存储引擎的情况:

show engine innodb status\G

输出的信息很多,我们重点关注 TRANSACTIONS,如下:

可以看到:

  • TABLE LOCK table test08.user trx id 3564804 lock mode IX:这句就是说事务 id 为 3564804 的事务,为 user 表添加了意向排他锁(IX)。
  • RECORD LOCKS space id 851 page no 3 n bits 80 index PRIMARY of table test08.user trx id 3564804 lock_mode X locks rec but not gap:这个就是一个锁结构的记录,这里的索引是 PRIMARY,加的锁也是正儿八经的记录锁(not gap)。

看到了 LOCKS REC BUT NOT GAP,就说明这是一个记录锁。

那么这个 Record Lock 和我们之前所讲的 S 锁以及 X 锁有什么区别呢?S 锁是共享锁,X 锁是排他锁,当我们加 S 锁或者 X 锁的时候,如果用到了索引,锁加在了某一条具体的记录上,那么这个锁也是一个记录锁(其实,记录锁,S 锁,X 锁,概念有一些重复的地方,但是描述的重点不一样)。

或者也可以理解为记录锁又细分为 S 锁和 X 锁,它们之间的兼容性如下图:

兼容性

S 型记录锁

X 型记录锁

S 型记录锁

兼容

不兼容

X 型记录锁

不兼容

不兼容

2. Gap Lock

Gap Lock 也叫做间隙锁,它的存在可以解决幻读问题,另外需要注意,Gap Lock 也只在 REPEATABLE READ 隔离级别下有效。先来看看什么是幻读,我们来看如下一个表格:

有两个会话,A 和 B,先在会话 A 中开启事务,然后查询 age 为 99 的用户总数,注意使用当前读,因为在默认的隔离级别下,默认的快照读并不能读到其他事务提交的数据,至于快照读和当前读的区别,大家参考:S 锁与 X 锁,当前读与快照读!。当会话 A 中第一次查询过后,会话 B 中向数据库添加了一行记录,等到会话 A 中第二次查询的时候,就查到了和第一次查询不一样的结果,这就是幻读(注意幻读专指数据插入引起的不一致)。

在 MySQL 默认的隔离级别 REPEATABLE READ 下,上图所描述的情况无法复现。无法复现的原因在于,在 MySQL 的 REPEATABLE READ 隔离级别中,它已经帮我们解决了幻读问题,解决的方案就是 Gap Lock。

大家想想,之所以出现幻读的问题,是因为记录之间存在缝隙,用户可以往这些缝隙中插入数据,这就导致了幻读问题,如下图:

如图所示,id 之间有缝隙,有缝隙就有漏洞。前面我们所说的记录锁只能锁住一条具体的记录,但是对于记录之间的空隙却无能无力,这就导致了幻读(其他事务可往缝隙中插入数据)。

现在 Gap Lock 间隙锁,就是要把这些记录之间的间隙也给锁住,间隙锁住了,就不用担心幻读问题了,这也是 Gap Lock 存在的意义。

给一条记录加 Gap Lock,是锁住了这条记录前面的空隙,例如给 id 为 1 的记录加 Gap Lock,锁住的范围是 (-∞,1),给 id 为 3 的记录加 Gap Lock,锁住的范围是 (1,3),那么 id 为 10 后面的空隙怎么锁定呢?MySQL 提供了一个 Supremum 表示当前页面中的最大记录,所以最后针对 Supremum 锁住的范围就是 (10,+∞),这样,所有的间隙都被覆盖到了,由于锁定的是间隙,所以都是开区间。

那么我们怎么样能看到 Gap Lock 呢?我给大家举一个简单的例子,假设我有如下一张表:

CREATE TABLE `user` (
`id` int(11) unsigned NOT NULL AUTO_INCREMENT,
`username` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
`age` int(11) DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `age` (`age`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

一个简单的表,id 是主键,age 是普通索引,表中有如下几条记录:

接下来我们执行如下 SQL,锁定一行数据,此时也会产生间隙锁:

接下来我们在一个新的会话中执行如下指令来查看 InnoDB 存储引擎的情况:

show engine innodb status\G

输出的信息很多,我们重点关注 TRANSACTIONS,如下:

红色框选中的,就是一个间隙锁的加锁记录,可以看到,在某一个记录之前加了间隙锁。

这就是间隙锁。非常重要的一点需要大家牢记:Gap Lock 只在 REPEATABLE READ 隔离级别下有效。

3. Next-Key Lock

以下内容都是基于 MySQL 默认的隔离级别 REPEATABLE READ。

如果我们既想锁定一行,又想锁定行之间的记录,那么就是 Next-Key Lock 了,换言之,Next-Key Lock 是 Record Lock 和 Gap Lock 的结合体。

正常来说,我们加行锁的基本单位就是 Next-Key Lock,即既有记录锁又有间隙锁,但是有时候 Next-Key Lock 会退化,我们通过几个简单的例子来分析一下。

首先我们来看看 Next-Key Lock 的加锁规则:

  • 锁的范围是左开右闭。
  • 如果是唯一非空索引的等值查询,Next-Key Lock 会退化成 Record Lock。
  • 普通索引上的等值查询,向后遍历时,最后一个不满足等值条件的时候,Next-Key Lock 会退化成 Gap Lock。

我们通过几个简单的例子来分析下。

3.1 唯一非空索引

假设我有一个学生表,学生表中有学生的姓名和成绩,如下:

CREATE TABLE `student` (
`id` int(11) unsigned NOT NULL AUTO_INCREMENT,
`name` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
`score` double NOT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `score` (`score`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

id 是主键,score 是成绩,其中 score 是唯一非空索引。

现在表中有如下数据:

假设我们执行如下 SQL:

在这个例子中,由于 score 是唯一非空索引,所以 Next-Key Lock 会退化成 Record Lock,换句话说,这行 SQL 只给 score 为 90 的记录加锁,不存在 Gap Lock,即我们新开一个会话,插入一条 score 为 88 的记录也是 OK 的。

不过这里有一个特例,如果锁定的是一个不存在的记录,那么也会产生间隙锁,例如下面这个:

由于并不存在 score 为 91 的记录,所以这里会产生一个范围为 (90,95) 的间隙锁,我们执行如下 SQL 可以验证:

可以看到,90.1、94.9 都会被阻塞(我按了 Ctrl C,所以大家看到查询终止)。

90、95 则不符合唯一非空索引的条件。

95.1 则可以插入成功。

没问题。

3.2 非空索引

现在我们重新开始,将 score 索引改为普通索引,如下:

CREATE TABLE `student` (
`id` int(11) unsigned NOT NULL AUTO_INCREMENT,
`name` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
`score` double NOT NULL,
PRIMARY KEY (`id`),
KEY `score` (`score`)
) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

数据还是跟前面一样,此时我们来执行如下 SQL:

我们来分析下。

此时要锁定的是 id 为 90 的记录,那么首先加间隙锁,上一个 score 为 89,所以这次加的间隙锁范围是 (89,90),同时要锁定 id 为 90 的记录,所以进一步优化为 (89,90]。

同时,这里还有一条规则,就是满足条件的上一条记录,也需要被锁住,所以最终的锁范围就是 [89,90]。

由于 score 不是唯一性索引,所以还需要继续向后查找,找到的下一条记录是 95,由于此时 Next-Key Lock 会退化成 Gap Lock,所以锁定的范围是 (90,95)。综上,最终锁定的范围是 [89,95)。

接下来我们可以新开一个会话,我们分别尝试添加如下数据看看是否能够添加成功:

可以看到,score 为 88 是可以的,但是为 89.1 就不行。

score 为 95 也是可以的,但是为 94.9 就不行。

再试一下 89 是否可以:

说明我们上面分析的加锁范围是正确的。

再来看如下一条 SQL:

跟前面的案例相比,这次多了 limit 1,limit 1 表示只要一条记录,所以这次查找到 90 之后就不会再往后查找了,那么最终的锁就是间隙锁+一个记录锁,最终的范围就是 [89,90]。

此时新开一个会话,分别插入 score 为 88.9、89、90、91 的 记录,验证我们上面所分析的加锁范围:

88.9 和 89 的插入结果跟我们预想的一致。

可以看到,这里 90 也能插入,能插入的原因是因为缺乏 90 往后的间隙锁。

4. 小结

MySQL 中的锁有点繁杂,小伙伴们可以趁着某个周末,花点时间捋一捋,以后面试再遇到这些问题的时候就不头大了。

责任编辑:武晓燕 来源: 江南一点雨
相关推荐

2022-05-07 15:31:19

物联网5G智慧城市

2022-04-13 08:00:00

Hilla开发Java

2022-04-25 11:26:16

开发SpringBoot

2022-03-25 10:38:40

索引MySQL数据库

2022-03-22 07:57:42

Java多线程并发

2022-04-20 10:33:59

人工智能数字经济互联网 文章链接:智

2022-04-13 14:49:59

安全供应链Go

2022-03-10 17:02:51

Rust单链表数据结构

2022-04-08 16:57:08

戴尔

2022-04-12 10:36:52

数据库PostgreSQLMongoDB

2022-04-05 13:10:15

consul分布式高可用

2022-03-16 11:32:54

苹果iOS 15.4Face ID

2022-03-18 15:14:03

拓扑量子计算拓扑间隙协议微软

2022-04-11 15:44:07

Linux人脸识别

2022-03-18 14:44:14

数据安全端点安全防护

2021-12-06 07:23:12

2022-04-26 08:02:00

locktryLocklockInterr

2022-05-17 08:02:55

2021-06-05 18:02:20

2019-04-15 13:15:12

数据库MySQL死锁

同话题下的热门内容

分库分表会带来读扩散问题?怎么解决?浅析MySQL日志体系面试官问我 InnoDB 的物理存储结构!InnoDB B-TREE 索引怎么定位一条记录?聊聊索引失效的经典场景关于MySQL数据库性能优化方法,看这一篇文章就够了

编辑推荐

MySQL集群搭建详解如果对MySQL还停留在这个印象,就out了防止服务器宕机时MySQL数据丢失的几种方案MySQL innodb引擎备份工具XtraBackup之二(数据库全备)MySQL优化: Slave延迟很大的优化方法总结
我收藏的内容
点赞
收藏

51CTO技术栈公众号