实战 MySQL 锁等待问题的定位与排查

1. 引言

在 MySQL 的实际使用中，常常会遇到一条 SQL 执行非常慢的情况，此前我们总结了一系列博客来排查相关的问题：

1.1. 通过 Explain 语句查看 SQL 执行计划

通常 SQL 执行耗时异常是由于索引使用不合理或创建了临时表等操作，上一篇文章中，我们介绍了 MySQL 执行计划： 详解 MySQL 执行计划 — explain

通过 Explain 语句可以详细分析具体的原因。

1.2. 通过 SQL 各状态的执行耗时具体分析背后的原因

但有时，耗时过多也可能是由于磁盘 IO 等资源问题，如果 Explain 无法一目了然的分析出原因，此时我们就要剖析 SQL 执行中具体的每一个步骤，查看 SQL 执行的各状态耗时，并具体分析： SQL 某状态耗时过多的优化

上面这篇文章中，同时还介绍了临时表创建的条件，创建临时表是我们经常会遇到大量占用磁盘 IO 造成查询耗时的一个主要原因。

1.3. 锁等待

然而，此前的文章中详细介绍了 MySQL 的锁机制： MySQL 锁机制（上） — 全局锁与表级锁 MySQL 锁机制（下） — 细说 InnoDB 行锁（记录锁、间隙锁与临键锁）

在实际的使用中，一个简单地 SQL 迟迟没有返回，多半就是陷入了锁等待，那么，上面介绍了这么多种锁的情况，我们应该如何去排查究竟我们正在执行的 SQL 在等待哪一种锁呢？ 别急，本文我们就来详细介绍。

2. 等待 MDL 锁的排查

上面提到，排查 SQL 执行超时的一个重要手段是通过 show processlist 命令查看 SQL 执行各状态的耗时情况，但这是通过 SQL 执行完成后的 queryID 来进行查询的。 在 SQL 执行过程中，也可以通过 show processlist 命令查看当前 SQL 的执行状态：

如上图所示，可以看到，我们正在执行的 SQL 状态是 Waiting for table metadata lock，这就说明他正在等待 MDL 锁，可是如何找到是谁持有 MDL 锁的呢？ 通过 performance_schema 和 sys 两个数据库中的数据我们可以看到数据库执行过程中的各项情况，包括 MDL 锁的情况等。

2.1. performance_schema

performance_schema 是 mysql 提供的一种在数据库运行时监控 server 各项执行状态的机制，监控数据都保存在 performance_schema 数据库中，而该数据库使用的存储引擎为 performance_schema，因此 performance_schema 同时具有以下三种含义：

1. 一种 MySQL 自带的对 server 运行状态的监控机制
2. MySQL 中的一个数据库名
3. 一种存储引擎

2.1.1. performance_schema 机制

通过 MySQL 启动前在配置文件中配置 performance_schema=on 开启，相比于未开启会有 10% 左右的性能损失，MySQL 5.7 以后是默认开启的。 可以通过 MySQL 的 performance_schema 全局变量来查看是否已开启：

performance_schema 机制通过监听 server 事件实现对 server 的监控，事件中包含了函数调用、操作系统的等待、SQL语句执行的阶段（如sql语句执行过程中的parsing 或 sorting阶段）或者整个SQL语句与SQL语句集合等发生时相关存储引擎对磁盘文件、表I/O、表锁等资源的同步调用信息以及事件发生的耗时、调用次数等信息。

2.1.2. performance_schema 存储引擎与数据库

performance_schema 存储引擎是一个纯内存的存储引擎，因此一旦服务器重启，其中所有数据都会丢失，而同时，所有对 performance_schema 数据库中表的操作都不会写入 binlog，因此也不会在集群中进行同步。 performance_schema 数据库中大部分表可以直接通过 select 语句进行查询，以便直接获取对 server 的监控数据，以 setup_ 开头的一系列数据表用于存储数据收集时的配置信息，可以通过修改表中的数据项来实现对配置的调整，这些修改都是立即生效的。 performance_schema 共有 87 个表，主要包含：

1. MySQL 执行的 SQL 语句记录
2. 等待事件记录表
3. 阶段事件记录表
4. 事务事件记录表
5. 监视文件系统调用记录表
6. 监视内存使用记录表
7. 实时配置表

2.2. sys 数据库

performance_schema 库中拥有大量的表以及复杂的关联关系，想要掌握其复杂的信息检索是非常难的，因此，MySQL 提供了 sys 库，将 performance_schema 数据库中的数据组合成视图，便于人工检索和使用，主要分为：

1. sys_config — 配置表，用于存储配置 sys 数据库的配置参数
2. host — 以 IP 分组相关的统计信息
3. innodb — innodb buffer 相关信息
4. io — 数据内不同维度展的IO相关的信息
5. memory — 以 IP，连接，用户，分配的类型分组及总的占用显示内存的使用
6. metrics — DB 的内部的统计值
7. processlist — 线程相关的信息(包含内部线程及用户连接）
8. ps_ — 没有工具统计的一些变量（没看出来存在的价值）
9. schema — 表结构相关的信息，例如— 自增，索引， 表里的每个字段类型，等待的锁等等
10. session — 用户连接相关的信息
11. statement — 基于语句的统计信息（重店）
12. statements_ — 出错的语句，进行全表扫描， 运行时间超长，排序相等（重点）
13. user_ — 和host_开头的相似，只是以用户分组统计
14. wait — 等待事件
15. waits — 以IP，用户分组统计出来的一些延迟事件，有一定的参考价值
16. x$开头 — 适合工具采集数据的原始类数据

除 sys_config 外其他均为数据视图，我们可以方便的查询数据库当前的各项指标，当然也包括各个表的 MDL 锁使用情况。

2.3. 排查 MDL 锁的占用情况

在 sys 数据库的 schema_table_lock_waits 数据视图中就存有 MDL 锁的信息（5.7.9 版本加入）：

接着我们可以通过 kill pid 命令来杀掉对应的执行，也可以通过下面语句查询对应 sql 的详细信息：

select * from information_schema.`PROCESSLIST` where ID = 4;

3. 等待 flush 操作的排查

我们此前介绍过通过 flush 操作加表锁或全局锁：

flush tables test with read lock;

这个操作首先会关闭所有需要被锁的表，这通常是一个耗时非常短的操作，在此之中，对表的任何读写都会被阻塞。 如果此时正在进行着一个对该表的慢查询，那么 flush 操作就会被阻塞，此后所有读写操作也都会被阻塞。 同样，通过 show processlist 可以查看相应的情况：

我们找到那个罪魁祸首的慢查询，kill 掉即可（图中显示的是 id 为 14 的 select sleep(100) from test）

4. 等待行锁的排查

通过 show processlist 看到语句既不是在等待 MDL 锁，也不是在等待 flush，而是陷入 statistics 状态，则说明在等待行锁：

那么，我们如何找到持有行锁的是哪一条语句呢？ sys.innodb_lock_waits 表中可以查到行锁的各项信息：

通过查询结果，我们可以看到，我们需要查询的 id 为 10 的记录由被 id 为 142349 的事务持有写锁锁住了， 通过 infomation_schema.INNODB_TRX 表我们可以查看事务的相关信息：

结果中显示了事务的线程 id：trx_mysql_thread_id，执行 kill 线程id 即可杀死对应的事务。

5. 参考资料

https://dev.mysql.com/doc/refman/5.7/en/sys-schema-reference.html。 https://dev.mysql.com/doc/refman/8.0/en/performance-schema.html。