MySQL 的全局锁、表锁、行锁

01 前言

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

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

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

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

1.1 往期精彩

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

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

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

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 为什么要加锁？

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

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

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

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

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

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

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

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

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

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

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

2.3 一致性视图备份

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

PS：不了解事务，看这里肯定一脸懵。建议看这篇：《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；不能访问其他表。整个表格更直观：

	student 表	course 表	其他表
线程A	读	读写	不允许
其他线程	阻塞	阻塞	随便

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

前提：注意，我这里的事务是手动开启和提交的。而 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 中的记录进行操作。

其中事务 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 死锁

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

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

操作开始，事务 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 锁的总结。感谢各技术社区大佬们的付出，尤其是极客时间，真的牛逼。如果说我看得更远，那是因为我站在你们的肩膀上。希望这篇文章对你有帮助，我们下篇文章见~