CS-Wiki CS-Wiki
Home
知识体系总览
  • 数据结构与算法
  • 计算机网络
  • 操作系统
  • MySQL
  • Redis
  • 设计模式
  • Java 基础
  • Java 集合
  • Java 并发
  • Java 虚拟机
  • Spring
  • Kafka
  • 校招扫盲
  • 项目推荐
  • 唠唠嗑儿
  • 读书笔记
归档
GitHub (opens new window)
Home
知识体系总览
  • 数据结构与算法
  • 计算机网络
  • 操作系统
  • MySQL
  • Redis
  • 设计模式
  • Java 基础
  • Java 集合
  • Java 并发
  • Java 虚拟机
  • Spring
  • Kafka
  • 校招扫盲
  • 项目推荐
  • 唠唠嗑儿
  • 读书笔记
归档
GitHub (opens new window)
  • 小牛肉的 MySQL 知识体系结构
  • SQL 刷题

  • MySQL 体系架构

  • InnoDB 存储引擎

  • 索引

  • 事务

    • 锁

      • 三分钟入门 InnoDB 存储引擎中的表锁和行锁
      • 美团一面:能不能通俗的解释下为什么要有意向锁这个东西?
      • InnoDB 存储引擎中行锁的三种算法
      • 手画图解,关于死锁,面试的一切都在这里了
      • 美团面试特有:写个 SQL 语句然后问加了哪些锁
        • 案例 1:唯一索引等值查询
          • 查询的记录存在
          • 查询的记录不存在
        • 案例 2:唯一索引范围查询
        • 案例 3:非唯一索引等值查询
          • 查询的记录存在
          • 查询的记录不存在
        • 案例 4:非唯一索引范围查询
      • 怎么理解 Next-Key Lock 是 Record Lock 加 Gap Lock?
    • 事务的隔离级别

    • binlog和redolog

  • SQL 优化

  • 35-MySQL
  • 事务
  • 锁
小牛肉
2023-01-29
目录

美团面试特有:写个 SQL 语句然后问加了哪些锁

美团问数据库应该是非常多的,尤其喜欢考手写 SQL 然后问你这个 SQL 语句上面加了哪些锁,你会发现其他厂面试基本很少会这样考,所以很多小伙伴遇到这种问题的时候都是一脸懵逼,这篇文章就来详细总结下 InnoDB 存储引擎中的行锁的加锁规则,并辅以实例解释。

首先众所周知,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 都是普通的列,其上没有任何索引:

id (唯一索引) a (非唯一索引) b
10 4 Alice
15 8 Bob
20 16 Cilly
25 32 Druid
30 64 Erik

# 案例 1:唯一索引等值查询

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

  1. 当查询的记录是存在的,Next-key Lock 会退化成记录锁
  2. 当查询的记录是不存在的,Next-key Lock 会退化成间隙锁

# 查询的记录存在

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

select * from user
where id = 25
for update;

结合加锁的两条核心:查找过程中访问到的对象才会加锁 + 加锁的基本单位是 Next-key Lock(左开右闭),我们可以分析出,这条语句的加锁范围是 (20, 25]

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

# 查询的记录不存在

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

select * from user
where id = 22
for update;

结合加锁的两条核心:查找过程中访问到的对象才会加锁 + 加锁的基本单位是 Next-key Lock(左开右闭),我们可以分析出,这条语句的加锁范围是 (20, 25]

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

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

# 案例 2:唯一索引范围查询

唯一索引范围查询的规则和等值查询的规则一样,只有一个区别,就是唯一索引的范围查询需要一直向右遍历到第一个不满足条件的记录,下面结合案例来分析:

select * from user
where id >= 20 and id < 22
for update;

先来看语句查询条件的前半部分 id >= 20,因此,这条语句最开始要找的第一行是 id = 20,结合加锁的两个核心,需要加上 Next-key Lock (15,20]。又由于 id 是唯一索引,且 id = 20 的这行记录是存在的,因此会退化成记录锁,也就是只会对 id = 20 这一行加锁。

再来看语句查询条件的后半部分 id < 22,由于是范围查找,就会继续往后找第一个不满足条件的记录,也就是会找到 id = 25 这一行停下来,然后加 Next-key Lock (20, 25],重点来了,但由于 id = 25 不满足 id < 22,因此会退化成间隙锁,加锁范围变为 (20, 25)。

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

# 案例 3:非唯一索引等值查询

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

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

    很好记忆,就是要查找记录的左区间加 Next-key Lock,右区间加 Gap lock

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

# 查询的记录存在

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

select * from user
where a = 16
for update;

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

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

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

# 查询的记录不存在

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

select * from user
where a = 18
for update;

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

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

# 案例 4:非唯一索引范围查询

范围查询和等值查询的区别在上面唯一索引章节已经介绍过了,就是范围查询需要一直向右遍历到第一个不满足条件的记录,和唯一索引范围查询不同的是,非唯一索引的范围查询并不会退化成 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]。

🎁 公众号

各位小伙伴大家好呀,叫我小牛肉就行,目前在读东南大学硕士,上方扫码关注公众号「飞天小牛肉」,与你分享我的成长历程与技术感悟~

帮助小牛肉改善此页面 (opens new window)
Last Updated: 2023/02/16, 11:32:28
手画图解,关于死锁,面试的一切都在这里了
怎么理解 Next-Key Lock 是 Record Lock 加 Gap Lock?

← 手画图解,关于死锁,面试的一切都在这里了 怎么理解 Next-Key Lock 是 Record Lock 加 Gap Lock?→

最近更新
01
02
线上环境 CPU 使用率飙升如何快速排查?
03-05
03
面试官再跟你说中国没有根服务器,雪人计划让他了解下
02-23
更多文章>
Theme by Vdoing | Copyright © 2019-2023 飞天小牛肉 | 皖ICP备2022008966号
  • 跟随系统
  • 浅色模式
  • 深色模式
  • 阅读模式