我的sql没问题为什么还是这么慢｜MySQL加锁规则

╰半夏微凉° 2023-09-28 15:59 144阅读 0赞

### 前言 ###

前阵子参与了字节跳动后端青训营，其中大项目编写涉及到数据持久化一般选择使用`MySQL`。由于时间原因，数据库使用我选择了无脑三板斧：**1. 建立了索引加速查询、2. 关闭自动提交事务、3. 在需要确保原子性的数据库操作之间手动创建和提交事务**。

这么一看，仿佛即使是实际开发也与你此前听闻的一些`MySQL`相关名词：**读写锁**、**间隙锁**、**多版本并发控制**、`redo log`、`bin log`、`undo log`毫不相干，在讲本文的主题之前，我先引入一个真实场景。

在**某次不够规范的**小组开发过程中，开发成员选择测试程序的方式比较原始，大家共享一个测试数据库，各自使用测试账号进行接口的测试，这就意味着数据库中的记录在某一时刻有可能被多个事务访问，甚至在其他人测试的同时，某张数据表的结构被另一位同学修改。

多事务并发访问，反映到开发者这边，就是查询接口**有时**速度很慢。如果你是直接使用数据库管理工具操作数据库表数据/结构，对应的就是`Navicat`不时的陷入较长时间的无响应状态。

当然导致数据库访问速度变慢的原因有很多：**sql语句编写不规范、数据库服务器的性能差、网络状况不佳等**，但是本文所侧重的点在于探究`MySQL`的**锁机制**，在其中发挥了什么作用。

相信在完成本文的阅读之后，你会明白上面的场景的发生，可能是`MySQL`的锁机制从中作祟。

### MySQL的锁有哪几种 ###

#### 全局锁 ####

`MySQL`可以通过显式命令对整个数据库实例加全局读锁：

![format_png][]

此时整个数据库处于只读状态，所有数据记录的更新、数据库/表结构的改动提交都会被阻塞，这可以用于全库的数据备份。

#### 表级锁 ####

表锁

表锁可以通过以下显式命令实现对一个表添加读/写锁：

![format_png 1][]

如果A线程为t1表添加了读锁，为t2表添加了写锁。则其他线程将只能读t1，写t1被阻塞；读/写t2都会被阻塞。而A线程在执行`unlock tables`之前，也只能执行读t1、读/写t2的操作。

元数据锁（metadata lock）

MDL锁不需要显式使用，在访问一个表的时候会被自动加上，并且当事务完成提交时释放。当对一个表数据做CRUD操作的时候，自动加MDL读锁；当对该表结构作出改动的时候，自动加MDL写锁。

*  读锁之间不互斥，因此多个线程才可以同时访问一张数据表。

*  读写锁之间、写锁之间是互斥的（**被读锁占用时，加写锁的线程被阻塞/被写锁占用时，加读锁/写锁的线程都被阻塞**），这也是为了确保表结构的修改和表的数据的操作不发生冲突。

这里展示一个多线程并发操作同一个数据表的案例：

![format_png 2][]

这里`线程B`会因为`线程A`的事务还没有提交，而添加列的操作需要获取**MDL写锁**因此被阻塞，同时`线程C`申请**MDL读锁**的请求又被阻塞在了`线程B`申请**MDL写锁**的请求之后，此时表t在`线程A`事务提交之前，**完全丧失了读写能力**。

**或许此时你已经对于为什么多人调试程序时数据库访问不时出现卡顿有了一些自己的想法，当然这只是锁机制的冰山一角**。

### 行级锁 ###

通过上面的讲解，我们明白了，所谓的读写锁并不是单指一个锁叫读锁/写锁，而是指**不同粒度的锁有读锁和写锁两种状态**，允许的并发程度也有所不同。行级锁也是如此（针对记录行的锁，锁粒度进一步缩小），行锁的存在也使得事务并发访问数据库的性能进一步的提高，并且依旧有读写锁之分，下面介绍。

但区别于全局锁和表级锁，**MySQL行锁**是由各个存储引擎自己实现的，并不是所有的存储引擎都支持行锁（MyISAM不支持），**由于现在MySQL用户大多选择使用InnoDB存储引擎，所以本文将以InnoDB引擎为默认选择**。

#### 两阶段锁协议 ####

在InnoDB事务当中，行锁在需要的时候添加，并且直到事务提交才释放（锁的添加和释放分两个阶段进行），举个例子：

![format_png 3][]

事务A（线程A）在提交之前，占有id=1这条行记录的写锁，事务B（线程B）修改同一行的操作将被阻塞。

#### 死锁与检测 ####

死锁原本是操作系统当中的概念，意思是多个线程都在等待其他线程释放自己需要的资源，使得这些线程陷入无限制的等待。

![format_png 4][]

在这个例子当中，线程A的事务和线程B的事物分别占有`id=1`和`id=2`这两条记录的写锁，使得两个线程在试图获取其他线程占用的锁资源时陷入死锁。

**InnoDB**存储引擎默认**开启了死锁检测**，每个新来的被阻塞的线程，都会主动判断是否是自己的加入导致死锁（检测逻辑就是判断自己需要的行资源是否被别的线程的事务占有），时间复杂度O(n)，一旦检测到，则回滚当前线程的事务，确保其他线程可以得到执行。

这里你会发现，如果同时有多个线程修改同一条记录，一旦并发度很高，则需要消耗O(n^2)时间去完成死锁检测，就会消耗大量CPU资源在死锁检测上，而使得数据库IO的性能下降。

**此时你是否又对我最初给出的小组开发时访问数据库慢的场景有了自己的思考，其实在高QPS情况下，发生死锁检测的概率是大大高于小组开发场景的**

**因此控制热点记录的并发访问数量，是提升数据库IO性能的重要前提。**

#### 多版本并发控制（MVCC） ####

上面讲述了**InnoDB**的update操作会占用行记录的写锁，那么你自然而然就想到，**select查询操作是否就占用了行记录的读锁呢？不完全正确**，这就不得不提及**MySQL的InnoDB引擎的用于控制事务隔离级别的多版本并发控制机制**。

简言之就是每条行记录值的变化是由一个链式的结构组织的，存放在`undo log`文件当中，`undo log`在事务发生回滚的时候，用于回溯事务对行记录的修改过程。

![format_png 5][]

而InnoDB存储引擎默认的事务隔离级别是可重复读（Read Repeatable），简单来说：就是当事务A启动期间，普通的select查询将无法访问到其他事务在此期间对表记录的改动。

关于多版本并发控制（MVCC）这里我没有过多深入讲解，详情给出我的另一篇文章：[juejin.cn/post/708518…][juejin.cn_post_708518]

快照读

![format_png 6][]

对于普通的查询操作，你大致了解InnoDB引擎管理的表的行记录变更是链式组织的，那么每一条记录就相当于一个个的快照，因此普通的select查询操作被称为快照读，会读取到自己可见的最近一个版本（但不一定是最新版本），快照读并不加锁（**也就是没有获取读锁**）。

至于具体读到哪个版本的快照，在上面链接给出的文章中有详细讲解。

当前读

![format_png 7][]

**这里给出了两种不同的当前读方式**，当前读可以读取到`undo log`版本链上的最新记录，不同之处在于，第一条sql获取了id=1这条行记录的**读锁**（在其他事务已经持有id=1行记录的**写锁**时将被阻塞）；第二条select查询虽然也是当前读，但是获取了id=1这条记录的**写锁**（在其他事物已经持有id=1行记录的**读/写锁**时将被阻塞）。

上面讲解死锁检测的时候我用**更新语句**获得了行记录的**写锁**，而这里，通过增加`for update`后缀，可以使得**当前读**操作也获取行记录的**写锁**。

### 间隙锁 ###

间隙锁的出现解决了幻读问题，那么先简述一下幻读的概念，以及幻读有什么问题。

#### 幻读概述 ####

*  在**InnoDB引擎**的可重复读隔离级别下，普通查询是快照读，不会看到其他并发事务插入的数据，因此幻读在**当前读**情况下才会出现。
 *  幻读指当前读场景下，查询到了其他并发事务**新插入的行**（读到其他事务对行记录的修改，并不属于幻读，因为当前读就是会读取到行记录的最新版本）。

#### 幻读的问题 ####

这里用一张表t的操作来描述幻读带来的问题。

![format_png 8][]

![format_png 9][]

以下的分析建立在**没有间隙锁**的情况下（只是为了分析所作的假设）：

*  事务A的第一个sql查询c=1的记录，获得（1，1），此时添加了`for update`，从语义上就是希望锁住所有c=1的行记录。
 *  并且在RR隔离级别下，**所有扫描到的行数据都会加行锁**，因为c字段没有索引，比较c=1的操作需要全表扫描，因此事务A的第一条sql在当前读的情况下，为整张表的3条行记录都添加了写锁。
 *  此时事务B并发插入了一条（2，1）的记录，并且成功。
 *  事务A的第二个sql依旧查询c=1的记录，获得（1，1）、（2，1）两条记录，从语义上违背了第一条sql的目的。（原本打算锁定所有c=1的记录，但是突然又冒出一条记录）

**这里的核心问题就在于：即使所有扫描到的行记录都加上了锁，依旧无法阻止新记录的插入（因为要插入的记录不可能提前锁定），要避免幻读，就需要将记录之间的间隙锁定——间隙锁**。

#### Gap Lock ####

间隙锁在可重复读隔离级别下才有效，所以本文的描述都是基于RR级别（InnoDB存储引擎事务默认隔离级别），这里给出**间隙锁**配合**行锁**工作的一些规则：

*  所有的锁是添加在索引上的

*  加间隙锁的基本单位是next-key lock（前开后闭区间）

*  查找过程中访问到的记录和区间才会加锁
 *  索引上的等值查询，给唯一索引加锁的时候，next-key lock退化为行锁
 *  索引上的等值查询，向右遍历时且最后一个值不满足等值条件的时候，next-key lock退化为间隙锁
 *  唯一索引上的范围查询会访问到不满足条件的第一个值为止

### 小结 ###

本文概述了MySQL锁机制的工作情况，明确了锁有读/写之分。以及给出了一些会触发表锁、行锁的案例，同时，InnoDB存储引擎为了解决幻读问题，引入了间隙锁，用于锁定索引之间的间隙，防止当前读的出错。

还记得文章开头我抛出的实际开发案例吗，相信通过这篇文章的讲解，你对于多事务并发操作数据库时数据库访问性能下降的原因，已经有了不少自己的思考。

![2b38766f300947cc8fe4fdf9466a5c1a.jpeg][]

[format_png]: https://img-blog.csdnimg.cn/img_convert/ee8976a066c7dc7b471234c8c766eac9.webp?x-oss-process=image/format,png
[format_png 1]: https://img-blog.csdnimg.cn/img_convert/45b2d7a8026055a22120933e2fe9e0df.webp?x-oss-process=image/format,png
[format_png 2]: https://img-blog.csdnimg.cn/img_convert/9fc30928487f354ff0aede83ffcbf8ec.webp?x-oss-process=image/format,png
[format_png 3]: https://img-blog.csdnimg.cn/img_convert/deb74f92e24d20f9210d973c27f570a3.webp?x-oss-process=image/format,png
[format_png 4]: https://img-blog.csdnimg.cn/img_convert/03df6a49d51815d40714ac9a48bc4319.webp?x-oss-process=image/format,png
[format_png 5]: https://img-blog.csdnimg.cn/img_convert/ca18f91545af5fd4fca69f62b11cc2a5.webp?x-oss-process=image/format,png
[juejin.cn_post_708518]: https://juejin.cn/post/7085185961239248927
[format_png 6]: https://img-blog.csdnimg.cn/img_convert/e3a7cb14f918854c06ee2855617a97c9.webp?x-oss-process=image/format,png
[format_png 7]: https://img-blog.csdnimg.cn/img_convert/572d8ec3547571f9ab8fba69a2796349.webp?x-oss-process=image/format,png
[format_png 8]: https://img-blog.csdnimg.cn/img_convert/88eaf245772f86b50bcddf4e50ab660a.webp?x-oss-process=image/format,png
[format_png 9]: https://img-blog.csdnimg.cn/img_convert/1ea9c51bf03cfee834a8eb9872d06ed5.webp?x-oss-process=image/format,png
[2b38766f300947cc8fe4fdf9466a5c1a.jpeg]: https://img-blog.csdnimg.cn/2b38766f300947cc8fe4fdf9466a5c1a.jpeg