问题描述
使用 show full processlist 查看当前数据库正在运行的 SQL 语句，我们可能会发现 State 列为 Waiting for table metadata lock，我们该如何排查此类问题？
问题复现
- 在会话 1 中显式开启一个事务，执行一个 update 操作，不进行提交
mysql> begin;
Query OK, 0 rows affected (0.00 sec)
mysql> update t1 set name='liwangz' where id=1;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
```
`
2. 在会话 2 中对表执行 DDL 操作：我们会发现操作被阻塞
````undefined
mysql> alter table t1 add index i_c(id);
```
`
3. 使用 `show full processlist` 命令进行排查，会发现 State 字段出现 **Waiting for table metadata lock**
# 解决方案
### 1. 前提条件
* 排查 MDL 锁依赖于 performance_schema，请提前确认 performance_schema 已经开启
* MDL 锁记录对应的 instruments 为**wait/lock/metadata/sql/mdl**，需要提前开启
### 2. 查看 metadata_lock 表获取锁信息
````undefined
mysql> select * from performance_schema.metadata_locks where OWNER_THREAD_ID!=sys.ps_thread_id(connection_id())\G;
*************************** 1. row ***************************
OBJECT_TYPE: TABLE
OBJECT_SCHEMA: rudonx
OBJECT_NAME: t1
OBJECT_INSTANCE_BEGIN: 139681262016240
LOCK_TYPE: SHARED_WRITE
LOCK_DURATION: TRANSACTION
LOCK_STATUS: GRANTED
SOURCE: sql_parse.cc:5993
OWNER_THREAD_ID: 48
OWNER_EVENT_ID: 282
*************************** 2. row ***************************
OBJECT_TYPE: GLOBAL
OBJECT_SCHEMA: NULL
OBJECT_NAME: NULL
OBJECT_INSTANCE_BEGIN: 139681127989264
LOCK_TYPE: INTENTION_EXCLUSIVE
LOCK_DURATION: STATEMENT
LOCK_STATUS: GRANTED
SOURCE: sql_base.cc:5497
OWNER_THREAD_ID: 54
OWNER_EVENT_ID: 1064
*************************** 3. row ***************************
OBJECT_TYPE: SCHEMA
OBJECT_SCHEMA: rudonx
OBJECT_NAME: NULL
OBJECT_INSTANCE_BEGIN: 139681137621328
LOCK_TYPE: INTENTION_EXCLUSIVE
LOCK_DURATION: TRANSACTION
LOCK_STATUS: GRANTED
SOURCE: sql_base.cc:5482
OWNER_THREAD_ID: 54
OWNER_EVENT_ID: 1064
*************************** 4. row ***************************
OBJECT_TYPE: TABLE
OBJECT_SCHEMA: rudonx
OBJECT_NAME: t1
OBJECT_INSTANCE_BEGIN: 139681127961472
LOCK_TYPE: SHARED_UPGRADABLE
LOCK_DURATION: TRANSACTION
LOCK_STATUS: GRANTED
SOURCE: sql_parse.cc:5993
OWNER_THREAD_ID: 54
OWNER_EVENT_ID: 1066
*************************** 5. row ***************************
OBJECT_TYPE: TABLE
OBJECT_SCHEMA: rudonx
OBJECT_NAME: t1
OBJECT_INSTANCE_BEGIN: 139681137618432
LOCK_TYPE: EXCLUSIVE
LOCK_DURATION: TRANSACTION
LOCK_STATUS: PENDING
SOURCE: mdl.cc:3891
OWNER_THREAD_ID: 54
OWNER_EVENT_ID: 1137
5 rows in set (0.00 sec)
```
`
输出中共有 5 行数据，其中我们可以发现第一行为 t1 表的SHARED_WRITE 锁，编号为 48 的线程所持有(GRANTED)
后续 4 行中体现了 t1 表的 SHARED_UPGRADABLE、EXCLUSIVE 锁，其中 SHARED_UPGRADABLE 处于 GRANTED 状态，EXCLUSIVE 处于 PENDING 状态，编号为 54 的线程在等待 MDL 锁。
### 3. 查询 events_statements_current 获取查询语句
从 performance_schema.events_statements_current 表来查询某个线程最后一次执行的语句：
````undefined
mysql> select * from performance_schema.events_statements_current where thread_id=48\G;
*************************** 1. row ***************************
THREAD_ID: 48
EVENT_ID: 403
END_EVENT_ID: 457
EVENT_NAME: statement/sql/update
SOURCE: socket_connection.cc:101
TIMER_START: 8819577691629633000
TIMER_END: 8819577691947950000
TIMER_WAIT: 318317000
LOCK_TIME: 160000000
SQL_TEXT: update t1 set name='liwangz' where id=1
DIGEST: 3c26d58f8e27a87167a1237254ff60c5
DIGEST_TEXT: UPDATE `t1` SET NAME = ? WHERE `id` = ?
CURRENT_SCHEMA: rudonx
OBJECT_TYPE: NULL
OBJECT_SCHEMA: NULL
OBJECT_NAME: NULL
OBJECT_INSTANCE_BEGIN: NULL
MYSQL_ERRNO: 0
RETURNED_SQLSTATE: 00000
MESSAGE_TEXT: Rows matched: 1 Changed: 0 Warnings: 0
ERRORS: 0
WARNINGS: 0
ROWS_AFFECTED: 0
ROWS_SENT: 0
ROWS_EXAMINED: 1
CREATED_TMP_DISK_TABLES: 0
CREATED_TMP_TABLES: 0
SELECT_FULL_JOIN: 0
SELECT_FULL_RANGE_JOIN: 0
SELECT_RANGE: 0
SELECT_RANGE_CHECK: 0
SELECT_SCAN: 0
SORT_MERGE_PASSES: 0
SORT_RANGE: 0
SORT_ROWS: 0
SORT_SCAN: 0
NO_INDEX_USED: 0
NO_GOOD_INDEX_USED: 0
NESTING_EVENT_ID: NULL
NESTING_EVENT_TYPE: NULL
NESTING_EVENT_LEVEL: 0
1 row in set (0.00 sec)
ERROR:
No query specified
```
`
通过上述 SQL 回显的信息，我们可以看到相关 SQL 文本的信息。如果我们单纯去查询 `show full processlist`，不会显示 SQL 文本，会给定位问题带来一定的困扰。同样的去查询 `information_schema.innodb_trx\G`，我们只能看到有一个未提交的事务，也不会有文本信息。
````undefined
mysql> select * from information_schema.innodb_trx\G;
*************************** 1. row ***************************
trx_id: 36257
trx_state: RUNNING
trx_started: 2021-07-06 14:35:27
trx_requested_lock_id: NULL
trx_wait_started: NULL
trx_weight: 3
trx_mysql_thread_id: 22
trx_query: NULL
trx_operation_state: NULL
trx_tables_in_use: 0
trx_tables_locked: 1
trx_lock_structs: 2
trx_lock_memory_bytes: 1136
trx_rows_locked: 1
trx_rows_modified: 1
trx_concurrency_tickets: 0
trx_isolation_level: READ COMMITTED
trx_unique_checks: 1
trx_foreign_key_checks: 1
trx_last_foreign_key_error: NULL
trx_adaptive_hash_latched: 0
trx_adaptive_hash_timeout: 0
trx_is_read_only: 0
trx_autocommit_non_locking: 0
1 row in set (0.00 sec)
ERROR:
No query specified
```
`
### 4. kill 掉相关线程
通过 `performance_schema.events_statements_current` 表中的 THREAD_ID 字段值来获取 processlist ID，相关语句如下：
````undefined
mysql> select THREAD_ID,PROCESSLIST_ID from performance_schema.threads where THREAD_ID=48;
+-----------+----------------+
| THREAD_ID | PROCESSLIST_ID |
+-----------+----------------+
| 48 | 22 |
+-----------+----------------+
1 row in set (0.03 sec)
```
`
当和业务确认之后，可以执行如下操作来 kill 掉相关连接，将锁释放。
````undefined
mysql> kill 22;
Query OK, 0 rows affected (0.06 sec)
```
`
上述所有 SQL 语句的排查目的在于发现到底是什么语句持有 MDL 锁而引起阻塞，如果想快速恢复业务，可以使用如下方式：
````undefined
SELECT
concat( 'kill ', p1.id, ';' )
FROM
information_schema.PROCESSLIST p1,
(
SELECT
id,
time
FROM
information_schema.PROCESSLIST
WHERE
time = ( SELECT max( time ) FROM information_schema.PROCESSLIST WHERE state = 'Waiting for table metadata lock'
)) p2
WHERE
p1.time >= p2.time
AND p1.command IN ( 'Sleep', 'Query' )
AND p1.id NOT IN ( connection_id(), p2.id );
```
`
# 参考文档
[1] [https://dev.mysql.com/doc/refman/5.7/en/metadata-locking.html](https://dev.mysql.com/doc/refman/5.7/en/metadata-locking.html)
**如果您有其他问题，欢迎您联系火山引擎**[技术支持服务](https://console.volcengine.com/ticket/createTicketV2/)
