锁的三重奏：MySQL全局锁、表级锁与行锁详解

拼搏现实的明天。 2023-10-14 18:28 26阅读 0赞

在MySQL的世界里，锁是一把双刃剑。它既可以保证数据的一致性和并发控制，又可能因为不当的使用导致数据的冲突甚至死锁。本文将深入探讨MySQL的锁机制，涵盖全局锁、表级锁（包括表锁和元数据锁）以及行级锁。我们将深入解读这些锁的运作机制，介绍如何使用工具查看锁信息，进而提高我们对数据库的理解和操作能力。最后，我们将探讨死锁的检测和预防，以尽量减少它对数据库操作的影响。让我们一起深入探索MySQL的世界，理解和应用这些关键的并发控制工具。

#### **全局锁** ####

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

如果执行 `flush tables with read lock` 命令行窗口退出后，则数据库会恢复为执行该命令之前的状态。

针对上述我们加全局读锁的方式，可能大家会有疑问：既然要全库只读，为什么不使用 `set global readonly=true` 的方式呢？确实 readonly 方式也可以让全库进入只读状态，但我还是会建议你用 FTWRL 方式，主要有以下几个原因：

*  在有些系统中，readonly 的值会被用来做其他逻辑，比如用来判断一个库是主库还是备库。因此，修改 global 变量的方式影响面更大，因此不建议你使用。（不要随变修改全局的变量，它可能会影响到其他地方的使用。）
 *  在异常处理机制上有差异。如果执行 FTWRL 命令之后由于客户端发生异常断开，那么 MySQL 会自动释放这个全局锁，整个库回到可以正常更新的状态。而将整个库设置为 readonly 之后，如果客户端发生异常，则数据库就会一直保持 readonly 状态，这样会导致整个库长时间处于不可写状态，风险较高。
 *  在 slave 上 如果用户有超级权限的话 readonly 是失效的。

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

全库备份加锁听起来还是很危险的，对于 MySQL 高可用框架而言，只读对可用性和一致性同时造成影响。

*  影响主库的可用性，在备份期间都不能执行更新，业务基本上就得停摆。
 *  影响从库的一致性，从库不能执行主库同步过来的 binlog，会导致主从延迟。

可是为什么还要加锁呢？不加锁会有什么影响呢？

举个简单的例子，假设有两个表，一个账户余额表，一个权益表，先备份账户余额表，然后扣费后再增加权益，最后备份权益表，则最终备份的数据会不一致，相当于是没花钱获得了权益（白嫖）。反过来先备份权益表，最后备份账户余额表，同样存在数据不一致的问题。

所以就好比“快照”一样，备份的时候，需要拿到一个一致性视图。

我们可以在可重复读隔离级别下开启一个事务，然后拿到一个一致性视图，从而在不加锁的情况下进行系统备份。 官方自带的逻辑备份工具是 mysqldump。当 mysqldump 使用参数–single-transaction 的时候，导数据之前就会启动一个事务，来确保拿到一致性视图。而由于 MVCC 的支持，这个过程中数据是可以正常更新的。

**一致性读是好，但前提是引擎要支持这个隔离级别。 single-transaction 方法只适用于所有的表使用事务引擎的库。**

#### **表级锁** ####

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

##### **表锁** #####

表锁的语法是 lock tables … read/write。与 FTWRL 类似，可以用 unlock tables 主动释放锁，也可以在客户端断开的时候自动释放。需要注意，lock tables 语法除了会限制别的线程的读写外，也限定了本线程接下来的操作对象。直白点讲，就类似于**共享读锁独占写锁**的逻辑。

##### **元数据锁MDL** #####

元数据锁是 server 层的锁，表级锁，主要用于隔离 DML（Data Manipulation Language，数据操纵语言，如select、update）和 DDL（Data Definition Language，数据定义语言，如改表头新增一列）操作之间的干扰。每执行一条DML、DDL 语句时都会申请 MDL 锁，DML 操作需要 MDL 读锁，DDL 操作需要 MDL 写锁（MDL 加锁过程是系统自动控制，无法直接干预，读读共享，读写互斥，写写互斥）。

*  读锁之间不互斥，因此你可以有多个线程同时对一张表增删改查。（读锁则共享读的权利，允许所有线程DML，但阻塞所有DDL操作。）
 *  读写锁之间、写锁之间是互斥的，用来保证变更表结构操作的安全性。因此，如果有两个线程要同时给一个表加字段，其中一个要等另一个执行完才能开始执行。

给一个表加字段，或者修改字段，或者加索引，需要扫描全表的数据。索引要根据表中的每一行的记录值来创建，所以需要全表扫描；加字段或修改字段，也要修改每一行记录中的对应列的数据，所以也要全表扫描。

案例：给表加字段存在的问题，可能会导致整个库挂掉（基于MySQL5.6）。如下图所示：

![img][]

在上图 session A 中有个 begin 并且之后一直没有 commit，表示这是一个长事务。

我们可以看到 session A 先启动，这时候会对表 t 加一个 MDL 读锁。由于 session B 需要的也是 MDL 读锁，因此可以正常执行。之后 session C 会被 blocked，是因为 session A 的 MDL 读锁还没有释放，而 session C 需要 MDL 写锁，因此只能被阻塞。

如果只有 session C 自己被阻塞还没什么关系，但是之后所有要在表 t 上新申请 MDL 读锁的请求也会被 session C 阻塞。前面我们说了，所有对表的增删改查操作都需要先申请 MDL 读锁，就都被锁住，等于这个表现在完全不可读写了。

因为 session C 申请写锁 并且在队列处于优先，导致 session C 后面的所有读锁请求申请都被 block 了。如果某个表上的查询语句频繁，而且客户端有重试机制，也就是说超时后会再起一个新 session 再请求的话，这个库的线程很快就会爆满，最终导致表不可用和库挂掉。

从上得知，事务中的 MDL 锁，在语句执行开始时申请，但是语句结束后并不会马上释放，而会等到整个事务提交后再释放。所以建议：**有未提交的事务时不要修改表字段，而且在存在长事务时执行修改表字段命令是一个危险的操作，可能阻塞其它增删改查请求，或导致线程爆满。**

问题：**如何安全地给小表加字段呢？**

1、首先我们要解决长事务，事务不提交，就会一直占着 MDL 锁。在 MySQL 的 information\_schema 库的 innodb\_trx 表中，你可以查到当前执行中的事务。如果你要做 DDL 变更的表刚好有长事务在执行，要考虑先暂停 DDL，或者 kill 掉这个长事务。

2、如果要给热点数据做表结构变更，要带上超时时间，拿不到写锁就放弃。具体做法如下：在 alter table 语句里面设定等待时间，如果在这个指定的等待时间里面能够拿到 MDL 写锁最好，拿不到也不要阻塞后面的业务语句，先放弃。之后开发人员或者 DBA 再通过重试命令重复这个过程。

MariaDB 已经合并了 AliSQL 的这个功能，所以这两个开源分支目前都支持 DDL NOWAIT/WAIT n 这个语法。

SQL复制代码ALTER TABLE tbl_name NOWAIT add column ...
    ALTER TABLE tbl_name WAIT N add column ...

##### 意向锁 #####

InnoDB 引擎的意向锁是表级别的锁，分为两类：

*  意向共享锁（IS Lock)，事务想要获得一张表中某几行的共享锁
 *  意向排他锁（Ix Lock)，事务想要获得一张表中某几行的排他锁

**意向锁协议**

*  事务在获得表中某行上的共享锁之前，必须先获得表上的IS锁或更强的锁。
 *  在事务可以获得表中某一行上的排他锁之前，它必须首先获得表上的IX锁。

由于 InnoDB 存储引擎支持的是行级别的锁，因此意向锁其实不会阻塞除全表扫以外的任何请求。故表级意向锁与行级锁的兼容性如下图所示：

![img][img 1]

#### **行级锁** ####

##### 类型 #####

InnoDB 存储引擎实现了如下两种标准的行级锁：

*  共享锁（S Lock)，允许事务读一行数据。简称为读锁。在获取S锁之前，先获取IS锁或更高级别的锁
 *  排他锁（X Lock)，允许事务删除或更新一行数据，简称为写锁。获取X锁之前，先获取IX锁。

如果事务 `T1` 持有行 `r` 上的共享 ( `S` ) 锁，则来自某个不同事务 `T2` 的对行 `r` 上的锁的请求将按如下方式处理：

*  `T2` 对 `S` 锁的请求可以立即被授予。因此， `T1` 和 `T2` 都在 `r` 上持有 `S` 锁。
 *  `T2 对 X 锁的请求不能立即被授予。`

如果事务 `T1` 持有行 `r` 上的独占 ( `X` ) 锁，则来自某个不同事务 `T2` 的对 `r` 上任一类型的锁的请求无法立即被授予。相反，事务 `T2` 必须等待事务 `T1` 释放其对行 `r` 的锁定。

##### 实现算法 #####

InnoDB 存储引擎有3 种行锁的算法，其分别是：

*  Record Lock：单个行记录上的锁
 *  Gap Lock：间隙锁，锁定一个范围，但不包含记录本身。该锁防止幻读，在 RR隔离级别下生效，如果不想启用，则设置 RC 隔离级别，或者设置innodb\_locks\_unsafe\_for\_binlog=1 。
 *  Next-Key Lock：Gap Lock+Record Lock，锁定一个范围，并且锁定记录本身

Record Lock 总是会去锁住索引记录，而非记录本身，如果 InnoDB 存储引擎表在建立的时候没有设置任何一个素引，那么这时 InnoDB 存储引擎会使用隐式的主键来进行锁定。

所以说当一条 SQL 没有走任何索引时，那么将会在每一条聚集索引后面加**X锁**，这个类似于表锁，但原理上和表锁应该是完全不同的。

间隙锁是对索引记录之间间隙的锁定，或者对第一个索引记录之前或最后一个索引记录之后的间隙的锁定。

后续会有文章详细介绍这三种行锁算法的使用。

##### **两阶段锁** #####

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

![img][img 2]

经过测试可知，事务 B 的 update 语句会被阻塞，直到事务 A 执行 commit 之后，事务 B 才能继续执行。

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

在同一个事务中，如果有多条行记录需要修改，那么该如何设置顺序呢？**如果你的事务中需要锁多个行，要把最可能造成锁冲突、最可能影响并发度的锁尽量往后放。**

假设我们的业务方法中有两条 update 语句，一条 insert 语句，SQL 语句对应业务如下：

1.  从顾客 A 账户余额中扣除电影票价；
2.  给影院 B 的账户余额增加这张电影票价；
3.  记录一条顾客A的交易日志。

在并发环境下，如果此时顾客C又要购票，同样需要三条SQL语句，那么这两个事务容易冲突的部分就是步骤2。基于如下前提：**事务在执行的时候，并不是一次性把所有行锁都持有，而是执行到哪一行就拿哪一行的锁。等到最后commit的时候，一起释放。**

所以，把语句 2 安排在最后，比如按照 3、1、2 这样的顺序，就可以最大程度地减少了事务之间的锁等待，提升了并发度。

注意，\*\*InnoDB 行级锁是通过锁索引记录实现的。\*\*执行 update 语句时根据 where 条件后字段是否有索引，行锁的范围会有很大的不同。下面的结论基于 RR 隔离级别。

比如 `update t set msg='abc' where name='cde';`

*  如果 name 字段上有索引，则只锁住对应的索引记录。如果 name 字段是普通索引，则对应的主键索引也会被加锁。
 *  如果 name字段上没有索引，更新就是走主键索引树，逐行扫描满足条件的行，等于将主键索引所有的行上了锁。
 *  如果name字段上没有索引，在 update 语句后加上 limit 1,扫描主键索引树，直到找到第一条满足条件的行并停止扫描，扫描过的行都会被加上行锁，未扫描的行不会加锁。

所以，当字段没有索引的情况下，即使只更新一条记录，也有可能锁住整张表。

#### 查看锁信息 ####

执行下述命令，可以看到每张表的上锁情况。

SQL复制代码mysql> SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCKS\G
    *************************** 1. row ***************************
        lock_id: 3723:72:3:2
    lock_trx_id: 3723
      lock_mode: X
      lock_type: RECORD
     lock_table: `mysql_db`.`t`
     lock_index: PRIMARY
     lock_space: 72
      lock_page: 3
       lock_rec: 2
      lock_data: 1, 9
    *************************** 2. row ***************************
        lock_id: 3722:72:3:2
    lock_trx_id: 3722
      lock_mode: S
      lock_type: RECORD
     lock_table: `mysql_db`.`t`
     lock_index: PRIMARY
     lock_space: 72
      lock_page: 3
       lock_rec: 2
      lock_data: 1, 9

上述结果中各字段含义：

*  `lock_id`：表示该锁的唯一标识符，由锁事务ID、锁空间（lock\_space）、锁页（lock\_page）和锁记录（lock\_rec）组成。
 *  `lock_trx_id`：表示持有或等待此锁的事务ID。
 *  `lock_mode`：表示该锁的模式。
 *  `lock_type`：表示该锁的类型，此处为记录级（RECORD）锁。
 *  `lock_table`：表示所涉及的表，此处为名为 "t" 的表在 "mysql\_db" 数据库中。
 *  `lock_index`：表示锁定的索引，此处为主键（PRIMARY）索引。
 *  `lock_space`：表示锁涉及的表空间的ID。
 *  `lock_page`：表示锁涉及的页的编号。
 *  `lock_rec`：表示锁涉及的记录的编号。
 *  `lock_data`：此参数在提供的结果中没有对应的说明，无法确定其具体含义。

我们重点学习一下 lock\_mode 参数值：

1.  `S`：表示共享锁（Shared Lock）。允许多个事务同时获取相同范围内的共享锁，用于并发读取数据。
2.  `X`：表示排他锁（Exclusive Lock）。只允许一个事务独占地获取排他锁，用于修改数据。其他事务无法获取共享锁或排他锁。
3.  `IS`：表示意向共享锁（Intent Shared Lock）。表示事务想要获取一个共享锁，用于在获取实际共享锁之前表明其意图。
4.  `IX`：表示意向排他锁（Intent Exclusive Lock）。表示事务想要获取一个排他锁，用于在获取实际排他锁之前表明其意图。
5.  `S,GAP`：表示间隙共享锁（Gap Shared Lock）。用于锁定索引范围之间的间隙，防止其他事务在该范围内插入新记录。
6.  `X,GAP`：表示间隙排他锁（Gap Exclusive Lock）。用于锁定索引范围之间的间隙，防止其他事务在该范围内插入新记录。
7.  `AUTO_INC`： 表示锁定了自增（AUTO\_INCREMENT）的值。
8.  `UNKNOWN` :表示锁定的对象是未知的。

执行下述命令，可以直观地看到哪个事务阻塞了另一个事务。

SQL复制代码mysql> SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCK_WAITS\G
    *************************** 1. row ***************************
    requesting_trx_id: 2644706
    requested_lock_id: 2644706:66:4:8
      blocking_trx_id: 2644700
     blocking_lock_id: 2644700:66:4:8

字段含义如下图所示：

![img][img 3]

执行 show engine innodb status 命令得到的部分输出。这个命令会输出很多信息，有一节 LATEST DETECTED DEADLOCK，就是记录的最后一次死锁信息。

![img][img 4]

我们来看看这图中的几个关键信息。

SQL复制代码1、这个结果分成三部分：
    (1) TRANSACTION 2644529，是第一个事务的信息,对应 session B
    (2) TRANSACTION 2644530，是第二个事务的信息，对应 session A
    WE ROLL BACK TRANSACTION (1)，是最终的处理结果，表示回滚了第一个事务。
    2、第一个事务的信息中：
    WAITING FOR THIS LOCK TO BE GRANTED，表示的是这个事务在等待的锁信息；
    lock_mode X waiting,表示自己加了一个next-key 锁，当前状态是等待中
    0: len 4; hex 8000000a; asc     ;;是第一个字段，也就是 c。值是十六进制 a，也就是 10；
    1: len 4; hex 8000000a; asc     ;;是第二个字段，也就是主键 id，值也是 10；
    
    3、第二个事务显示的信息要多一些：
    “ HOLDS THE LOCK(S)”用来显示这个事务持有哪些锁；
    lock mode S 表示自己持有读锁
    lock_mode X locks gap before rec insert intention waiting 表示准备插入意向锁，尝试获取间隙锁

**在死锁日志里，lock\_mode X waiting 表示 next-key lock；lock\_mode X locks rec but not gap是只有行锁； locks gap before rec 是间隙锁。**

#### **死锁和死锁检测** ####

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

如下图所示，事务 A 在等待事务 B 释放 id=2 的行锁，而事务 B 在等待事务 A 释放 id=1 的行锁。 事务 A 和事务 B 在互相等待对方的资源释放，就是进入了死锁状态。

![img][img 5]

当出现死锁以后，有两种策略：

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

在 InnoDB 中，innodb\_lock\_wait\_timeout 的默认值是 50s，意味着如果采用第一个策略，当出现死锁以后，第一个被锁住的线程要过 50s 才会超时退出，然后其他线程才有可能继续执行。对于在线服务来说，这个等待时间往往是无法接受的。

但是，我们又不可能直接把这个时间设置成一个很小的值，比如 1s。这样当出现死锁的时候，确实很快就可以解开，但如果不是死锁，而是简单的锁等待呢？所以，超时时间设置太短的话，会出现很多误伤。

所以，正常情况下我们还是要采用第二种策略，即：主动死锁检测，而且 innodb\_deadlock\_detect 的默认值本身就是 on。主动死锁检测在发生死锁的时候，是能够快速发现并进行处理的，但是它也是有额外负担的。

每个新来的被堵住的线程，都要判断会不会由于自己的加入导致了死锁，这是一个时间复杂度是 O(n) 的操作。假设有 1000 个并发线程要同时更新同一行，那么死锁检测操作就是 100 万这个量级的。

怎么解决由这种热点行更新导致的性能问题呢？

高并发下避免死锁检测带来的负面影响：

1.  确保业务上不会产生死锁，直接将死锁检测关闭。（innodb 自带死锁检测）
2.  在数据库中间件中统一对更新同一行的请求进行排队，控制并发度。
3.  业务逻辑上进行优化，将一行数据分解成多行，降低写入压力。

此外，并不是**每条事务执行前都需要进行死锁检测吗？**

如果它要加锁的访问的行上有锁，才需要检测。

1、一致性读不会加锁，就不需要做死锁检测；

2、 并不是每次死锁检测都都要扫所有事务。比如某个时刻，事务等待状态是这样的：

*  B在等A，
 *  D在等C，
 *  现在来了一个E，发现E需要等D，那么E就判断跟D、C是否会形成死锁，这个检测不用管B和A。

[img]: https://img-blog.csdnimg.cn/img_convert/a2f7eca30195c8ab3e11aa4c04ab6540.webp?x-oss-process=image/format,png
[img 1]: https://img-blog.csdnimg.cn/img_convert/74c38629db7dadab2896fb79dbe26d0b.webp?x-oss-process=image/format,png
[img 2]: https://img-blog.csdnimg.cn/img_convert/1b5e01ba7858225eab539d0047d90f16.webp?x-oss-process=image/format,png
[img 3]: https://img-blog.csdnimg.cn/img_convert/c85e8021588428f2b14d9d1e5bfa765f.webp?x-oss-process=image/format,png
[img 4]: https://img-blog.csdnimg.cn/img_convert/10484add409d8d95cbe8a766dcb263c3.webp?x-oss-process=image/format,png
[img 5]: https://img-blog.csdnimg.cn/img_convert/c5d046225e9c116b31a3e32a227c3e36.webp?x-oss-process=image/format,png