索命一问：一个SQL ，怎么分析加了哪些锁？

绝地灬酷狼 2024-03-27 13:28 37阅读 0赞

### 背景说明： ###

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

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

可以说是“夺命一问”

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

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

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

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

> 注：本文以 PDF 持续更新，最新尼恩 架构笔记、面试题 的PDF文件，请从这里获取：[码云][Link 1]

--------------------

### 加锁的场景分析 ###

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

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

回顾一下，InnoDB 三种行锁：

*  Record Lock（记录锁）：锁住某一行记录
 *  Gap Lock（间隙锁）：锁住一段**左开右开**的区间
 *  Next-key Lock（临键锁）：锁住一段**左开右闭**的区间

哪些语句上面会加行锁？

1）对于常见的 DML 语句（如 `UPDATE`、`DELETE` 和 `INSERT` ），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上没有任何索引：

<table> 
 <thead> 
  <tr> 
   <th align="center">id (唯一索引)</th> 
   <th align="center">a (非唯一索引)</th> 
   <th align="center">b</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td align="center">10</td> 
   <td align="center">4</td> 
   <td align="center">Alice</td> 
  </tr> 
  <tr> 
   <td align="center">15</td> 
   <td align="center">8</td> 
   <td align="center">Bob</td> 
  </tr> 
  <tr> 
   <td align="center">20</td> 
   <td align="center">16</td> 
   <td align="center">Cilly</td> 
  </tr> 
  <tr> 
   <td align="center">25</td> 
   <td align="center">32</td> 
   <td align="center">Druid</td> 
  </tr> 
  <tr> 
   <td align="center">30</td> 
   <td align="center">64</td> 
   <td align="center">Erik</td> 
  </tr> 
 </tbody> 
</table>

### 场景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 >= 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%，该怎么处理？][CPU_900]》

《[阿里二面：千万级、亿级数据，如何性能优化？ 教科书级 答案来了][Link 2]》

《[峰值21WQps、亿级DAU，小游戏《羊了个羊》是怎么架构的？][21WQps_DAU]》

《[场景题：假设10W人突访，你的系统如何做到不 雪崩？][10W_]》

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

《[Nginx面试题（史上最全 + 持续更新）][Nginx_ _]》

《[K8S面试题（史上最全 + 持续更新）][K8S_ _]》

《[操作系统面试题（史上最全、持续更新）][Link 3]》

《[Docker面试题（史上最全 + 持续更新）][Docker_ _]》

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

《[Flux、Mono、Reactor 实战（史上最全）][Flux_Mono_Reactor]》

《[sentinel （史上最全）][sentinel]》

《[Nacos (史上最全)][Nacos]》

《[TCP协议详解 (史上最全)][TCP_]》

《[分库分表 Sharding-JDBC 底层原理、核心实战（史上最全）][Sharding-JDBC]》

《[clickhouse 超底层原理 + 高可用实操 （史上最全）][clickhouse _ _ _]》

《[nacos高可用（图解+秒懂+史上最全）][nacos]》

《[队列之王： Disruptor 原理、架构、源码 一文穿透][Disruptor _]》

《[环形队列、 条带环形队列 Striped-RingBuffer （史上最全）][_ Striped-RingBuffer]》

《[一文搞定：SpringBoot、SLF4j、Log4j、Logback、Netty之间混乱关系（史上最全）][SpringBoot_SLF4j_Log4j_Logback_Netty]》

《[单例模式（史上最全）][Link 4]》

《[红黑树（ 图解 + 秒懂 + 史上最全）][_ _ _ _]》

《[分布式事务 （秒懂）][Link 5]》

《[缓存之王：Caffeine 源码、架构、原理（史上最全，10W字 超级长文）][Caffeine _10W_]》

《[缓存之王：Caffeine 的使用（史上最全）][Caffeine]》

《[Java Agent 探针、字节码增强 ByteBuddy（史上最全）][Java Agent _ ByteBuddy]》

《[Docker原理（图解+秒懂+史上最全）][Docker]》

《[Redis分布式锁（图解 - 秒懂 - 史上最全）][Redis_ - _ -]》

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

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

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

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

《[Springcloud 高并发 配置 （一文全懂）][Springcloud _ _]》

[Java]: https://blog.csdn.net/crazymakercircle/article/details/124790425
[Link 1]: https://gitee.com/crazymaker/SimpleCrayIM/blob/master/%E7%96%AF%E7%8B%82%E5%88%9B%E5%AE%A2%E5%9C%88%E6%80%BB%E7%9B%AE%E5%BD%95.md
[CPU_900]: https://blog.csdn.net/crazymakercircle/article/details/128805374
[Link 2]: https://blog.csdn.net/crazymakercircle/article/details/128848309
[21WQps_DAU]: https://blog.csdn.net/crazymakercircle/article/details/128725701
[10W_]: https://blog.csdn.net/crazymakercircle/article/details/128533821
[2_ 100_ _ _]: https://blog.csdn.net/crazymakercircle/article/details/128697096
[Nginx_ _]: https://blog.csdn.net/crazymakercircle/article/details/128684641
[K8S_ _]: https://blog.csdn.net/crazymakercircle/article/details/128671196
[Link 3]: https://blog.csdn.net/crazymakercircle/article/details/128708648
[Docker_ _]: https://blog.csdn.net/crazymakercircle/article/details/128670335
[Springcloud gateway _]: https://blog.csdn.net/crazymakercircle/article/details/125057567
[Flux_Mono_Reactor]: https://blog.csdn.net/crazymakercircle/article/details/124120506
[sentinel]: https://blog.csdn.net/crazymakercircle/article/details/125059491
[Nacos]: https://blog.csdn.net/crazymakercircle/article/details/125057545
[TCP_]: https://blog.csdn.net/crazymakercircle/article/details/114527369
[Sharding-JDBC]: https://blog.csdn.net/crazymakercircle/article/details/123420859
[clickhouse _ _ _]: https://blog.csdn.net/crazymakercircle/article/details/126992542
[nacos]: https://blog.csdn.net/crazymakercircle/article/details/120702536
[Disruptor _]: https://blog.csdn.net/crazymakercircle/article/details/128264803
[_ Striped-RingBuffer]: https://blog.csdn.net/crazymakercircle/article/details/128264508
[SpringBoot_SLF4j_Log4j_Logback_Netty]: https://blog.csdn.net/crazymakercircle/article/details/125135726
[Link 4]: https://blog.csdn.net/crazymakercircle/article/details/128265067
[_ _ _ _]: https://blog.csdn.net/crazymakercircle/article/details/125017316
[Link 5]: https://blog.csdn.net/crazymakercircle/article/details/109459593
[Caffeine _10W_]: https://blog.csdn.net/crazymakercircle/article/details/128123114
[Caffeine]: https://blog.csdn.net/crazymakercircle/article/details/113751575
[Java Agent _ ByteBuddy]: https://blog.csdn.net/crazymakercircle/article/details/126579528
[Docker]: https://blog.csdn.net/crazymakercircle/article/details/120747767
[Redis_ - _ -]: https://blog.csdn.net/crazymakercircle/article/details/116425814
[Zookeeper _ - _ -]: https://blog.csdn.net/crazymakercircle/article/details/85956246
[Zookeeper Curator _ - 10]: https://blog.csdn.net/crazymakercircle/article/details/85922561
[Netty _ _ _]: https://blog.csdn.net/crazymakercircle/article/details/83957259
[Netty 100]: https://blog.csdn.net/crazymakercircle/article/details/83758107
[Springcloud _ _]: https://blog.csdn.net/crazymakercircle/article/details/102557988