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；不能拜访其余表。整个表格更直观：

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 锁的总结。感激各技术社区大佬们的付出，尤其是极客工夫，真的牛逼。如果说我看得更远，那是因为我站在你们的肩膀上。心愿这篇文章对你有帮忙，咱们下篇文章见~

07 送点面试题 & 电子书

如果看到这里，喜爱这篇文章的话，请帮点个 难看。

初次见面，也不晓得送你们啥。罗唆就送 几百本电子书 和2021 最新面试材料 吧。微信 搜寻 一个优良的废人 回复 电子书 送你 1000+ 本编程电子书；回复 面试 送点面试题；回复 1024 送你一套残缺的 java 视频教程。

面试题都是有答案的，具体如下所示：有须要的就来拿吧，相对收费，无套路获取。