女朋友问我：什么是 MySQL 的全局锁、表锁、行锁？

我就是我 2023-01-13 14:57 174阅读 0赞

# 01 前言 #

小胖真的让人不省心。继上次小胖误删数据之后，这次这货直接给我把整个表锁住了。页面无响应，用户疯狂投诉，我特么脸都绿了。。。

事情是这样的，线上有个数据库几十万的数据，由于一开始没做好规划并没有给热点字段加索引。我就让小胖有空加个索引，没想到这货在用户使用高峰期加。。。

知道原因，我还是比较淡定的。毕竟最近都在研究 MySQL，对于 MySQL 锁的问题解决起来还是得心应手。小胖见我三两下就解决了问题，客户也给出了**卧槽，牛逼的肯定**，忙问我怎么解决的，我点燃手中 82 年的华子深深吸了一口，花了几个小时写了这篇文章给它。

全文 5665 字，从下午四点写到晚上九点，先上张思维导图镇楼：

![MySQL 锁][MySQL]

## 1.1 往期精彩 ##

[1、小胖问我：select 语句是怎么执行的？][1_select]

[2、女朋友问我：MySQL 索引的原理是怎样的？][2_MySQL]

[3、小胖问我：MySQL 日志到底有啥用？][3_MySQL]

[4、老王问我：MySQL 事务与 MVCC 原理是怎样的？][4_MySQL _ MVCC]

# 02 全局锁 #

**全局锁是对整个数据库实例加锁，让其处于只读状态**。MySQL 可以通过 Flush tables with read lock (FTWRL) 实现，**PS：unlock tables 可以解除只读状态**。

执行该命令之后，数据更新语句（DML）数据的增删改操作以及数据操纵语句（DDL）修改表结构等操作将被阻塞。

## 2.1 全局锁的应用场景 ##

最典型的要数**全库逻辑备份**，就是**把整个库的所有表都 select 出来存成文本**。

假设现在我的数据库是读写分离的：主写从读。有一种思路是使用 FTWRL 定时备份 + binlog 恢复增量数据。

用 FTWRL 确保备份期间不会有其他线程对数据库做更新操作，然后整库备份。**这时数据库实例会处于只读状态**，就会造成两个问题：

*  在主库备份，备份期间不能写入，业务就会收到严重影响。
 *  在从库备份，备份期间不能执行主库同步的过来的 binlog（锁住了，不能写入），就会导致主从延迟，业务也会受到影响。

如果非要用这种方式，那么建议是在一个月黑风高，系统最少用户在使用的时候。

## 2.2 为什么要加锁？ ##

上面说了，利用全局锁备份会造成两个问题。那不加锁行吗？废话，肯定是不行的。不加锁，你养我呀（备份出问题被开除）?

不加锁同样会出现意想不到的问题：举个栗子，看电影买票，系统有个余额表和用户已购票表。

![为什么要加锁？][b7952e475fbc241bf71172daf8c1c221.png]

现在我要备份，期间有人买票。逻辑上：余额表减掉相应金额，已购票表加上一张票。备份就会出现两个问题：

*  先备份余额表，用户购买，再备份用户表。这是会怎样呢？方便理解，我画张图：

从上图，我们也大概知道发生了啥。我来捋一捋：

T1 时刻是备份前两个表的数据状态；T2 时刻开始备份，只备份了余额表；T3 时刻，由于没有加锁，用户买票；T4 时刻是买完票后的状态；T5 时刻备份到已购票表。

看最终的备份状态你发现没有？？？用户钱没少，票却多了一张（用户窃喜，程序员苦逼）。

以上就是不加锁的下场，它会导致数据前后不一致。这还是先备份余额表后备份已购票表的情况出现的问题。

如果，备份的顺序颠倒一下就会出现：用户钱少了，票却没增加（你指定被投诉，程序员还是苦逼）。

通过上面分析知道，**不加锁的话。备份得到的库不是同一个逻辑时间点，才会造成这种后果**。那怎么保证是同一逻辑时间点呢？

这时候就要引入上篇文章提到的一致性视图。

## 2.3 一致性视图备份 ##

上篇说到**在可重读隔离级别下开启一个事务，会创建一致性视图**。

PS：不了解事务，看这里肯定一脸懵。建议看这篇：[《MySQL 事务与 MVCC 原理》][4_MySQL _ MVCC]

你可能会问：**狗狗你说得，我都知道。问题是怎么在备份的时候开启事务呢？**

是这样，MySQL 自带的逻辑备份工具是 mysqldump 。它使用参数 -single-transaction 可以启动一个事务，从而确保拿到一致性视图。并且由于 MVCC 的支持，备份期间数据库仍可以写入。比如像这样：

// 详细参数见：cnblogs.com/markLogZhu/p/11398028.html
    // 格式：mysqldump [选项] --数据库名 [选项 表名] > 脚本名
    mysqldump -uroot -p test -single-transaction > /backup/mysqldump/test.db

这时好学的朋友可能会说：**既然有了这功能，那不用 FTWRL 命令行不行呀？**

答案是：**可以的，前提是你的数据库引擎要支持可重复读隔离级别，比如：InnDB；如果是 MyLSAM，那么很抱歉，你还是得用 FTWRL，不然备份拿到的视图还是不一致**。就会出现上面数据不一致的问题。

## 2.4 readonly = 1 的方式行么？ ##

提到全库只读你可能想到这个命令：

mysql> set global read_only=1;

能使用它来让全库只读么？不行或者说是不建议，主要原因有三点：

*  影响业务逻辑；set global read\_only=1 可能会用于一些业务判断，比如：主从的判断，从库只读。
 *  异常不释放状态；FTRWL 命令在异常发生时，会自动释放全局锁；而 set global read\_only=1 在异常时，数据库会一直保持只读状态，这时候业务就完犊子了。
 *  set global read\_only=1 这个命令对超级管理员角色无效；备份期间，超管更新数据库还是会导致数据不一致问题。

# 03 表级锁 #

MySQL 有两种表级锁：表锁以及元数据锁（meta data lock，MDL）

## 3.1 表锁 ##

**表锁的语法是这样的：lock tables … read/write，它是显式使用的，同样也是通过 unlock tables 主动释放锁；当然，客户算断开或者异常时也会释放**。

mysql> lock tables student read,course read;
    mysql> SELECT count(1) FROM student;
    mysql> SELECT count(1) FROM course;
    mysql> unlock tables;

需要注意一点：lock tables 除了会限制别的线程读写以外，也限定了本线程接下来操作的对象。举个栗子：

**线程 A 执行 lock tables student read,course write; 语句，其他线程读 student、读写 course 都会被阻塞。同时，线程 A 在执行 unlock tables 之后，也只能读 student、读写 course；不能访问其他表**。整个表格更直观：

<table> 
 <thead> 
  <tr> 
   <th></th> 
   <th>student 表</th> 
   <th>course 表</th> 
   <th>其他表</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>线程A</td> 
   <td>读</td> 
   <td>读写</td> 
   <td>不允许</td> 
  </tr> 
  <tr> 
   <td>其他线程</td> 
   <td>阻塞</td> 
   <td>阻塞</td> 
   <td>随便</td> 
  </tr> 
 </tbody> 
</table>

PS：在没有更细粒度的年代，表锁是最常用与处理并发的方式。但是对于 InnDB 来说，一般不使用 lock tables 控制并发，因为粒度太大了。

## 3.2 MDL 元数据锁 ##

MDL 不需要我们记命令，它是隐式使用的，访问表会自动加上。它的主要作用是**防止 DDL（改表结构） 和 DML（CRUD 表数据） 并发的冲突**。

举个栗子，线程 A 遍历查询表数据，这期间线程 B 删了表的某一列，这时 A 拿到的数据就跟表结构对不上，MySQL 不允许这种事发生，所以在 5.5 版本引入了 MDL。

它的逻辑很简单，对表进行 CRUD 操作，加 MDL 读锁；对表结构下手时，加 MDL 写锁。因此：

*  读读不互斥，可以多线程对一张表增删改查。
 *  读写互斥、写写互斥，保证对表结构下手时只能有一个线程操作，另一个进入阻塞。

### 3.2.1 加个字段就搞挂数据库？ ###

我们知道 MDL 默认是系统加的，**对表结构下手时（加字段、该字段、加索引等等），需要全表扫描**。对大表操作时，你肯定会选月黑凤高，系统使用人数最少时进行，以免遭投诉。

但不只是大表，有时候对小表进行操作时，也会有这样的问题。比如下面的例子：4 个session 对表进行操作。

PS：版本是 MySQL 5.7

![加个字段][06bdb29ee3340b05751054d7607ea565.png]

前提：**注意，我这里的事务是手动开启和提交的。而 MDL 锁是语句开始时申请，事务提交才释放。所以，如果是自动提交就不会出现下面的问题**。

*  T1、T2 时刻 session A 事务启动，**加个 MDL 读锁**，然后执行 select 语句。注意：这时事务并没有提交；
 *  T3 时刻 session B 也是读操作，可以共享 MDL 读锁，顺利执行；
 *  T4 时刻 session C 不讲武德，对表执行 DDL （改表结构）操作，需要的是 MDL 写锁，所以被阻塞；
 *  T5 时刻 session D 也是读操作，按道理说 session C 阻塞应该没影响。

**但是 MySQL 有一个队列会根据时间先后决定哪个 Session 先执行。所以，不管是 D 还是之后的 session 都会被 C 阻塞。而恰巧 student 又是访问频率很高的表，如此这个库的线程数很快就打满了**。

此时，数据库完全不能读写，甚至导致宕机，在用户界面看来就是没响应了。

### 3.2.2 安全地更改表 ###

相信你都看出来了，出现上面问题是因为使用了长事务（一个事务包括 session A、B、C、D 的操作）。事务一直不提交，MDL 锁就会一直被占用。

所以，遇到这种情况就要在 MySQL 的 information\_schema 表中先找出长事务对应的线程，把它 kill 掉。

// MySQL 长事务请看这篇：cnblogs.com/mysqljs/p/11552646.html
    // 查询事务
    select * from information_schema.INNODB_TRX;

那你可能又问了。我的表就是热点表访问很高频，但我又不得不加个字段。那应该咋办呢？回想下多线程业务操作时，线程一直拿不到锁，我们是怎么处理的？

**没错，就是加超时时间**。比如在 alter 语句里面加个等待时间，超过了这时间还拿不到锁。也不要阻塞后面的业务查询语句，先放弃更改。之后再交由你司 DBA 重复这个过程，直到更改成功。加等待时间语句，像下面这样的：

// N 以秒为单位
    ALTER TABLE tbl_name WAIT N add column ...

# 04 行锁 #

mysql 的行索是在引擎实现的，但并不是所有引擎都支持行锁，不支持行锁的引擎只能使用表锁。

行锁比较容易理解：行锁就是针对数据表中行记录的锁。比如：事务 A 先更新一行，同时事务 B 也要更新同一行，则必须等事务 A 的操作完成后才能进行更新。

## 4.1 两阶段提交 ##

先举个栗子：事务 A 和 B 对 student 中的记录进行操作。

![两阶段提交][96c462b8a3a6c43c2df46d0f59e1f065.png]

其中事务 A 先启动，在这个事务中更新两条数据；事务 B 后启动，更新 id = 1 的数据。由于 A 更新的也是 id = 1 的数据，**所以事务 B 的 update 语句从事务 A 开始就会被阻塞，直到事务 A 执行 commit 之后，事务 B 才能继续执行**。

在事务期间，事务 A 实际上持有 id = 1 和 id = 2 这两行的行锁。**如果事务 B 更新的是 id = 2 的数据，那么它阻塞的时间就是从 A 更新 id = 2 这行开始（事务 A 更新 id = 1 时，它并没有阻塞），到事务 A 提交结束，比更新 id = 1 数据阻塞的时间要短**。PS：理解这句话很重要。

**在 InnoDB 事务中，行锁是在需要的时候才加上的，但并不是不需要了就立刻释放，而是要等到事务结束时才释放。这个就是两阶段锁协议**。锁的添加与释放分到两个阶段进行，之间不允许交叉加锁和释放锁。

根据这个特性，**对于高并发的行记录的操作语句就可以尽可能的安排到最后面，以减少锁等待的时间，提高并发性能**。

举个栗子：广州长隆乐园卖票系统。卖出一张票的逻辑应该分三步：

*  1、扣除用户账户余额
 *  2、增加长隆账户收入
 *  3、插入一条交易记录

三个操作必须是要放在同一个事务当中，那应该怎么安排它们的执行顺序呢？做个分析：

*  用户余额表是个人的，并发度很低；
 *  长隆账户表每个用户买票都要访问，并发度最高；
 *  交易记录表是插入操作问题不大；

**这时将事务步骤安排成 3、1、2 这样的顺序是最佳的。因为此时如果有别的用户买票，它的事务在顺序 1、2 并不会阻塞，而是到了顺序 3 更新长隆账户表才会引起阻塞。但它的阻塞时间是最短的**。

## 4.2 死锁 ##

不同线程出现循环资源依赖，涉及的线程都在等待别的线程释放资源时，就会导致这几个线程都进入无限等待的状态，称为死锁。

举个行锁死锁的例子：两个事物相互等待对方持有的锁。

![死锁][a78b37c3ada9963eb37d219935432bad.png]

操作开始，事务 A 持有 id = 1 的行锁，事务 B 持有 id = 2 的行锁；事务 A 想更新 id = 2 行数据，不料事务 B 已持有，**事务 A 只能等待事务 B 释放 id = 2 的行锁**；同理，事务 B 想更新 id = 1 行数据，不料事务 A 已持有，**事务 B 只能等事务 A 释放 id = 1 的行锁**。

两者互相等待，一直到完犊子。这就是死锁，懂了么？

## 4.3 如何解决死锁？ ##

那出现了死锁怎么办？有两个解决策略：

*  进入等待，直到超时
 *  进行死锁检测，主动回滚某个事务

### 4.2.2 加入等待时间 ###

首先是第一种：**直接进入等待，直到超时。这个超时时间可以通过参数 innodb\_lock\_wait\_timeout 设置**。 这个参数，默认设置的锁等待时间是 50s

在 MySQL 中，像下面这样执行即可：

// 设置等待时间
    mysql> set global innodb_lock_wait_timeout = 500;

上面这个语句表示：当出现死锁以后，第一个被锁住的线程要过 500s 才会超时退出，然后其他线程才有可能继续执行。

你可能说这不解决啦？真简单。别得意，这里还有个坑。到底设置多长的过期时间合适呢？

我设置 1s 吧，有些线程可能并没有发生死锁，只是正常的等待锁。这就会造成本来正常的线程让我给干掉了。

### 4.2.3 死锁检测 ###

再看第二种：**死锁检测，主动回滚某个事务**。MySQL 通过设置 innodb\_deadlock\_detect 的值决定是否开启检测，默认值是 on（开启）。

主动死锁检测在发生死锁的时候，可以快速发现并进行处理的，但是它也有额外负担。

什么负担呢？循环依赖检测，过程如下图：

> 新来的线程 F，被锁了后就要检查锁住 F 的线程（假设为 D）是否被锁，如果没有被锁，则没有死锁，如果被锁了，还要查看锁住线程 D 的是谁，如果是 F，那么肯定死锁了，如果不是 F（假设为 B），那么就要继续判断锁住线程 B 的是谁，一直走知道发现线程没有被锁（无死锁）或者被 F 锁住（死锁）才会终止

如果大量并发修改同一行数据，死锁检测又会怎样呢？

**假设有 1000 个并发线程同时更新同一行，那么死锁检测操作就是 1000 x 1000 达到 100 万量级的。即便最终检测结果没有死锁，但这期间要消耗大量 CPU 资源。所以，你就会看到 CPU 利用率很高，但是每秒却执行不了几个事务的情况**。

### 4.2.4 解决热点行更新问题 ###

那前面两种方案都有弊端，死锁的问题应该怎么解决呢？

**一种比较依赖运气的方法就是：如果你能确保这个业务一定不会出现死锁，可以临时把死锁检测关掉**。但是这可能会影响到业务：开启死锁检测，出现死锁就回滚重试，不会影响到业务。如果关闭，可能就会大量超时，严重就会拖垮数据库。

**另一种就是在服务端（消息队列或者数据库服务端）控制并发度**：之所以担心死锁检测会造成额外的负担，是因为并发线程很多的时候，假设我们能在服务端做下限流，比如同一样最多只能允许 10 个线程同时修改。

一个思想：**减少死锁的主要方向，就是控制访问相同资源的并发事务量**。

# 05 巨人的肩膀 #

*  《高性能 MySQL》
 *  time.geekbang.org/column/article/69862
 *  cnblogs.com/dyh004/p/11264569.html
 *  cnblogs.com/mysqljs/p/11552646.html
 *  blog.csdn.net/Annie\_ya/article/details/104938829
 *  blog.csdn.net/u012483153/article/details/107308715

# 06 总结 #

本文详细介绍了 MySQL 的全局锁、表级锁、元数据锁以及行锁和死锁。其中全局锁撩到了应用场景、为什么备份要加全局锁？如何利用一致性视图备份以及为啥 readonly = 1 不适合用来做备份？

表级锁聊了表锁、MDL 元数据锁以及怎么利用 MDL 锁安全快速更改表结构；行锁聊了两阶段提交、死锁的定义、死锁的检测以及给怎么解决死锁，提供了两种思路。

好啦，以上就是狗哥关于 MySQL 锁的总结。感谢各技术社区大佬们的付出，尤其是极客时间，真的牛逼。如果说我看得更远，那是因为我站在你们的肩膀上。希望这篇文章对你有帮助，我们下篇文章见~

# 07 送点面试题 & 电子书 #

如果看到这里，喜欢这篇文章的话，请帮点个**好看**。

初次见面，也不知道送你们啥。干脆就送**几百本电子书**和**2021最新面试资料**吧。微信搜索**一个优秀的废人**回复**电子书**送你 1000+ 本编程电子书；回复**面试**送点面试题；回复**1024**送你一套完整的 java 视频教程。

面试题都是有答案的，详细如下所示：有需要的就来拿吧，**绝对免费，无套路获取**。

![面试题][f7fb80ac3cce1e9520824533308f57b3.png]

[MySQL]: /images/20221022/d0b14cc06bb041db83da9872cf869920.png
[1_select]: https://mp.weixin.qq.com/s/lRY7b9iS_xDDuyKNQKUWSg
[2_MySQL]: https://mp.weixin.qq.com/s/ZDM_ttWCstw0mUwGtUEciw
[3_MySQL]: https://mp.weixin.qq.com/s/yG2pQW7qkTPF4TLBk8qgwQ
[4_MySQL _ MVCC]: https://mp.weixin.qq.com/s/l62CAZ55ZU9f9fsLOQR71A
[b7952e475fbc241bf71172daf8c1c221.png]: /images/20221022/292868708f1d4f64afc4de538c615e74.png
[06bdb29ee3340b05751054d7607ea565.png]: /images/20221022/daadd4d198e547d993f88dab405cac48.png
[96c462b8a3a6c43c2df46d0f59e1f065.png]: /images/20221022/49ea3fd8303842ae8d277be7f6ae4575.png
[a78b37c3ada9963eb37d219935432bad.png]: /images/20221022/e654bec302df45ecb34bbdabcf87292b.png
[f7fb80ac3cce1e9520824533308f57b3.png]: /images/20221022/c3c84be28588406390fe630cc9a5a353.png