【搞定MySQL数据库】第7篇：MySQL中的锁：全局锁、表锁、行锁

一时失言乱红尘 2022-03-28 14:50 252阅读 0赞

本文为本人学习极客时间《MySQL实战45讲》的学习笔记。

原文链接：[https://time.geekbang.org/column/article/69862][https_time.geekbang.org_column_article_69862]

[https://time.geekbang.org/column/article/70215][https_time.geekbang.org_column_article_70215]

### **本文目录：** ###

**1、全局锁**

**2、表级锁**

**3、行锁**

3.1、两阶段锁协议

3.2、死锁和死锁的检测

**4、总结**

--------------------

本文用来聊聊 MySQL 的锁。数据库锁设计的初衷是处理并发问题。作为多用户共享的资源，当出现并发访问的时候，数据库需要合理地控制资源的访问规则。而锁就是用来实现这些访问规则的重要数据结构。

**根据加锁的范围，MySQL 里面的锁大致可以分成全局锁、表级锁和行锁三类**。锁的设计比较复杂，本文介绍的主要是碰到锁时的现象和其背后的原理。

## 1、全局锁 ##

顾名思义，**全局锁就是对整个数据库实例加锁**。**MySQL 提供了一个加全局锁的方法，命令是 Flush tables with read lock(FTWRL)**。当你需要让整个库处于只读状态的时候，可以使用这个命令，之后其他线程的以下语句会被阻塞： 数据更新语句（数据的增删改）、数据定义语句（包括建表、修改表结构等）和更新类事务的提交语句。

**全局锁的典型使用场景是：做全库逻辑备份。**也就是把整库每个表都 select 出来存成文本。

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3Bjd2wxMjA2_size_16_color_FFFFFF_t_70][]

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3Bjd2wxMjA2_size_16_color_FFFFFF_t_70 1][] 业务和备份状态图

![20190110204701614.png][]

也就是说**不加锁的话，备份系统备份得到的库不是一个逻辑时间点，这个视图是逻辑不一致的。**

前面讲事务隔离的时候，在可重复读隔离级别下开启一个事务是可以拿到一致性视图的。

**官方自带的逻辑备份工具是 mysqldump。当 mysqldump 使用参数 -single-transaction 的时候，导数据之前会启动一个事务，来确保拿到一致性视图。而由于 MVCC 的支持，这个过程中数据是可以正常更新的。**

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3Bjd2wxMjA2_size_16_color_FFFFFF_t_70 2][]

你也许还会问，**既然要全库只读，为什么不使用 set  global  readonly = true 的方式呢？**

readonly 方式确实是可以让全库进入只读状态，但是这里还是建议使用 FTWRL ，原因如下：

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3Bjd2wxMjA2_size_16_color_FFFFFF_t_70 3][]

**业务的更新不只是增删改数据（DML），还有可能是加字段等修改表结构操作（DDL）。不论是哪种方法，一个库被全局锁上以后，你要对里面任何一个表做加字段操作，都是会被锁住的。**

但是，即使没有被全局锁住，加字段也不是就能一帆风顺的，因为你还会碰到接下来我们要介绍的表级锁。

--------------------

## 2、表级锁 ##

MySQL 里面表级别的锁有两种：一种是**表锁**，一种是**元数据锁**（meta  data  lock, MDL）。

*  **表锁**

**表锁的语法是：lock  tables ... read/write**。与 FTWRL 类似，可以使用 unlock  tables 主动释放锁，也可以在客户端断开的时候自动释放。需要注意的是，**lock  tables 语法除了会限制别的线程的读写外，也限定了本线程接下来的操作对象。**

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3Bjd2wxMjA2_size_16_color_FFFFFF_t_70 4][]

*  **元数据锁：MDL**

**另一类表级别的是 MDL （metadata  lock）**。MDL 不需要显示使用，在访问一个表的时候会被自动加上。MDL 的作用是：保证读写的正确性。你可以想象下，如果一个查询正在遍历一个表的数据，而执行期间另一个线程对这个表结构做变更，删了一列，那么查询线程拿到的结果是和表结构对不上的，肯定是不行的。

因此，**在 MySQL5.5 版本中引入 MDL，当对一个表做增删该查操作时，加 MDL 读锁；当对表结构变更操作的时候，加 MDL 写锁。**

*  读锁之间不互斥，因此你可以有多个线程同时对一张表增删该查；
 *  读写锁之间、写锁之间是互斥的，用来保证变更表结构操作的安全性。因此，如果有两个线程要同时给一个表加字段，其中一个要等另一个执行完后才能开始执行。

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3Bjd2wxMjA2_size_16_color_FFFFFF_t_70 5][]

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3Bjd2wxMjA2_size_16_color_FFFFFF_t_70 6][]

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3Bjd2wxMjA2_size_16_color_FFFFFF_t_70 7][]

**事务中的 MDL 锁，在语句执行开始时申请，但是语句结束后并不会马上释放，而会等待整个事务提交后再释放。**

基于上面的讨论，我们考虑下：如何安全地给小表加字段？

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3Bjd2wxMjA2_size_16_color_FFFFFF_t_70 8][]

ALTER TABLE tbl_name NOWAIT add column ...
    ALTER TABLE tbl_name WAIT N add column ...

--------------------

## 3、行锁 ##

**MySQL 的行锁是在引擎层由各个引擎自己实现的。但并不是所有的引擎都支持行锁，比如 MyISAM 引擎就不支持行锁。不支持行锁意味着并发控制使用表锁，对于这种引擎的表，同一张表上任何时刻只能有一个更新在执行。这就会影响到业务的并发度。InnoDB 是支持行锁的，这也是 MyISAM 被 InnoDB 替代的重要原因之一。**

下面就聊聊 InnoDB 的行锁，以及如何通过减少锁冲突来提升业务并发度的。

行锁就是针对数据表中行记录的锁。比如：事务 A 更新了一行，而这时候事务 B 也要更新同一行，则必须等事务 A 的操作完成之后才能进行更新。

### 3.1、两阶段锁协议 ###

先看个例子，在下面的操作序列中，事务B 的 update 语句执行时会是什么现象呢？假设字段 id 是表 t 的主键。

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3Bjd2wxMjA2_size_16_color_FFFFFF_t_70 9][]

这个问题的结论取决于事务 A 在执行完两条 update 语句后，持有哪些锁，以及在什么时候释放。你可以验证下：实际上事务 B 的 update 语句会被阻塞，直到事务 A 执行 commit 之后，事务 B 才能继续执行。

所以，上图中事务 A 持有的两个记录的行锁，都是在 commit 的时候才释放掉的。

也就是说，**在 InnoDB 事务中，行锁是在需要的时候才加上的，但并不是不需要了就立刻释放，而是要等到事务结束时才释放。这个就是两阶段锁协议。**

知道了这个设定，**如果你的事务中需要锁多个行，要把最可能造成锁冲突、最可能影响并发度的锁尽量往后放。**（这样占用的时间就少，没占用的时间其他线程还可以用）。下面举个例子。

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3Bjd2wxMjA2_size_16_color_FFFFFF_t_70 10][]

### 3.2、死锁和死锁的检测 ###

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

下面用数据库的行锁举个死锁的例子：

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3Bjd2wxMjA2_size_16_color_FFFFFF_t_70 11][]

这个时候，事务 A 在等待事务 B 释放 id=2 的行锁，而事务 B 在等待事务 A 释放 id = 1 的行锁。事务 A 和事务 B 在互相等待对方的资源释放，就是进入了死锁状态。当出现死锁以后，有两种策略：

> 1、一种策略是：**直接进入等待，直到超时**。这个超时时间可以通过参数：innodb\_lock\_wait\_timeout 来设置；
> 
> 2、另一种策略是：**发起死锁检测**，发现死锁后，主动回滚死锁链条中的某一个事务，让其他事务得以继续执行。将参数 innodb\_deadlock\_detect 设置为 on，表示开启这个逻辑。

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3Bjd2wxMjA2_size_16_color_FFFFFF_t_70 12][]

根据前面的分析，我们来讨论下：**如何解决这种热点行更新导致的性能问题呢？**问题的症结在于：**死锁检测要耗费大量的 CPU 资源。**

**一种头痛医头的方法是：如果你能确保这个业务一定不会出现死锁，可以临时把死锁检测给关掉。**但是这种操作本身带有一定的风险，因为业务设计的时候一般不会把死锁当做一个严重的错误，毕竟出现死锁了，就回滚，然后通过业务重试一般就没有问题了，这是业务无损的。而关掉死锁检测意味着可能会出现大量的超时，这是业务有损的。

**另外一种思路就是控制并发度**：根据上面的分析，你会发现如果并发度能够控制。比如，同一行同时最多只有 10 个线程在更新，那么死锁检测的成本很低，就不会出现这个问题。

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3Bjd2wxMjA2_size_16_color_FFFFFF_t_70 13][]

--------------------

## 4、总结 ##

1、全局锁主要用在逻辑备份的过程中。对于全部是 InnoDB 引擎的库，建议使用 -single  transaction 参数，对应用更加友好；

2、表锁一般是在数据库引擎不支持行锁的时候才会用到；

3、MDL 会直到事务提交才释放掉，在做表结构变更的时候，一定要小心不要导致锁住线上查询和更新；

4、两阶段协议：如何正确安排事务的语句，提高并发度：如果你的事务中需要多个行，要把最可能造成锁冲突或者最可能影响并发度的锁的申请时间放到最后；

5、调整语句顺序并不能完全避免死锁，所以引入了死锁和死锁检测的概念，以及提供了三个方案来减少死锁对数据库的影响。减少死锁的主要方向是：控制访问相同资源的并发事务量。

[https_time.geekbang.org_column_article_69862]: https://time.geekbang.org/column/article/69862
[https_time.geekbang.org_column_article_70215]: https://time.geekbang.org/column/article/70215
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3Bjd2wxMjA2_size_16_color_FFFFFF_t_70]: /images/20220328/3278bcb26f114f98a0c67f2e476becd3.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3Bjd2wxMjA2_size_16_color_FFFFFF_t_70 1]: /images/20220328/843d7fd2699f41f2a0e2dca29a7cef1a.png
[20190110204701614.png]: /images/20220328/787a280f03a54621ac1049a3b3927e9e.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3Bjd2wxMjA2_size_16_color_FFFFFF_t_70 2]: /images/20220328/8b661d0f07144d29a5399276c1f65776.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3Bjd2wxMjA2_size_16_color_FFFFFF_t_70 3]: /images/20220328/375249588e534d5694d54fa767e4dd00.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3Bjd2wxMjA2_size_16_color_FFFFFF_t_70 4]: /images/20220328/5bce8ad05fb5462b85598e21e4f6b2a4.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3Bjd2wxMjA2_size_16_color_FFFFFF_t_70 5]: /images/20220328/8deadd66f4474338ba07ca591b8a9e80.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3Bjd2wxMjA2_size_16_color_FFFFFF_t_70 6]: /images/20220328/561080f79149452683d3dc6eaaa7b2fb.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3Bjd2wxMjA2_size_16_color_FFFFFF_t_70 7]: /images/20220328/12e56bfc341c46a581339217894474dc.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3Bjd2wxMjA2_size_16_color_FFFFFF_t_70 8]: /images/20220328/cc1eb4651b8b4695a328f6233896c57c.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3Bjd2wxMjA2_size_16_color_FFFFFF_t_70 9]: /images/20220328/b0a2703b95d74aafad3569ff3c29aa23.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3Bjd2wxMjA2_size_16_color_FFFFFF_t_70 10]: /images/20220328/a3244b2cdd304b3da53bf62393ce4c8f.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3Bjd2wxMjA2_size_16_color_FFFFFF_t_70 11]: /images/20220328/2162b11d888f46aba3dab8ffa14d348d.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3Bjd2wxMjA2_size_16_color_FFFFFF_t_70 12]: /images/20220328/90368149650645d68351e953040bb69d.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3Bjd2wxMjA2_size_16_color_FFFFFF_t_70 13]: /images/20220328/30b0919a0a2c46ae97e001208c595b7a.png