最新要闻

广告

手机

iphone11大小尺寸是多少?苹果iPhone11和iPhone13的区别是什么?

iphone11大小尺寸是多少?苹果iPhone11和iPhone13的区别是什么?

警方通报辅警执法直播中被撞飞:犯罪嫌疑人已投案

警方通报辅警执法直播中被撞飞:犯罪嫌疑人已投案

家电

美团索命一问:一个SQL ,怎么分析加了哪些锁? 含4大场景、8个规则

来源:博客园

背景说明:

美团问数据库应该是非常多的,尤其喜欢考手写 SQL 然后问你这个 SQL 语句上面加了哪些锁,


(相关资料图)

你会发现其他厂面试基本很少会这样考,所以很多小伙伴遇到这种问题的时候都是一脸懵逼,

可以说是“夺命一问”

此文,40岁老架构师尼恩,用自己的深厚内功,为大家梳理了 加锁的四大场景、八大规则,

把这个 “夺命一问”, 彻底攻克。

现在把这个 题目以及参考答案,收入咱们的 《尼恩Java面试宝典》,

供后面的小伙伴参考,提升大家的 3高 架构、设计、开发水平。

注:本文以 PDF 持续更新,最新尼恩 架构笔记、面试题 的PDF文件,请从这里获取:码云

加锁的场景分析

  • 场景1:唯一索引等值查询
    • 场景1.1、查询的记录存在
    • 场景1.2、查询的记录不存在
  • 场景 2:唯一索引范围查询
  • 场景 3:非唯一索引等值查询
    • 场景3.1、查询的记录存在
    • 场景3.2、查询的记录不存在
  • 场景 4:非唯一索引范围查询

接下来,按照以上4大场景,进行 sql 加锁的分析。

回顾一下,InnoDB 三种行锁:

  • Record Lock(记录锁):锁住某一行记录
  • Gap Lock(间隙锁):锁住一段左开右开的区间
  • Next-key Lock(临键锁):锁住一段左开右闭的区间

哪些语句上面会加行锁?

1)对于常见的 DML 语句(如 UPDATEDELETEINSERT),InnoDB 会自动给相应的记录行加写锁

2)默认情况下对于普通 SELECT语句,InnoDB 不会加任何锁,但是在 Serializable 隔离级别下会加行级读锁

上面两种是隐式锁定,InnoDB 也支持通过特定的语句进行显式锁定:

3)SELECT * FROM table_name WHERE ... FOR UPDATE,加行级写锁

4)SELECT * FROM table_name WHERE ... LOCK IN SHARE MODE,加行级读锁

两条最为基础的规则

在学习具体行锁加锁规则之前,小伙伴们需要记住两条最为基础的规则:

规则1:查找过程中访问到的对象才会加锁

这句话该怎么理解?

比如有主键 id 为 1 2 3 4 5 ... 10的10 条记录,我们要找到 id = 7的记录。

注意,查找并不是从第一行开始一行一行地进行遍历,而是根据 B+ 树的特性进行二分查找,所以一般存储引擎只会访问到要找的记录行(id = 7)的相邻区间

规则2:加锁的基本单位是 Next-key Lock (临键锁):锁住一段左开右闭的区间

实例分析

下面结合实例, 分析一条 SQL 语句上面究竟被 InnoDB 自动加上了多少个锁

假设有这么一张 user表:

id为主键(唯一索引),a是普通索引(非唯一索引),b都是普通的列,b上没有任何索引:

id (唯一索引)a (非唯一索引)b
104Alice
158Bob
2016Cilly
2532Druid
3064Erik

场景1:唯一索引等值查询

当我们用唯一索引进行等值查询的时候,根据查询的记录是否存在,加锁的规则会有所不同:

规则3:当查询的记录是存在的,Next-key Lock 临键锁 会退化成 Record Lock 记录锁

规则4:当查询的记录是不存在的,Next-key Lock 临键锁 会退化 Gap Lock 成间隙锁

场景1.1、查询的记录存在

先来看个查询的记录存在的案例:

select * from user  where id = 25  for update;

结合加锁的规则1、规则2

规则1:查找过程中访问到的对象才会加锁

规则2:加锁的基本单位是 Next-key Lock 临键锁(左开右闭)

两条要点, 我们可以分析出,这条语句的加锁范围是 临键锁(20, 25]

不过,按照规则3,由于这个唯一索引等值查询的记录 id = 25是存在的,因此,Next-key Lock 临键锁 会退化成 Record Lock 记录锁,因此最终的加锁范围是 id = 25这一行

场景1.2、查询的记录不存在

再来看查询的记录不存在的案例:

select * from user  where id = 22 for update;

结合加锁的两条要点(重复一下,表示强调)

规则1:查找过程中访问到的对象才会加锁

规则2:加锁的基本单位是 Next-key Lock 临键锁(左开右闭)

我们可以分析出,这条语句的加锁范围是 (20, 25]

这里为什么是 (20,25]而不是 (20, 22]

因为 id = 22 的记录不存在呀,InnoDB 先找到 id = 20 的记录,发现不匹配,于是继续往下找,发现 id = 25,因此,id = 25 的这一行被扫描到了,所以整体的加锁范围是 (20, 25]

不过,按照规则4,由于这个唯一索引等值查询的记录 id = 22是不存在的,因此,Next-key Lock 会退化成间隙锁,因此最终在主键 id 上的加锁范围是 Gap Lock (20, 25)

场景 2:唯一索引范围查询

唯一索引范围查询的规则和等值查询的规则一样,

规则3:当查询的记录是存在的,Next-key Lock 会退化成记录锁

规则4:当查询的记录是不存在的,Next-key Lock 会退化成间隙锁

只有一个区别,就是唯一索引的范围查询,需要一直向右遍历到第一个不满足条件的记录,相当于增加了一条规则:

规则5:唯一索引的范围查询,一直向右遍历到第一个不满足条件的记录

下面结合案例来分析:

select * from user  where id >= 20 and id < 22  for update;
id (唯一索引)a (非唯一索引)b
104Alice
158Bob
2016Cilly
2532Druid
3064Erik

先来看语句查询条件的前半部分 id >= 20

结合加锁的规则1、规则2

规则1:查找过程中访问到的对象才会加锁

规则2:加锁的基本单位是 Next-key Lock 临键锁(左开右闭)

这条语句最开始要找的第一行是 id = 20,需要加上 Next-key Lock (15,20]

根据规则3、规则4

又由于 id 是唯一索引,且 id = 20 的这行记录是存在的,因此会退化成记录锁,也就是只会对 id = 20这一行加锁。

根据规则5

再来看语句查询条件的后半部分 id < 22,由于是范围查找,就会继续往后找第一个不满足条件的记录,也就是会找到 id = 25这一行停下来,然后加 Next-key Lock 临界锁(20, 25]

根据规则4

重点来了,但由于 id = 25不满足 id < 22,因此会退化成间隙锁,加锁范围变为 (20, 25)

所以,上述语句在主键 id 上的最终的加锁范围是 Record Lock id = 20以及 Gap Lock (20, 25)

场景 3:非唯一索引等值查询

当我们用非唯一索引进行等值查询的时候,根据查询的记录是否存在,加锁的规则会有所不同:

规则6:当查询的记录是存在的,除了会加 Next-key Lock 外,还会额外加间隙锁(规则是向下遍历到第一个不符合条件的值才能停止),也就是会加两把锁

很好记忆,就是要查找记录的左区间加 Next-key Lock 锁住一段左开右闭的区间,右区间加 Gap lock 锁住一段左开右开的区间

规则7:当查询的记录是不存在的,Next-key Lock 会退化成 Gap lock 间隙锁(这个规则和唯一索引的等值查询是一样的)

场景3.1、查询的记录存在

先来看个查询的记录存在的案例:

select * from user  where a = 16 for update;

结合加锁的规则1、规则2

规则1:查找过程中访问到的对象才会加锁

规则2:加锁的基本单位是 Next-key Lock 临键锁(左开右闭)

这条语句首先会对普通索引 a 加上 Next-key Lock,范围是 (8,16]

根据规则6,又因为是非唯一索引等值查询,且查询的记录 a= 16是存在的,所以还会加上间隙锁,规则是向下遍历到第一个不符合条件的值才能停止,因此间隙锁的范围是 (16,32)

所以,上述语句在普通索引 a 上的最终加锁范围是 Next-key Lock (8,16]以及 Gap Lock (16,32)

场景3.2、查询的记录不存在

再来看查询的记录不存在的案例:

select * from user  where a = 18  for update;

结合加锁的两条核心,这条语句首先会对普通索引 a 加上 Next-key Lock,范围是 (16,32]

但是由于查询的记录 a = 18是不存在的,因此 Next-key Lock 会退化为间隙锁,即最终在普通索引 a 上的加锁范围是 (16,32)

场景 4:非唯一索引范围查询

回顾一下,唯一索引范围查询,其查询的规则和唯一索引等值查询的规则一样,

规则3:当查询的记录是存在的,Next-key Lock 会退化成记录锁

规则4:当查询的记录是不存在的,Next-key Lock 会退化成间隙锁

规则5:唯一索引的范围查询,一直向右遍历到第一个不满足条件的记录

非唯一索引的范围查询和唯一索引范围查询不同的是,

规则8:非唯一索引的范围查询并不会退化成 Record Lock 或者 Gap Lock。

select * from user  where a >= 16 and a < 18  for update;

先来看语句查询条件的前半部分 a >= 16,因此,这条语句最开始要找的第一行是 a = 16,结合加锁的两个核心,需要加上 Next-key Lock (8,16]

虽然非唯一索引 a = 16的这行记录是存在的,但此时并不会像唯一索引那样退化成记录锁。

再来看语句查询条件的后半部分 a < 18,由于是范围查找,就会继续往后找第一个不满足条件的记录,也就是会找到 id = 32这一行停下来,然后加 Next-key Lock (16, 32]

虽然 id = 32不满足 id < 18,但此时并不会向唯一索引那样退化成间隙锁。

所以,上述语句在普通索引 a 上的最终的加锁范围是 Next-key Lock (8, 16](16, 32],也就是 (8, 32]

推荐阅读:

《网易二面:CPU狂飙900%,该怎么处理?》

《阿里二面:千万级、亿级数据,如何性能优化? 教科书级 答案来了》

《峰值21WQps、亿级DAU,小游戏《羊了个羊》是怎么架构的?》

《场景题:假设10W人突访,你的系统如何做到不 雪崩?》

《2个大厂 100亿级 超大流量 红包 架构方案》

《Nginx面试题(史上最全 + 持续更新)》

《K8S面试题(史上最全 + 持续更新)》

《操作系统面试题(史上最全、持续更新)》

《Docker面试题(史上最全 + 持续更新)》

《Springcloud gateway 底层原理、核心实战 (史上最全)》

《Flux、Mono、Reactor 实战(史上最全)》

《sentinel (史上最全)》

《Nacos (史上最全)》

《分库分表 Sharding-JDBC 底层原理、核心实战(史上最全)》

《clickhouse 超底层原理 + 高可用实操 (史上最全)》

《nacos高可用(图解+秒懂+史上最全)》

《队列之王: Disruptor 原理、架构、源码 一文穿透》

《环形队列、 条带环形队列 Striped-RingBuffer (史上最全)》

《一文搞定:SpringBoot、SLF4j、Log4j、Logback、Netty之间混乱关系(史上最全)》

《红黑树( 图解 + 秒懂 + 史上最全)》

《分布式事务 (秒懂)》

《缓存之王:Caffeine 源码、架构、原理(史上最全,10W字 超级长文)》

《缓存之王:Caffeine 的使用(史上最全)》

《Docker原理(图解+秒懂+史上最全)》

《Redis分布式锁(图解 - 秒懂 - 史上最全)》

《Zookeeper 分布式锁 - 图解 - 秒懂》

《Zookeeper Curator 事件监听 - 10分钟看懂》

《Netty 粘包 拆包 | 史上最全解读》

《Netty 100万级高并发服务器配置》

关键词: 持续更新 基本单位 查询条件