监控MySQL MetaData Lock

UPDATE performance_schema.setup_instruments SET ENABLED = 'YES' WHERE NAME ='wait/lock/metadata/sql/mdl';

select * from performance_schema.metadata_locks

 可以用下面的SQL查出锁的ID并给出kill的语句（sql_kill_blocking_connection字段）：

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