日期：
| 后台-插件-广告管理-首页/栏目/内容广告位一（PC） |
| 后台-插件-广告管理-首页/栏目/内容广告位一（手机） |
在上篇文章《MySQL表结构变更，不可不知的Metadata Lock》中，我们介绍了MDL引入的背景，及基本概念，从“道”的层面知道了什么是MDL。下面就从“术”的层面看看如何定位MDL的相关问题。
在MySQL 5.7中，针对MDL，引入了一张新表performance_schema.metadata_locks，该表可对外展示MDL的相关信息，包括其作用对象，类型及持有等待情况。
开启MDL的instrument
但是相关instrument并没有开启（MySQL 8.0是默认开启的），其可通过如下两种方式开启，
临时生效
修改performance_schema.setup_instrume nts表，但实例重启后，又会恢复为默认值。
UPDATE performance_schema.setup_instruments SET ENABLED = 'YES', TIMED = 'YES' WHERE NAME = 'wait/lock/metadata/sql/mdl';
永久生效
在配置文件中设置
[mysqld] performance-schema-instrument='wait/lock/metadata/sql/mdl=ON'
测试场景
下面结合一个简单的Demo，来看看在MySQL 5.7中如何定位DDL操作的阻塞问题。
session1> begin; Query OK, 0 rows affected (0.00 sec) session1> delete from slowtech.t1 where id=2; Query OK, 1 row affected (0.00 sec) session1> select * from slowtech.t1; +------+------+ | id | name | +------+------+ | 1 | a | +------+------+ 1 row in set (0.00 sec) session1> update slowtech.t1 set name='c' where id=1; Query OK, 1 row affected (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 0 session2> alter table slowtech.t1 add c1 int; ##被阻塞 session3> show processlist; +----+------+-----------+------+---------+------+---------------------------------+------------------------------------+ | Id | User | Host | db | Command | Time | State | Info | +----+------+-----------+------+---------+------+---------------------------------+------------------------------------+ | 2 | root | localhost | NULL | Sleep | 51 | | NULL | | 3 | root | localhost | NULL | Query | 0 | starting | show processlist | | 4 | root | localhost | NULL | Query | 9 | Waiting for table metadata lock | alter table slowtech.t1 add c1 int | +----+------+-----------+------+---------+------+---------------------------------+------------------------------------+ 3 rows in set (0.00 sec) session3> select object_type,object_schema,object_name,lock_type,lock_duration,lock_status,owner_thread_id from performance_schema.metadata_locks; +-------------+--------------------+----------------+---------------------+---------------+-------------+-----------------+ | object_type | object_schema | object_name | lock_type | lock_duration | lock_status | owner_thread_id | +-------------+--------------------+----------------+---------------------+---------------+-------------+-----------------+ | TABLE | slowtech | t1 | SHARED_WRITE | TRANSACTION | GRANTED | 27 | | GLOBAL | NULL | NULL | INTENTION_EXCLUSIVE | STATEMENT | GRANTED | 29 | | SCHEMA | slowtech | NULL | INTENTION_EXCLUSIVE | TRANSACTION | GRANTED | 29 | | TABLE | slowtech | t1 | SHARED_UPGRADABLE | TRANSACTION | GRANTED | 29 | | TABLE | slowtech | t1 | EXCLUSIVE | TRANSACTION | PENDING | 29 | | TABLE | performance_schema | metadata_locks | SHARED_READ | TRANSACTION | GRANTED | 28 | +-------------+--------------------+----------------+---------------------+---------------+-------------+-----------------+ 6 rows in set (0.00 sec)
这里，重点关注lock_status，"PENDING"代表线程在等待MDL，而"GRANTED"则代表线程持有MDL。
如何找出引起阻塞的会话
结合owner_thread_id，可以可到，是29号线程在等待27号线程的MDL，此时，可kill掉52号线程。
但需要注意的是，owner_thread_id给出的只是线程ID，并不是show processlist中的ID。如果要查找线程对应的processlist id，需查询performance_schema.threads表。
session3> select * from performance_schema.threads where thread_id in (27,29)\G
*************************** 1. row ***************************
THREAD_ID: 27
NAME: thread/sql/one_connection
TYPE: FOREGROUND
PROCESSLIST_ID: 2
PROCESSLIST_USER: root
PROCESSLIST_HOST: localhost
PROCESSLIST_DB: NULL
PROCESSLIST_COMMAND: Sleep
PROCESSLIST_TIME: 214
PROCESSLIST_STATE: NULL
PROCESSLIST_INFO: NULL
PARENT_THREAD_ID: 1
ROLE: NULL
INSTRUMENTED: YES
HISTORY: YES
CONNECTION_TYPE: Socket
THREAD_OS_ID: 9800
*************************** 2. row ***************************
THREAD_ID: 29
NAME: thread/sql/one_connection
TYPE: FOREGROUND
PROCESSLIST_ID: 4
PROCESSLIST_USER: root
PROCESSLIST_HOST: localhost
PROCESSLIST_DB: NULL
PROCESSLIST_COMMAND: Query
PROCESSLIST_TIME: 172
PROCESSLIST_STATE: Waiting for table metadata lock
PROCESSLIST_INFO: alter table slowtech.t1 add c1 int
PARENT_THREAD_ID: 1
ROLE: NULL
INSTRUMENTED: YES
HISTORY: YES
CONNECTION_TYPE: Socket
THREAD_OS_ID: 9907
2 rows in set (0.00 sec)[!--empirenews.page--]
将这两张表结合，借鉴sys.innodb_lock _waits的输出，实际上我们也可以直观地呈现MDL的等待关系。
SELECT
a.OBJECT_SCHEMA AS locked_schema,
a.OBJECT_NAME AS locked_table,
"Metadata Lock" AS locked_type,
c.PROCESSLIST_ID AS waiting_processlist_id,
c.PROCESSLIST_TIME AS waiting_age,
c.PROCESSLIST_INFO AS waiting_query,
c.PROCESSLIST_STATE AS waiting_state,
d.PROCESSLIST_ID AS blocking_processlist_id,
d.PROCESSLIST_TIME AS blocking_age,
d.PROCESSLIST_INFO AS blocking_query,
concat('KILL ', d.PROCESSLIST_ID) AS sql_kill_blocking_connection
FROM
performance_schema.metadata_locks a
JOIN performance_schema.metadata_locks b alt="MySQL 5.7中如何定位DDL被阻塞的问题 " src="https:///d/file/20240124/cf5dc85252f004bd2bdc9dbe07a4edf3.png" />
三者的表结构完全一致，其中，events_statements_history又包含了events_statements_current的操作，所以我们这里会使用events_statements_history。
终极SQL如下，
SELECT
locked_schema,
locked_table,
locked_type,
waiting_processlist_id,
waiting_age,
waiting_query,
waiting_state,
blocking_processlist_id,
blocking_age,
substring_index(sql_text,"transaction_begin;" ,-1) AS blocking_query,
sql_kill_blocking_connection
FROM
(
SELECT
b.OWNER_THREAD_ID AS granted_thread_id,
a.OBJECT_SCHEMA AS locked_schema,
a.OBJECT_NAME AS locked_table,
"Metadata Lock" AS locked_type,
c.PROCESSLIST_ID AS waiting_processlist_id,
c.PROCESSLIST_TIME AS waiting_age,
c.PROCESSLIST_INFO AS waiting_query,
c.PROCESSLIST_STATE AS waiting_state,
d.PROCESSLIST_ID AS blocking_processlist_id,
d.PROCESSLIST_TIME AS blocking_age,
d.PROCESSLIST_INFO AS blocking_query,
concat('KILL ', d.PROCESSLIST_ID) AS sql_kill_blocking_connection
FROM
performance_schema.metadata_locks a
JOIN performance_schema.metadata_locks b ON a.OBJECT_SCHEMA = b.OBJECT_SCHEMA
AND a.OBJECT_NAME = b.OBJECT_NAME
AND a.lock_status = 'PENDING'
AND b.lock_status = 'GRANTED'
AND a.OWNER_THREAD_ID <> b.OWNER_THREAD_ID
AND a.lock_type = 'EXCLUSIVE'
JOIN performance_schema.threads c ON a.OWNER_THREAD_ID = c.THREAD_ID
JOIN performance_schema.threads d ON b.OWNER_THREAD_ID = d.THREAD_ID
) t1,
(
SELECT
thread_id,
group_concat( CASE WHEN EVENT_NAME = 'statement/sql/begin' THEN "transaction_begin" ELSE sql_text END ORDER BY event_id SEPARATOR ";" ) AS sql_text
FROM
performance_schema.events_statements_history
GROUP BY thread_id
) t2
WHERE
t1.granted_thread_id = t2.thread_id \G
*************************** 1. row ***************************
locked_schema: slowtech
locked_table: t1
locked_type: Metadata Lock
waiting_processlist_id: 4
waiting_age: 294
waiting_query: alter table slowtech.t1 add c1 int
waiting_state: Waiting for table metadata lock
blocking_processlist_id: 2
blocking_age: 336
blocking_query: delete from slowtech.t1 where id=2;select * from slowtech.t1;update slowtech.t1 set name='c' where id=1
sql_kill_blocking_connection: KILL 2
1 row in set, 1 warning (0.00 sec)
从上面的输出可以看到，blocking_query中包含了会话1中当前事务的所有操作，按执行的先后顺序输出。
需要注意的是，默认情况下，events_statements_history只会保留每个线程最近的10个操作，如果事务中进行的操作较多，实际上也是没办法抓全的。
Anyway, it is better than nothing！