MySQL 全局锁，表锁，行锁

MySQL 数据库锁

[TOC]

## 1. 锁产生的背景

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

根据加锁的范围，MySQL 里面的锁大致可以分成全局锁、表级锁和行锁三类。

## 2. 全局锁

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

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

以前有一种做法，是通过 FTWRL 确保不会有其他线程对数据库做更新，然后对整个库做备份。注意，在备份过程中整个库完全处于只读状态。

但是让整库都只读，听上去就很危险：

+ 如果你在主库上备份，那么在备份期间都不能执行更新，业务基本上就得停摆；

+ 如果你在从库上备份，那么备份期间从库不能执行主库同步过来的 binlog，会导致主从延迟。

看来加全局锁不太好。但是细想一下，备份为什么要加锁呢？我们来看一下不加锁会有什么问题。

假设你现在要维护“极客时间”的购买系统，关注的是用户账户余额表和用户课程表。

现在发起一个逻辑备份。假设备份期间，有一个用户，他购买了一门课程，业务逻辑里就要扣掉他的余额，然后往已购课程里面加上一门课。

如果时间顺序上是先备份账户余额表 (u_account)，然后用户购买，然后备份用户课程表(u_course)，会怎么样呢？你可以看一下这个图：

![](/UploadFlie/MarkdownImg/20250407/202504070427386813.png)

<small style="margin-left:280px;">图 1 业务和备份状态图</small>

可以看到，这个备份结果里，用户 A 的数据状态是“账户余额没扣，但是用户课程表里面已经多了一门课”。如果后面用这个备份来恢复数据的话，用户 A 就发现，自己赚了。

作为用户可别觉得这样可真好啊，你可以试想一下：如果备份表的顺序反过来，先备份用户课程表再备份账户余额表，又可能会出现什么结果？

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

说到视图你肯定想起来了，我们在前面讲事务隔离的时候，其实是有一个方法能够拿到一致性视图的，对吧？

是的，就是在可重复读隔离级别下开启一个事务。

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

你一定在疑惑，有了这个功能，为什么还需要 FTWRL 呢？**一致性读是好，但前提是引擎要支持这个隔离级别。**比如，对于MyISAM 这种不支持事务的引擎，如果备份过程中有更新，总是只能取到最新的数据，那么就破坏了备份的一致性。这时，我们就需要使用FTWRL 命令了。

所以，**single-transaction 方法只适用于所有的表使用事务引擎的库。**如果有的表使用了不支持事务的引擎，那么备份就只能通过 FTWRL 方法。这往往是 DBA 要求业务开发人员使用 InnoDB 替代 MyISAM 的原因之一。

你也许会问，既然要全库只读，为什么不使用 set global readonly=true 的方式呢？确实 readonly 方式也可以让全库进入只读状态，但我还是会建议你用 FTWRL 方式，主要有两个原因：

+ 一是，在有些系统中，readonly 的值会被用来做其他逻辑，比如用来判断一个库是主库还是备库。因此，修改 global 变量的方式影响面更大，我不建议你使用。

+ 二是，在异常处理机制上有差异。如果执行 FTWRL 命令之后由于客户端发生异常断开，那么 MySQL 会自动释放这个全局锁，整个库回到可以正常更新的状态。而将整个库设置为 readonly 之后，如果客户端发生异常，则数据库就会一直保持 readonly 状态，这样会导致整个库长时间处于不可写状态，风险较高。

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

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

### 2.1. 添加全局锁

```SQL
FLUSH TABLES WITH READ LOCK;
```

### 2.2. 解除全局锁

```SQL
UNLOCK TABLES;
```

***

**完整的加锁解锁示例**

```SQL
-- 添加全局锁
FLUSH TABLES WITH READ LOCK;

-- 这里可以执行一些只读操作，例如查询数据
SELECT * FROM your_table;

-- 解除全局锁
UNLOCK TABLES;
```

### 2.3. 全局锁状态查看

在 MySQL 里，并没有直接查询全局锁状态的系统表或者函数。不过你可以通过检查 `SHOW ENGINE INNODB STATUS` 的输出结果，或者查看 `information_schema.INNODB_TRX` 与 `performance_schema.metadata_locks` 表来间接判断是否处于全局锁状态。下面为你详细介绍方法：

+ 方法一：查看 `SHOW ENGINE INNODB STATUS` 输出
`SHOW ENGINE INNODB STATUS` 命令能够提供 InnoDB 存储引擎的详细状态信息，其中包含锁的相关信息。你可以依据输出里的锁信息来判断是否存在全局锁。
```sql
SHOW ENGINE INNODB STATUS;
```
执行上述命令后，在输出结果里查找和锁相关的部分，特别是事务锁的信息。若有全局锁，通常会有相应的锁等待或者持有信息。

+ 方法二：查询 `information_schema.INNODB_TRX` 表
`information_schema.INNODB_TRX` 表存储了当前 InnoDB 事务的信息，你可以通过查询该表来查看是否有事务持有全局锁。
```sql
SELECT * FROM information_schema.INNODB_TRX;
```
如果查询结果里存在一个事务持有全局锁，那么你能看到相关的事务信息，比如事务 ID、事务状态等。

+ 方法三：查询 `performance_schema.metadata_locks` 表
`performance_schema.metadata_locks` 表记录了当前的元数据锁信息，你可以通过查询该表来判断是否存在全局锁。
```sql
SELECT * FROM performance_schema.metadata_locks
WHERE OBJECT_TYPE = 'GLOBAL';
```
若查询结果不为空，就表示当前存在全局锁。

**下面是一个综合示例，结合了上述三种方法：**

```sql
-- 方法一：查看 InnoDB 状态
SHOW ENGINE INNODB STATUS;

-- 方法二：查询 InnoDB 事务表
SELECT * FROM information_schema.INNODB_TRX;

-- 方法三：查询元数据锁表
SELECT * FROM performance_schema.metadata_locks
WHERE OBJECT_TYPE = 'GLOBAL';
```

通过执行上述 SQL 语句，你可以从多个角度判断 MySQL 当前是否处于全局锁状态。

## 3. 表锁

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

### 3.1. 表锁

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

举个例子, 如果在某个线程 A 中执行 lock tables t1 read, t2 write; 这个语句，则其他线程写 t1、读写 t2 的语句都会被阻塞。同时，线程 A 在执行 unlock tables 之前，也只能执行读 t1、读写 t2 的操作。连写 t1 都不允许，自然也不能访问其他表。

在还没有出现更细粒度的锁的时候，表锁是最常用的处理并发的方式。而对于 InnoDB 这种支持行锁的引擎，一般不使用 lock tables 命令来控制并发，毕竟锁住整个表的影响面还是太大。

### 3.2. 元数据锁

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

因此，在 MySQL 5.5 版本中引入了 MDL，当对一个表做增删改查操作的时候，加 MDL读锁；当要对表做结构变更操作的时候，加 MDL 写锁。

+ 读锁之间不互斥，因此你可以有多个线程同时对一张表增删改查。

+ 读写锁之间、写锁之间是互斥的，用来保证变更表结构操作的安全性。因此，如果有两个线程要同时给一个表加字段，其中一个要等另一个执行完才能开始执行。

虽然 MDL 锁是系统默认会加的，但却是你不能忽略的一个机制。比如下面这个例子，我经常看到有人掉到这个坑里：给一个小表加个字段，导致整个库挂了。

你肯定知道，给一个表加字段，或者修改字段，或者加索引，需要扫描全表的数据。在对大表操作的时候，你肯定会特别小心，以免对线上服务造成影响。而实际上，即使是小表，操作不慎也会出问题。我们来看一下下面的操作序列，假设表 t 是一个小表。

> 备注：这里的实验环境是 MySQL 5.6。

![](/UploadFlie/MarkdownImg/20250407/202504070559570473.png)

我们可以看到 session A 先启动，这时候会对表 t 加一个 MDL 读锁。由于 session B 需要的也是 MDL 读锁，因此可以正常执行。

之后 session C 会被 blocked，是因为 session A 的 MDL 读锁还没有释放，而 sessionC 需要 MDL 写锁，因此只能被阻塞。

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

如果某个表上的查询语句频繁，而且客户端有重试机制，也就是说超时后会再起一个新session 再请求的话，这个库的线程很快就会爆满。

你现在应该知道了，事务中的 MDL 锁，在语句执行开始时申请，但是语句结束后并不会马上释放，而会等到整个事务提交后再释放。

**基于上面的分析，我们来讨论一个问题，如何安全地给小表加字段？**

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

但考虑一下这个场景。如果你要变更的表是一个热点表，虽然数据量不大，但是上面的请求很频繁，而你不得不加个字段，你该怎么做呢？

这时候 kill 可能未必管用，因为新的请求马上就来了。比较理想的机制是，在 alter table语句里面设定等待时间，如果在这个指定的等待时间里面能够拿到 MDL 写锁最好，拿不到也不要阻塞后面的业务语句，先放弃。之后开发人员或者 DBA 再通过重试命令重复这个过程。

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

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

在 MySQL 里，MDL是一种用于保证数据一致性与并发操作正确性的锁机制，它主要作用于数据库对象的元数据，像表结构这类信息。下面从多方面为你详细解读元数据锁：

+ **作用：**
元数据锁的核心作用是在并发环境下维护数据库对象元数据的一致性。当有事务对表进行读写操作时，需要保证表结构不会被其他事务在操作期间修改，不然就可能引发数据不一致或者查询结果出错的问题。元数据锁能避免这种情况的发生，保证操作的安全性和一致性。

+ **触发场景：**
  - **查询操作**：当执行 `SELECT` 语句时，会自动获取表的共享元数据锁，这样可以确保在查询期间表结构不会被修改。
  - **数据修改操作**：执行 `INSERT`、`UPDATE`、`DELETE` 等语句时，也会获取表的共享元数据锁，防止在数据修改过程中表结构发生变化。
  - **表结构修改操作**：执行 `ALTER TABLE` 等修改表结构的语句时，会获取表的排他元数据锁，从而阻止其他事务对该表进行读写操作，保证表结构修改的原子性。

+ **锁类型**
  - **共享:锁（Shared MDL）**：也叫读锁，多个事务可以同时持有同一个表的共享元数据锁。例如多个事务同时对同一个表进行查询操作，它们都可以获取共享元数据锁，并发地读取表数据。
  - **排他锁（Exclusive MDL）**：也叫写锁，同一时间只允许一个事务持有表的排他元数据锁。当一个事务持有排他元数据锁时，其他事务无法获取该表的任何类型的元数据锁，这样就能保证在修改表结构时不会有其他事务对表进行读写操作。

+ **锁的生命周期:**
元数据锁的生命周期和事务紧密相关，通常在事务开始时获取，在事务结束（提交或者回滚）时释放。这意味着在一个事务执行期间，它持有的元数据锁会一直存在，从而保证在整个事务过程中表结构的一致性。

+ **影响:**
- **正面影响**：元数据锁能保证数据的一致性和并发操作的正确性，防止因为表结构的并发修改而导致的数据错误。
- **负面影响**：如果使用不当，元数据锁可能会引发锁等待和死锁问题。例如，一个长事务持有表的共享元数据锁，此时另一个事务需要对该表进行结构修改，就需要等待长事务结束释放锁，这可能会导致性能问题。

**示例：以下是一个简单的示例，展示元数据锁的作用：**

```sql
-- 会话 1：开启一个事务并查询表
START TRANSACTION;
SELECT * FROM your_table;

-- 此时会话 1 持有表的共享元数据锁

-- 会话 2：尝试修改表结构
ALTER TABLE your_table ADD COLUMN new_column INT;

-- 会话 2 会被阻塞，直到会话 1 的事务结束释放元数据锁
```

在这个示例中，会话 1 开启事务并查询表，获取了表的共享元数据锁。会话 2 尝试修改表结构，需要获取排他元数据锁，但由于会话 1 已经持有共享元数据锁，所以会话 2 会被阻塞，直到会话 1 的事务结束释放锁。

综上所述，元数据锁是 MySQL 中非常重要的一种锁机制，它能保证数据的一致性和并发操作的正确性，但在使用时需要注意避免锁等待和死锁问题。

***

在 MySQL 中，你可以使用以下 SQL 语句来对表加锁和解锁，表锁分为读锁和写锁，下面分别介绍。

### 3.3. 加读锁

读锁允许其他会话也对该表进行读操作，但不允许`写操作`。可以使用以下 SQL 语句对表加读锁：

```sql
-- 为单个表加读锁
LOCK TABLES your_table_name READ;
-- 若要为多个表加读锁，可以这样写
LOCK TABLES table1 READ, table2 READ;
```

### 3.4. 加写锁

写锁会阻止其他会话对该表进行`读和写操作`，直到锁被释放。使用以下 SQL 语句对表加写锁：

```sql
-- 为单个表加写锁
LOCK TABLES your_table_name WRITE;
-- 为多个表加写锁
LOCK TABLES table1 WRITE, table2 WRITE;
```

### 3.5. 解除表锁

不管是读锁还是写锁，都可以使用以下 SQL 语句来解除表锁：

```sql
UNLOCK TABLES;
```

***

**下面是一个完整的示例，展示了如何对表加读锁、进行操作，然后解除锁：**

```sql
-- 对表加读锁
LOCK TABLES your_table_name READ;

-- 执行读操作
SELECT * FROM your_table_name;

-- 解除表锁
UNLOCK TABLES;
```

**以下是加写锁并操作的示例：**

```sql
-- 对表加写锁
LOCK TABLES your_table_name WRITE;

-- 执行写操作，如插入数据
INSERT INTO your_table_name (column1, column2) VALUES ('value1', 'value2');

-- 解除表锁
UNLOCK TABLES;
```

需要注意的是，使用表锁时要谨慎，因为它会影响数据库的并发性能，尤其是在高并发场景下。尽量减少持有锁的时间，以避免阻塞其他会话的操作.

### 3.6. 表锁状态查看

在 MySQL 中，可通过不同的方式来查询当前是否处于表锁状态，下面为你详细介绍：

+ 查询 `performance_schema.metadata_locks` 表
`performance_schema.metadata_locks` 表记录了当前的元数据锁信息，你可以利用它查询表锁状态。
```sql
SELECT
    OBJECT_SCHEMA,
    OBJECT_NAME,
    LOCK_TYPE,
    LOCK_DURATION,
    LOCK_STATUS
FROM
    performance_schema.metadata_locks
WHERE
    OBJECT_TYPE = 'TABLE';
```
**解释**：
 - `OBJECT_SCHEMA`：表示被锁定表所在的数据库名。
 - `OBJECT_NAME`：是被锁定的表名。
 - `LOCK_TYPE`：锁的类型，如 `SHARED`（共享锁，即读锁）、`EXCLUSIVE`（排他锁，即写锁）等。
 - `LOCK_DURATION`：锁的持续时间，例如 `TRANSACTION` 表示在事务期间持有锁。
 - `LOCK_STATUS`：锁的状态，`GRANTED` 表示锁已被授予，`PENDING` 表示正在等待锁。
若查询结果不为空，就表明存在表锁。

+ 查看 `SHOW ENGINE INNODB STATUS` 的输出
此命令能提供 InnoDB 存储引擎的详细状态信息，包含锁的相关内容。
```sql
SHOW ENGINE INNODB STATUS;
```
执行该命令后，在输出结果中查找与锁相关的部分，尤其是事务锁的信息。若有表锁，通常会有相应的锁等待或者持有信息。不过这种方式输出内容较多，需仔细查找所需信息。

+ 查询 `information_schema.INNODB_LOCKS` 和 `information_schema.INNODB_LOCK_WAITS` 表（适用于 InnoDB 引擎）
 - `information_schema.INNODB_LOCKS`：存储当前 InnoDB 事务持有的锁信息。
```sql
SELECT * FROM information_schema.INNODB_LOCKS;
```
若查询结果不为空，则说明存在 InnoDB 表锁。
 - `information_schema.INNODB_LOCK_WAITS`：记录了正在等待锁的事务信息。
```sql
SELECT * FROM information_schema.INNODB_LOCK_WAITS;
```
若有结果，意味着有事务正在等待表锁。

**下面是一个综合示例，结合上述方法判断是否存在表锁：**

```sql
-- 方法一：查询 performance_schema.metadata_locks 表
SELECT
    OBJECT_SCHEMA,
    OBJECT_NAME,
    LOCK_TYPE,
    LOCK_DURATION,
    LOCK_STATUS
FROM
    performance_schema.metadata_locks
WHERE
    OBJECT_TYPE = 'TABLE';

-- 方法二：查看 InnoDB 状态
SHOW ENGINE INNODB STATUS;

-- 方法三：查询 InnoDB 锁表
SELECT * FROM information_schema.INNODB_LOCKS;
SELECT * FROM information_schema.INNODB_LOCK_WAITS;
```
通过执行上述 SQL 语句，你可以从多个方面判断 MySQL 当前是否存在表锁状态。

## 4. 行锁

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

我们今天就主要来聊聊 InnoDB 的行锁，以及如何通过减少锁冲突来提升业务并发度。顾名思义，行锁就是针对数据表中行记录的锁。这很好理解，比如事务 A 更新了一行，而这时候事务 B 也要更新同一行，则必须等事务 A 的操作完成后才能进行更新。

当然，数据库中还有一些没那么一目了然的概念和设计，这些概念如果理解和使用不当，容易导致程序出现非预期行为，比如两阶段锁。

### 4.1. 从两阶段锁说起

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

![](/UploadFlie/MarkdownImg/20250408/202504080207036294.png)

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

知道了这个答案，你一定知道了事务 A 持有的两个记录的行锁，都是在 commit 的时候才释放的。

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

知道了这个设定，对我们使用事务有什么帮助呢？那就是，<mark>如果你的事务中需要锁多个行，要把最可能造成锁冲突、最可能影响并发度的锁尽量往后放。我给你举个例子。</mark>

假设你负责实现一个电影票在线交易业务，顾客 A 要在影院 B 购买电影票。我们简化一点，这个业务需要涉及到以下操作：

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

也就是说，要完成这个交易，我们需要 update 两条记录，并 insert 一条记录。当然，为了保证交易的原子性，我们要把这三个操作放在一个事务中。那么，你会怎样安排这三个语句在事务中的顺序呢？

试想如果同时有另外一个顾客 C 要在影院 B 买票，那么这两个事务冲突的部分就是语句 2了。因为它们要更新同一个影院账户的余额，需要修改同一行数据。

根据两阶段锁协议，不论你怎样安排语句顺序，所有的操作需要的行锁都是在事务提交的时候才释放的。所以，如果你把语句 2 安排在最后，比如按照 3、1、2 这样的顺序，那么影院账户余额这一行的锁时间就最少。这就最大程度地减少了事务之间的锁等待，提升了并发度。

好了，现在由于你的正确设计，影院余额这一行的行锁在一个事务中不会停留很长时间。但是，这并没有完全解决你的困扰。

如果这个影院做活动，可以低价预售一年内所有的电影票，而且这个活动只做一天。于是在活动时间开始的时候，你的 MySQL 就挂了。你登上服务器一看，CPU 消耗接近100%，但整个数据库每秒就执行不到 100 个事务。这是什么原因呢？

这里，我就要说到死锁和死锁检测了。

### 4.2. 死锁和死锁检测

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

![](/UploadFlie/MarkdownImg/20250408/202504080242169915.png)

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

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

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

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

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

你可以想象一下这个过程：每当一个事务被锁的时候，就要看看它所依赖的线程有没有被别人锁住，如此循环，最后判断是否出现了循环等待，也就是死锁。

那如果是我们上面说到的所有事务都要更新同一行的场景呢？

每个新来的被堵住的线程，都要判断会不会由于自己的加入导致了死锁，这是一个时间复杂度是 O(n) 的操作。假设有 1000 个并发线程要同时更新同一行，那么死锁检测操作就是 100 万这个量级的。虽然最终检测的结果是没有死锁，但是这期间要消耗大量的 CPU资源。因此，你就会看到 CPU 利用率很高，但是每秒却执行不了几个事务。

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

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

**另一个思路是控制并发度。**根据上面的分析，你会发现如果并发能够控制住，比如同一行同时最多只有 10 个线程在更新，那么死锁检测的成本很低，就不会出现这个问题。一个直接的想法就是，在客户端做并发控制。但是，你会很快发现这个方法不太可行，因为客户端很多。我见过一个应用，有 600 个客户端，这样即使每个客户端控制到只有 5 个并发线程，汇总到数据库服务端以后，峰值并发数也可能要达到 3000。

因此，这个并发控制要做在数据库服务端。如果你有中间件，可以考虑在中间件实现；如果你的团队有能修改 MySQL 源码的人，也可以做在 MySQL 里面。基本思路就是，对于相同行的更新，在进入引擎之前排队。这样在 InnoDB 内部就不会有大量的死锁检测工作了。

可能你会问，**如果团队里暂时没有数据库方面的专家，不能实现这样的方案，能不能从设计上优化这个问题呢？**

你可以考虑通过将一行改成逻辑上的多行来减少锁冲突。还是以影院账户为例，可以考虑放在多条记录上，比如 10 个记录，影院的账户总额等于这 10 个记录的值的总和。这样每次要给影院账户加金额的时候，随机选其中一条记录来加。这样每次冲突概率变成原来的1/10，可以减少锁等待个数，也就减少了死锁检测的 CPU 消耗。

这个方案看上去是无损的，但其实这类方案需要根据业务逻辑做详细设计。如果账户余额可能会减少，比如退票逻辑，那么这时候就需要考虑当一部分行记录变成 0 的时候，代码要有特殊处理。

> **小结：**
今天，我和你介绍了 MySQL 的行锁，涉及了两阶段锁协议、死锁和死锁检测这两大部分内容。

> 其中，我以两阶段协议为起点，和你一起讨论了在开发的时候如何安排正确的事务语句。这里的原则 / 我给你的建议是：如果你的事务中需要锁多个行，要把最可能造成锁冲突、最可能影响并发度的锁的申请时机尽量往后放。

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

***

在 MySQL 里，行锁是自动释放的，但在某些场景下你或许想主动释放它，同时也想查询哪个表存在行锁。下面分别对这两个需求展开介绍。

### 4.3. 主动释放行锁

在 MySQL 里，行锁一般是在事务结束时自动释放的。事务结束包含两种情形：提交（`COMMIT`）和回滚（`ROLLBACK`）。下面是具体示例：

```sql
-- 开启事务
START TRANSACTION;

-- 执行可能加行锁的操作，例如更新操作
UPDATE your_table
SET column1 = 'new_value'
WHERE primary_key = 1;

-- 主动释放行锁，提交事务
COMMIT;

-- 或者，如果你想放弃本次操作，回滚事务也能释放行锁
-- ROLLBACK;
```

在这个示例中，当执行 `COMMIT` 或者 `ROLLBACK` 语句时，事务结束，行锁会被释放。

### 4.4. 查询哪个表出现了行锁情况

你可以通过查询 `information_schema` 数据库中的相关表来查看哪些表出现了行锁情况。下面是一个示例查询：

```sql
-- 查询当前正在被锁定的表
SELECT
    r.trx_id AS locked_by_transaction,
    r.trx_mysql_thread_id AS thread_id,
    r.trx_query AS query,
    l.lock_table AS locked_table,
    l.lock_index AS locked_index,
    l.lock_type AS lock_type,
    l.lock_mode AS lock_mode
FROM information_schema.innodb_locks l
JOIN information_schema.innodb_trx r ON l.lock_trx_id = r.trx_id;
```

这个查询会把当前正在被锁定的表以及相关的事务信息、锁类型等显示出来。

需要注意的是，这些查询只适用于 InnoDB 存储引擎，因为只有 InnoDB 支持行级锁。如果你使用的是其他存储引擎，这些查询可能不会得到预期结果。