解决MySQL 5.7中定位DDL被阻塞的问题

在上篇文章《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)

将这两张表结合，借鉴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 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\g

*************************** 1. row ***************************
    locked_schema: slowtech
    locked_table: t1
     locked_type: metadata lock
  waiting_processlist_id: 4
     waiting_age: 259
    waiting_query: alter table slowtech.t1 add c1 int
    waiting_state: waiting for table metadata lock
  blocking_processlist_id: 2
    blocking_age: 301
    blocking_query: null
sql_kill_blocking_connection: kill 2
1 row in set (0.00 sec)

输出一目了然，ddl操作如果要获得mdl，执行kill 2即可。

官方的sys.schematablelock_waits

实际上，mysql 5.7在sys库中也集成了类似功能，同样的场景，其输出如下，

mysql> select * from sys.schema_table_lock_waits\g
*************************** 1. row ***************************
    object_schema: slowtech
     object_name: t1
   waiting_thread_id: 29
     waiting_pid: 4
    waiting_account: root@localhost
   waiting_lock_type: exclusive
  waiting_lock_duration: transaction
    waiting_query: alter table slowtech.t1 add c1 int
   waiting_query_secs: 446
 waiting_query_rows_affected: 0
 waiting_query_rows_examined: 0
   blocking_thread_id: 27
    blocking_pid: 2
   blocking_account: root@localhost
   blocking_lock_type: shared_read
  blocking_lock_duration: transaction
  sql_kill_blocking_query: kill query 2
sql_kill_blocking_connection: kill 2
*************************** 2. row ***************************
    object_schema: slowtech
     object_name: t1
   waiting_thread_id: 29
     waiting_pid: 4
    waiting_account: root@localhost
   waiting_lock_type: exclusive
  waiting_lock_duration: transaction
    waiting_query: alter table slowtech.t1 add c1 int
   waiting_query_secs: 446
 waiting_query_rows_affected: 0
 waiting_query_rows_examined: 0
   blocking_thread_id: 29
    blocking_pid: 4
   blocking_account: root@localhost
   blocking_lock_type: shared_upgradable
  blocking_lock_duration: transaction
  sql_kill_blocking_query: kill query 4
sql_kill_blocking_connection: kill 4
2 rows in set (0.00 sec)

具体分析下官方的输出，

只有一个alter table操作，却产生了两条记录，而且两条记录的kill对象竟然还不一样，对表结构不熟悉及不仔细看记录内容的话，难免会kill错对象。

不仅如此，如果有n个查询被ddl操作堵塞，则会产生n*2条记录。在阻塞操作较多的情况下，这n*2条记录完全是个噪音。

而之前的sql，无论有多少操作被阻塞，一个alter table操作，就只会输出一条记录。

如何查看阻塞会话已经执行过的操作

但上面这个sql也有遗憾，其blocking_query为null，而在会话1中，其明明已经执行了三个sql。

这个与performance_schema.threads（类似于show processlist）有关，其只会输出当前正在运行的sql，对于已经执行过的，实际上是没办法看到。

但在线上，kill是一个需要谨慎的操作，毕竟你很难知道kill的是不是业务关键操作？又或者，是个批量update操作？那么，有没有办法抓到该事务之前的操作呢？

答案，有。

即performance schema中记录statement event（操作事件）的表，具体包括

events_statements_current,events_statements_history,events_statements_history_long,prepared_statements_instances。

常用的是前面三个。

三者的表结构完全一致，其中，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个操作，如果事务中进行的操作较多，实际上也是没办法抓全的。

总结

以上所述是小编给大家介绍的解决mysql 5.7中如何定位ddl被阻塞的问题，希望对大家有所帮助