likes
comments
collection
share

「MySQL高级篇」你真的知道查询的时候有哪些锁吗？

作者站长头像
站长
· 阅读数 19

大家好，我是Zhan，一名个人练习时长一年半的大二后台练习生，最近在学MySQL高级篇，现在正在看面经整理遗漏知识点，欢迎各路大佬一起交流讨论

👉本篇速览

在前面对MySQL高级篇的的学习中，我们从存储引擎、索引、锁、SQL语句优化、事务实现、常用工具这些方面学习了MySQL的底层知识，如果有需要了解的可以去看我前面的文章，文章底部有链接。

在当时讲锁的时候，我们只是介绍了三种粒度锁：全局锁、表级锁、行级锁，但是并没有系统的讲解，在查询的过程中锁的使用情况，当然看本文之前如果不了解三种锁，建议先看这篇文章：「MySQL高级篇」MySQL全局锁、表级锁、行级锁，本文可以当做MySQL锁的进阶篇，当时学的可能比较混乱，本次看面经整理出一些之前零碎的知识点，并补充一些遗漏的知识点：

  • 1️⃣ 表级锁——AUTO-INC锁
  • 2️⃣ 行级锁——插入意向锁
  • 3️⃣ 实验准备工作
  • 4️⃣ 唯一索引等值查询
  • 5️⃣ 唯一索引范围查询
  • 6️⃣ 非唯一索引等值查询
  • 7️⃣ 非唯一索引范围查询
  • 8️⃣ 没有加索引的查询

1️⃣ Auto-Inc 锁

当时在讲三种粒度的锁中的表级别锁的时候，分别讲了表锁、元数据锁、意向锁，但是我们遗漏了一个表级锁：AUTO-INC锁 ，这里我们就做一个补充

通常情况下，在数据库表中我们会设定一个自增的主键字段，那么在后续的插入中，我们即可不需要指定主键的值，让数据库自动为主键赋值，那么这是怎么实现的呢？

首先我们需要一个字段去维护这个ID自增值，并且要注意并发问题，不能出现由于并发产生的ID一样的问题，因此就有了AUTO-INC锁

起初，AUTO-INC锁在插入数据的时候加入，此时其他的事务都无法插入，那么就能维护主键的唯一性和自增，也就是说这把表级锁，在执行插入语句的时候加锁，在执行结束后释放锁，而不是在事务结束后释放锁。

但是，大量的数据进行插入的时候，如果每次插入都需要加一把锁，那么插入的性能将会大大降低，因此，在MySQL5.1.22版本开始，它的实现换了一种方式：

由本来比较重的锁，换成了轻量级的锁，它的实现方式是：在插入数据的时候，给这个字段赋值一个自增的值，然后就释放这个轻量级的锁，不需要等到整个插入语句执行结束再释放锁，它省去了执行的那段时间，只需要申领一个自增ID就释放锁

在MySQL中，提供给了我们选择自增的策略，通过innodb_autoinc_lock_mode这个系统变量区设置：

  1. innodb_autoinc_lock_mode = 0，采用最开始的策略，使用AUTO-INC锁，在语句结束后再释放锁
  2. innodb_autoinc_lock_mode = 2，采用轻量级锁，申请自增主键后就释放锁
  3. innodb_autoinc_lock_mode = 1
    1. 普通insert语句，自增锁在申请后就马上释放
    2. insert……SELECT 这种批量插入数据的语句，等到语句结束后释放

2️⃣ 插入意向锁

我们在讲行级锁的时候，介绍了三种锁：行锁、间隙锁、临键锁，我们在此处也遗漏了一种行级锁：插入意向锁，尽管它的名字里面有意向锁，但是它并不是一种意向锁，而是特殊的间隙锁，属于行级别锁：

在讲间隙锁的时候，我们就说到：在插入一条记录的时候，需要判断插入的位置是否存在间隙锁，如果存在的话，插入这个操作就会被阻塞，直到间隙锁解锁为止

既然是阻塞，也就代表着，如果间隙锁释放了这个操作就会马上执行，这靠的就是插入意向锁，在被阻塞的时候会生成一个插入意向锁，表明有事务想要在某个区间插入记录，但是现在处于阻塞状态。

例如：事务A在某个间隙加了一个间隙锁，但是B事务要在这个间隙插入一条数据，但是发现这个位置被加了间隙锁，就会生成一个插入意向锁，然后把锁的状态设置为等待状态，当然，此处事务B并没有获取到了锁，只是处于等待状态，然后事务B就会一直阻塞到A提交事务。

3️⃣ 实验准备

在之前的学习锁的过程中，我们确确实实演示了很多锁的情况，但是没有系统的讲解，什么时候会产生什么样的锁，也就是说对于某一个查询的语句，它底层的锁的情况很模糊，今天就来系统的讲解一下各种查询的情况下，加的锁的情况：

首先回顾一下之前的知识：

  • 对于普通的SELECT语句，使用的是快照读，也就是说，它的实现是由MVCC实现的，并不会加行级锁。
  • 对于当前读，也就是SELECT……IN SHARE MODE、SELECT……FOR UPDATE等语句，是会加行级锁的。
  • 对于查询和删除操作，都会加行级锁，并且锁的类型为独占锁，即排它锁。

因此对于查询语句的加锁情况，我们只研究当前读

那么对于当前读的语句，加锁情况是什么样的呢？我们来一起实验一下，在此之前，我们先做好实验的准备：

创建了一张表用于操作：

CREATE TABLE `volleyball` (
	`id` BIGINT NOT NULL AUTO_INCREMENT,
	`name` VARCHAR(30) COLLATE utf8mb4_unicode_ci NOT NULL,
	`age` int NOT NULL,
	PRIMARY KEY(`id`),
	KEY `index_age` (`age`) USING BTREE
)ENGINE = INNODB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

导入了十条数据方便后续的使用：

INSERT INTO `volleyball`(`id`, `name`, `age`) VALUES (1, '日向翔和', 18);
INSERT INTO `volleyball`(`id`, `name`, `age`) VALUES (2, '影山', 19);
INSERT INTO `volleyball`(`id`, `name`, `age`) VALUES (3, '旭', 21);
INSERT INTO `volleyball`(`id`, `name`, `age`) VALUES (4, '小武老师', 30);
INSERT INTO `volleyball`(`id`, `name`, `age`) VALUES (5, '清水', 20);
INSERT INTO `volleyball`(`id`, `name`, `age`) VALUES (6, '月岛', 18);
INSERT INTO `volleyball`(`id`, `name`, `age`) VALUES (7, '西谷', 19);
INSERT INTO `volleyball`(`id`, `name`, `age`) VALUES (8, '田中', 20);
INSERT INTO `volleyball`(`id`, `name`, `age`) VALUES (9, '菅原', 22);
INSERT INTO `volleyball`(`id`, `name`, `age`) VALUES (10, '大地', 22);

4️⃣ 唯一索引等值查询

我们使用唯一索引进行等值查询，查询的记录存在与否将会影响到加锁的类型：

在分别讲两种情况之前，我们补充两个知识点：

  • 加锁的对象是索引，加锁的基本单位是临键锁，而行锁和间隙锁，是由临键锁退化而来的
  • 还有就是临键锁是左开右闭区间，间隙锁是开区间

记录存在

如果记录存在，那么事务会给查询的这个值加上写的行锁，这个我们在讲锁的时候提到过，此时其他事务修改这条数据的时候就会被阻塞，因为写的行锁与其他的锁都互斥，这样就能保证在一个事务中，两次查询得到的数据是一致的，也就是避免了幻读。

下面的结果就是这样，左边的事务使用了当前读，加上了行锁，右边的更新语句就会被阻塞，但是如果我们进行对其他行数据的操作就无妨：

「MySQL高级篇」你真的知道查询的时候有哪些锁吗？

我们通过这个命令来详细看一下此时的锁：select * from performance_schema.data_locks;

「MySQL高级篇」你真的知道查询的时候有哪些锁吗？

  • 首先就是表锁，这个也就是意向锁，表明此时不能锁全表：
  • 再就是这一行的行锁，有两个字段我们单独拿出来说一下：LOCK_TYPE、LOCK_MODE
    • LOCK_TYPE：表示锁的粒度，分别有行级锁、表级锁、全局锁三种类型，此处的RECORD就表示行级锁
    • LOCK_MODE：知道了行级锁，是我们介绍的哪种行级锁呢，就需要看这个字段，它有三个值：
      • x：说明是临键锁
      • x，REC_NOT_GAP：说明是记录锁
      • x，GAP：说明是间隙锁

总结一下，其实我们只是演示了一下：在事务中使用当前读，读取记录存在的情况下，加锁的情况，其实就是加了一个互斥锁，防止其他事务修改改数据，导致幻读（幻读就是当一个事务前后两次查询结果集不同）

记录不存在

而如果记录不存在，在上面的演示过程中我们删除了3号，现在我们进行测试，看看会有什么样的结果发生：

「MySQL高级篇」你真的知道查询的时候有哪些锁吗？

我们会发现，在这个过程中生成的锁有两种：

  1. 一种是和之前一样的表级锁中的意向锁
  2. 而另外一种我们可以发现LOCK_MODE为X,GAP，也就是间隙锁，此时就是给(2,4)之间加上了间隙锁，那么此时再想往这个间隙插入id为3的数据就会被阻塞，那么这个间隙不被插入数据，在该事务中两次查询得到的结果一定就是一样的，也就是解决了幻读的问题。

5️⃣ 唯一索引范围查询

当使用唯一索引进行范围查询的时候，会对每一个扫描到的索引加临键锁，但是正如我们前面所说的，为了效率，有些临键锁会退化为间隙锁和行锁：这又是四种情况，从大的方面来看，可以分为大于等于、小于等于、大于、小于，从带等于的方面来可以分为等于的这个值是否存在。我们下面就来分类讨论一下：

💡Tips：在下文中，我希望大家能在看到具体是用了哪些锁之前想象一下，如果是你来设计，你会设计哪些锁，因为猜想、验证、理解，得到的知识更加牢固

🔴大于

废话少说，我们直接上场景：SELECT * FROM volleyball where id > 9执行过程中加锁情况为：

  • 最开始符合场景的就是id=10，便在(9,10]加了一个临键锁
  • 然后继续往下，后面尽管没有记录，但是会在(10,+∞]加一个临键锁

我们进入场景使用命令看看锁的情况：

「MySQL高级篇」你真的知道查询的时候有哪些锁吗？

也就是说，对于大于的范围查询，它会一直寻找数据，直到找不到数据，选择最大值作为右边界，然后所有的锁都是临键锁

🟢 大于等于

对于SELECT * FROM volleyball where id >= 9 for update，它的加锁情况为：

  • 在id=9这条记录上加上了行锁，也就是说其他事务无法更改这条数据
  • 而其他的还是两个临键锁，其实归根到底还是不让这些数据发生变化导致幻读

「MySQL高级篇」你真的知道查询的时候有哪些锁吗？

但是，如果id=9这条记录不存在，情况是否会有变化，我们删除这条数据再试试：

「MySQL高级篇」你真的知道查询的时候有哪些锁吗？

我们会发现这个锁仍然存在，仔细一想，如果不存在这个行锁，有数据插入进来，就会发现幻读，索引其实我们在分析加什么锁的时候，也可以想想怎么做才不会出现幻读

总的来说，对于大于等于，大于的锁还是临键锁，但是对于等值的地方退化为间隙锁了

🟡 小于

执行select * from volleyball where id < 4 for update，它的加锁情况为：

  • 首先找到的第一个数据是id=1，加上(-∞,1]的临键锁
  • 然后第二个数据是id=2，加上(1,2]的临键锁
  • 然后后续没有数据，但是防止数据插入，会有一个(3,4)的间隙锁

「MySQL高级篇」你真的知道查询的时候有哪些锁吗？

也就是说，从查到的第一条数据开始，构建(-无穷大,第一条数据]的临键锁，然后依次往上走，知道找到不符合条件的那个数据，与不符合条件之前的那个数据构成间隙锁

🟠 小于等于

当执行select * from volleyball where id <= 4 for update时，id=4的值存在，加锁的流程为：

  1. 最开始找到的数据为id=1，于是加上临键锁(-∞,1]
  2. 然后找到的数据为id=2，同样的加上临键锁(1,2]
  3. 然后继续找发现数据为id=4，刚刚好数据存在，加上临键锁(2,4] 「MySQL高级篇」你真的知道查询的时候有哪些锁吗？

但是，如果id=4这条数据不存在呢，结果又是什么样的，我们测试一下：

「MySQL高级篇」你真的知道查询的时候有哪些锁吗？

最开始的流程一样，但是在找完id=2的数据后，由于发现还满足小于等于4，于是找下一条数据，但是下一条数据此时是id=5，不符合条件，于是加上了(2,5)的间隙锁

总之，对于小于的数据都是临键锁，但是如果是等于并且值不存在就会找到不符合条件的数据与不符合条件数据的前一个数据构成间隙锁，但是如果值存在，就全部都是临键锁

💬 总结！！！

看完上面的演示可能脑袋里面很乱，但是又感觉知道了一些东西，因此我希望大家可以以这里的结论作为向导，对于结论中不理解的部分去上面找到原因，对于结论中文字不理解的地方，以实例作为辅助去理解，结论如下：

  • 情况一：针对「大于等于」的范围查询，首先所有的记录都会加上临键锁
    • 但是如果等值查询的记录是存在于表中，那么该记录的索引中的 next-key 锁会退化成行锁。
  • 情况二：针对「小于或者小于等于」的范围查询，要看条件值的记录是否存在于表中：
    • 当条件值的记录不在表中，那么不管是「小于」还是「小于等于」条件的范围查询，扫描到终止范围查询的记录时，该记录的索引的 next-key 锁会退化成间隙锁，其他扫描到的记录，都是在这些记录的索引上加 next-key 锁。
    • 当条件值的记录在表中，如果是「小于」条件的范围查询，扫描到终止范围查询的记录时，该记录的索引的 next-key 锁会退化成间隙锁，其他扫描到的记录，都是在这些记录的索引上加 next-key 锁；如果「小于等于」条件的范围查询，扫描到终止范围查询的记录时，该记录的索引 next-key 锁不会退化成间隙锁。其他扫描到的记录，都是在这些记录的索引上加 next-key 锁。

再就是，其实锁的作用是为了解决幻读而存在，我们不能只记结论，不妨先在自己想想，如果是你来解决幻读，你会加哪些锁，哪些锁能得到优化……

6️⃣ 非唯一索引等值查询

对于等值查询，情况就少一点，就是记录存在和记录不存在，但是我们同时也需要去思考唯一索引和非唯一索引的区别：

🛠 记录不存在

在了解具体怎么实现之前，我们先自己来想象一下怎么实现，能不能直接在这个记录上加锁呢，很明显，并不行，因为锁是加在索引上的，因此我想到的只能是在大于这个值和小于这个值之间加上间隙锁，但是！知道了真正的实现，我人麻了~

首先谈谈，间隙锁是怎么锁的，对于非唯一的索引，它可能出现的问题又是什么：

  1. 现在假设我们存在age的(15,18) 的间隙锁
  2. 我们诚然不能插入age=16,17的数据，但是我们有可能插入age=15,18的数据
  3. 听起来可能有点迷糊，我们首先看一下下面这张图：

「MySQL高级篇」你真的知道查询的时候有哪些锁吗？

  1. 对于二级索引，它的叶子结点可能是这样的，现在我们对id=3和id=5的数据插入了间隙锁
  2. 而间隙锁让插入阻塞的工作机制为：插入的时候首先定位位置，然后如果插入的位置的下一条记录存在间隙锁，那么就会被阻塞
  3. 可是如果我们插入的数据 id = 1 age = 18，此时插入的位置就是最开始的位置，它的下一个记录并不存在间隙锁，因此不会被阻塞
  4. 但是如果插入的数据为 id = 4 age = 18，它就会被阻塞因为它的下一条记录存在间隙锁
  5. 同理id = 4, age=20也会被阻塞，但是id=7,age=20就不会被阻塞

所以其实我们可以发现对于非唯一索引，它的间隙锁除了要维护一个区间里面的数据不被插入，还要保证主键的ID的规则

言归正传，对于记录不存在的值，会在查询的这个值与它后面的那个值加上间隙锁，并且规定主键的规则！，我们进入实际场景一探究竟，执行SQL语句select * from volleyball where age = 18 for update，然后看看它的锁：

「MySQL高级篇」你真的知道查询的时候有哪些锁吗？

首先我们这里确确实实是一个间隙锁，从查询条件18，到第一个查询条件18后面的那个值（B+Tree）也就是19的一个间隙锁

但是，我们也提到需要规定主键的规则，这里我们可以看到第二个值2，它表示在插入age=19的时候不允许插入的新纪录的id小于2，这就是当该值不存在时的情况

⚒ 记录存在的情况

同样的，我们先自己想想记录存在的情况下，加什么样的锁才能保证不出现幻读，首先要考虑不能改变现有的数据，再就是要考虑不能有新增的数据，我的想法就是所有存在的记录加上临键锁，然后两边的交界处加上间隙锁。也就是说，给记录加上行级锁防止被修改，给等值的所有记录加上临键锁，但是给等值最左和最右加上间隙锁。

实际情况与我的猜测有一点点出入，当我执行select * from volleyball where age = 19 for update;，然后查看锁的情况为：

「MySQL高级篇」你真的知道查询的时候有哪些锁吗？

与我的猜想有出入的地方就是，19的左边界是一个临键锁，因为要包含最左边这个结点

7️⃣ 非唯一索引范围查询

同样的，我们也是先自己想想如何去实现，对于非唯一索引的等值查询就已经到了基本上全部使用临键锁的情况，而范围查询，我觉得全部使用临键锁才能解决幻读，当然，对于数据也要做主键的行锁，防止记录被修改。

最后进行测试，发现与想象中的实现基本一致，执行select * from volleyball where age > 19 for update;，得到的结果如下：

「MySQL高级篇」你真的知道查询的时候有哪些锁吗？

8️⃣ 没有加索引的查询

如果没有使用索引作为查询条件，第一，它和非唯一索引范围查询一样，会出现全部使用临键锁，第二，由于没有索引，走的全表扫描。因此，全表都会被锁住！

所以，我们在做具有加锁语句的时候，最好要去看是否有索引，否则很有可能出现全表被锁住，导致其他事务对数据库表进行增删改的时候都被阻塞。

💡 总结

今天首先补充了之前在讲MySQL三种粒度锁时漏掉的两种锁：AUTO-INC锁和插入意向锁，然后呢开始了本文的主题：查询的时候究竟加了哪些锁保证了不出现幻读呢？便开始了我们的实验，一个一个去测试这几种情况：唯一索引的等值查询和范围查询、非唯一索引的等值查询和范围查询、没有加索引的查询，在学习的过程中，从最开始没有思路，到后来自己能够逐渐想到实现的思路是我的进步，我也希望读者能够在读的过程中去思考，在得到结论之前先自己猜想，然后验证，希望和大家共同进步~

这里最后在总结一下，收束一下我们的思路：

  1. 首先对于唯一索引的等值查询：
    1. 如果该值存在，那么这个记录就会退化为行锁
    2. 如果该值不存在，就会找到索引树中右边的值，退化为间隙锁
  2. 再就是唯一索引的范围查询，我们分成了四种情况，这里就不再次把结论贴出来了，大家可以在自己脑子里面再回顾一次，如果有不清晰的可以再去上面看
  3. 非唯一索引的等值查询和范围查询，最主要的就是那个间隙锁的判断，还需要结合主键的值进行判断，这是非唯一索引相较于主键索引的一个大区别

🍁 友链

✒写在最后

都看到这里啦~，给个点赞再走呗~，也欢迎各位大佬指正，在评论区一起交流，共同进步！也欢迎加微信一起交流：Goldfish7710。咱们明天见~

「MySQL高级篇」你真的知道查询的时候有哪些锁吗？