在执行语句时，我们可能经常会遇到阻塞等待MDL锁的情况。例如：使用show processlist语句查看线程信息时可能会发现State字段值为"Waiting for table metadata lock"。那么，当遇到这种情况时，应该如何排查是谁持有了MDL锁没有释放呢？下面我们尝试进行MDL锁的等待场景模拟(MDL锁记录对应的instruments为wait/lock/metadata/sql/mdl，默认没有启用；对应的consumers为performance_schema.metadata_locks)，在setup_consumers中只受全局配置项global_instrumentation控制，默认开启）。
mysql> begin; Query OK, 0 rows affected (0.00 sec) mysql> update sbtest1 set pad=‘yyy‘ where id = 1; Query OK, 1 row affected (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 0
mysql> use test; Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A Database changed mysql> alter table sbtest1 add index i_c(c); #被阻塞
mysql> show processlist; +------+------+---------------------+--------------------+---------+------+---------------------------------+--------------------------------------+ | Id | User | Host | db | Command | Time | State | Info | +------+------+---------------------+--------------------+---------+------+---------------------------------+--------------------------------------+ | 1029 | root | localhost | test | Sleep | 342 | | NULL | | 1030 | root | localhost | performance_schema | Query | 0 | starting | show processlist | | 1031 | root | localhost | test | Query | 201 | Waiting for table metadata lock | alter table sbtest1 add index i_c(c) | | 1032 | root | 218.72.68.237:62091 | performance_schema | Sleep | 197 | | NULL | +------+------+---------------------+--------------------+---------+------+---------------------------------+--------------------------------------+ 4 rows in set (0.00 sec)
use performance_schema; select p.THREAD_ID, l.ID as processlist_id, p.event_name, p.TIMER_START, round(p.TIMER_WAIT / 1000 / 1000 / 1000 /1000,2) as "TIMER_WAIT(s)", p.CURRENT_SCHEMA, p.SQL_TEXT, l.host, l.DB, l.STATE, ml.LOCK_TYPE, ml.LOCK_DURATION, ml.LOCK_STATUS, ml.OWNER_EVENT_ID from events_statements_current p inner join metadata_locks ml inner join information_schema.PROCESSLIST l on ml.OWNER_THREAD_ID = p.THREAD_ID and sys.ps_thread_id(l.id) = ml.OWNER_THREAD_ID and ml.OWNER_THREAD_ID != sys.ps_thread_id(connection_id()); +-----------+----------------+---------------------------+---------------------+---------------+----------------+-------------------------------------------+-----------+------+---------------------------------+---------------------+---------------+-------------+----------------+ | THREAD_ID | processlist_id | event_name | TIMER_START | TIMER_WAIT(s) | CURRENT_SCHEMA | SQL_TEXT | host | DB | STATE | LOCK_TYPE | LOCK_DURATION | LOCK_STATUS | OWNER_EVENT_ID | +-----------+----------------+---------------------------+---------------------+---------------+----------------+-------------------------------------------+-----------+------+---------------------------------+---------------------+---------------+-------------+----------------+ | 1054 | 1029 | statement/sql/update | 1503222543526366000 | 0.00 | test | update sbtest1 set pad=‘yyy‘ where id = 1 | localhost | test | | SHARED_WRITE | TRANSACTION | GRANTED | 1221 | | 1056 | 1031 | statement/sql/alter_table | 1503364065280785000 | 226.98 | test | alter table sbtest1 add index i_c(c) | localhost | test | Waiting for table metadata lock | INTENTION_EXCLUSIVE | STATEMENT | GRANTED | 1169 | | 1056 | 1031 | statement/sql/alter_table | 1503364065280785000 | 226.98 | test | alter table sbtest1 add index i_c(c) | localhost | test | Waiting for table metadata lock | INTENTION_EXCLUSIVE | TRANSACTION | GRANTED | 1169 | | 1056 | 1031 | statement/sql/alter_table | 1503364065280785000 | 226.98 | test | alter table sbtest1 add index i_c(c) | localhost | test | Waiting for table metadata lock | SHARED_UPGRADABLE | TRANSACTION | GRANTED | 1171 | | 1056 | 1031 | statement/sql/alter_table | 1503364065280785000 | 226.98 | test | alter table sbtest1 add index i_c(c) | localhost | test | Waiting for table metadata lock | EXCLUSIVE | TRANSACTION | PENDING | 1254 | +-----------+----------------+---------------------------+---------------------+---------------+----------------+-------------------------------------------+-----------+------+---------------------------------+---------------------+---------------+-------------+----------------+ 5 rows in set (0.00 sec)
+-----------+----------------+---------------------------+---------------------+---------------+----------------+--------------------------------------+-----------+------+---------------------------------+---------------------+---------------+-------------+----------------+ | THREAD_ID | processlist_id | event_name | TIMER_START | TIMER_WAIT(s) | CURRENT_SCHEMA | SQL_TEXT | host | DB | STATE | LOCK_TYPE | LOCK_DURATION | LOCK_STATUS | OWNER_EVENT_ID | +-----------+----------------+---------------------------+---------------------+---------------+----------------+--------------------------------------+-----------+------+---------------------------------+---------------------+---------------+-------------+----------------+ | 1054 | 1029 | statement/sql/select | 1503956228259601000 | 0.01 | test | select * from sbtest1 | localhost | test | | SHARED_WRITE | TRANSACTION | GRANTED | 1221 | | 1056 | 1031 | statement/sql/alter_table | 1503364065280785000 | 598.18 | test | alter table sbtest1 add index i_c(c) | localhost | test | Waiting for table metadata lock | INTENTION_EXCLUSIVE | STATEMENT | GRANTED | 1169 | | 1056 | 1031 | statement/sql/alter_table | 1503364065280785000 | 598.18 | test | alter table sbtest1 add index i_c(c) | localhost | test | Waiting for table metadata lock | INTENTION_EXCLUSIVE | TRANSACTION | GRANTED | 1169 | | 1056 | 1031 | statement/sql/alter_table | 1503364065280785000 | 598.18 | test | alter table sbtest1 add index i_c(c) | localhost | test | Waiting for table metadata lock | SHARED_UPGRADABLE | TRANSACTION | GRANTED | 1171 | | 1056 | 1031 | statement/sql/alter_table | 1503364065280785000 | 598.18 | test | alter table sbtest1 add index i_c(c) | localhost | test | Waiting for table metadata lock | EXCLUSIVE | TRANSACTION | PENDING | 1254 | +-----------+----------------+---------------------------+---------------------+---------------+----------------+--------------------------------------+-----------+------+---------------------------------+---------------------+---------------+-------------+----------------+ 5 rows in set (0.00 sec)
use performance_schema; select p.THREAD_ID, l.ID as processlist_id, p.event_name, round(p.TIMER_WAIT / 1000 / 1000 / 1000 /1000,2) as "TIMER_WAIT(s)", p.CURRENT_SCHEMA, p.SQL_TEXT, l.host, l.DB, l.STATE, ml.LOCK_TYPE, ml.LOCK_DURATION, ml.LOCK_STATUS, ml.OWNER_EVENT_ID from events_statements_history p inner join metadata_locks ml inner join information_schema.PROCESSLIST l on ml.OWNER_THREAD_ID = p.THREAD_ID and sys.ps_thread_id(l.id) = ml.OWNER_THREAD_ID and ml.OWNER_THREAD_ID != sys.ps_thread_id(connection_id()); +-----------+----------------+--------------------------+---------------+----------------+-------------------------------------------+-----------+------+---------------------------------+---------------------+---------------+-------------+----------------+ | THREAD_ID | processlist_id | event_name | TIMER_WAIT(s) | CURRENT_SCHEMA | SQL_TEXT | host | DB | STATE | LOCK_TYPE | LOCK_DURATION | LOCK_STATUS | OWNER_EVENT_ID | +-----------+----------------+--------------------------+---------------+----------------+-------------------------------------------+-----------+------+---------------------------------+---------------------+---------------+-------------+----------------+ | 1054 | 1029 | statement/sql/begin | 0.00 | test | begin | localhost | test | | SHARED_WRITE | TRANSACTION | GRANTED | 1221 | | 1054 | 1029 | statement/sql/update | 0.00 | test | update sbtest1 set pad=‘yyy‘ where id = 1 | localhost | test | | SHARED_WRITE | TRANSACTION | GRANTED | 1221 | | 1054 | 1029 | statement/sql/select | 0.01 | test | select * from sbtest1 | localhost | test | | SHARED_WRITE | TRANSACTION | GRANTED | 1221 | | 1054 | 1029 | statement/com/Field List | 0.00 | test | NULL | localhost | test | | SHARED_WRITE | TRANSACTION | GRANTED | 1221 | | 1054 | 1029 | statement/com/Field List | 0.00 | test | NULL | localhost | test | | SHARED_WRITE | TRANSACTION | GRANTED | 1221 | | 1054 | 1029 | statement/com/Field List | 0.00 | test | NULL | localhost | test | | SHARED_WRITE | TRANSACTION | GRANTED | 1221 | | 1054 | 1029 | statement/com/Field List | 0.00 | test | NULL | localhost | test | | SHARED_WRITE | TRANSACTION | GRANTED | 1221 | | 1054 | 1029 | statement/com/Field List | 0.00 | test | NULL | localhost | test | | SHARED_WRITE | TRANSACTION | GRANTED | 1221 | | 1054 | 1029 | statement/com/Field List | 0.00 | test | NULL | localhost | test | | SHARED_WRITE | TRANSACTION | GRANTED | 1221 | | 1054 | 1029 | statement/com/Field List | 0.00 | test | NULL | localhost | test | | SHARED_WRITE | TRANSACTION | GRANTED | 1221 | | 1056 | 1031 | statement/com/Field List | 0.00 | test | NULL | localhost | test | Waiting for table metadata lock | INTENTION_EXCLUSIVE | STATEMENT | GRANTED | 1169 | | 1056 | 1031 | statement/com/Field List | 0.00 | test | NULL | localhost | test | Waiting for table metadata lock | INTENTION_EXCLUSIVE | TRANSACTION | GRANTED | 1169 | | 1056 | 1031 | statement/com/Field List | 0.00 | test | NULL | localhost | test | Waiting for table metadata lock | SHARED_UPGRADABLE | TRANSACTION | GRANTED | 1171 | | 1056 | 1031 | statement/com/Field List | 0.00 | test | NULL | localhost | test | Waiting for table metadata lock | EXCLUSIVE | TRANSACTION | PENDING | 1254 | | 1056 | 1031 | statement/com/Field List | 0.00 | test | NULL | localhost | test | Waiting for table metadata lock | INTENTION_EXCLUSIVE | STATEMENT | GRANTED | 1169 | | 1056 | 1031 | statement/com/Field List | 0.00 | test | NULL | localhost | test | Waiting for table metadata lock | INTENTION_EXCLUSIVE | TRANSACTION | GRANTED | 1169 | | 1056 | 1031 | statement/com/Field List | 0.00 | test | NULL | localhost | test | Waiting for table metadata lock | SHARED_UPGRADABLE | TRANSACTION | GRANTED | 1171 | | 1056 | 1031 | statement/com/Field List | 0.00 | test | NULL | localhost | test | Waiting for table metadata lock | EXCLUSIVE | TRANSACTION | PENDING | 1254 | | 1056 | 1031 | statement/com/Field List | 0.00 | test | NULL | localhost | test | Waiting for table metadata lock | INTENTION_EXCLUSIVE | STATEMENT | GRANTED | 1169 | | 1056 | 1031 | statement/com/Field List | 0.00 | test | NULL | localhost | test | Waiting for table metadata lock | INTENTION_EXCLUSIVE | TRANSACTION | GRANTED | 1169 | | 1056 | 1031 | statement/com/Field List | 0.00 | test | NULL | localhost | test | Waiting for table metadata lock | SHARED_UPGRADABLE | TRANSACTION | GRANTED | 1171 | | 1056 | 1031 | statement/com/Field List | 0.00 | test | NULL | localhost | test | Waiting for table metadata lock | EXCLUSIVE | TRANSACTION | PENDING | 1254 | | 1056 | 1031 | statement/com/Field List | 0.00 | test | NULL | localhost | test | Waiting for table metadata lock | INTENTION_EXCLUSIVE | STATEMENT | GRANTED | 1169 | | 1056 | 1031 | statement/com/Field List | 0.00 | test | NULL | localhost | test | Waiting for table metadata lock | INTENTION_EXCLUSIVE | TRANSACTION | GRANTED | 1169 | | 1056 | 1031 | statement/com/Field List | 0.00 | test | NULL | localhost | test | Waiting for table metadata lock | SHARED_UPGRADABLE | TRANSACTION | GRANTED | 1171 | | 1056 | 1031 | statement/com/Field List | 0.00 | test | NULL | localhost | test | Waiting for table metadata lock | EXCLUSIVE | TRANSACTION | PENDING | 1254 | | 1056 | 1031 | statement/com/Field List | 0.00 | test | NULL | localhost | test | Waiting for table metadata lock | INTENTION_EXCLUSIVE | STATEMENT | GRANTED | 1169 | | 1056 | 1031 | statement/com/Field List | 0.00 | test | NULL | localhost | test | Waiting for table metadata lock | INTENTION_EXCLUSIVE | TRANSACTION | GRANTED | 1169 | | 1056 | 1031 | statement/com/Field List | 0.00 | test | NULL | localhost | test | Waiting for table metadata lock | SHARED_UPGRADABLE | TRANSACTION | GRANTED | 1171 | | 1056 | 1031 | statement/com/Field List | 0.00 | test | NULL | localhost | test | Waiting for table metadata lock | EXCLUSIVE | TRANSACTION | PENDING | 1254 | | 1056 | 1031 | statement/com/Field List | 0.00 | test | NULL | localhost | test | Waiting for table metadata lock | INTENTION_EXCLUSIVE | STATEMENT | GRANTED | 1169 | | 1056 | 1031 | statement/com/Field List | 0.00 | test | NULL | localhost | test | Waiting for table metadata lock | INTENTION_EXCLUSIVE | TRANSACTION | GRANTED | 1169 | | 1056 | 1031 | statement/com/Field List | 0.00 | test | NULL | localhost | test | Waiting for table metadata lock | SHARED_UPGRADABLE | TRANSACTION | GRANTED | 1171 | | 1056 | 1031 | statement/com/Field List | 0.00 | test | NULL | localhost | test | Waiting for table metadata lock | EXCLUSIVE | TRANSACTION | PENDING | 1254 | | 1056 | 1031 | statement/com/Field List | 0.00 | test | NULL | localhost | test | Waiting for table metadata lock | INTENTION_EXCLUSIVE | STATEMENT | GRANTED | 1169 | | 1056 | 1031 | statement/com/Field List | 0.00 | test | NULL | localhost | test | Waiting for table metadata lock | INTENTION_EXCLUSIVE | TRANSACTION | GRANTED | 1169 | | 1056 | 1031 | statement/com/Field List | 0.00 | test | NULL | localhost | test | Waiting for table metadata lock | SHARED_UPGRADABLE | TRANSACTION | GRANTED | 1171 | | 1056 | 1031 | statement/com/Field List | 0.00 | test | NULL | localhost | test | Waiting for table metadata lock | EXCLUSIVE | TRANSACTION | PENDING | 1254 | | 1056 | 1031 | statement/com/Field List | 0.00 | test | NULL | localhost | test | Waiting for table metadata lock | INTENTION_EXCLUSIVE | STATEMENT | GRANTED | 1169 | | 1056 | 1031 | statement/com/Field List | 0.00 | test | NULL | localhost | test | Waiting for table metadata lock | INTENTION_EXCLUSIVE | TRANSACTION | GRANTED | 1169 | | 1056 | 1031 | statement/com/Field List | 0.00 | test | NULL | localhost | test | Waiting for table metadata lock | SHARED_UPGRADABLE | TRANSACTION | GRANTED | 1171 | | 1056 | 1031 | statement/com/Field List | 0.00 | test | NULL | localhost | test | Waiting for table metadata lock | EXCLUSIVE | TRANSACTION | PENDING | 1254 | | 1056 | 1031 | statement/com/Field List | 0.00 | test | NULL | localhost | test | Waiting for table metadata lock | INTENTION_EXCLUSIVE | STATEMENT | GRANTED | 1169 | | 1056 | 1031 | statement/com/Field List | 0.00 | test | NULL | localhost | test | Waiting for table metadata lock | INTENTION_EXCLUSIVE | TRANSACTION | GRANTED | 1169 | | 1056 | 1031 | statement/com/Field List | 0.00 | test | NULL | localhost | test | Waiting for table metadata lock | SHARED_UPGRADABLE | TRANSACTION | GRANTED | 1171 | | 1056 | 1031 | statement/com/Field List | 0.00 | test | NULL | localhost | test | Waiting for table metadata lock | EXCLUSIVE | TRANSACTION | PENDING | 1254 | | 1056 | 1031 | statement/com/Field List | 0.00 | test | NULL | localhost | test | Waiting for table metadata lock | INTENTION_EXCLUSIVE | STATEMENT | GRANTED | 1169 | | 1056 | 1031 | statement/com/Field List | 0.00 | test | NULL | localhost | test | Waiting for table metadata lock | INTENTION_EXCLUSIVE | TRANSACTION | GRANTED | 1169 | | 1056 | 1031 | statement/com/Field List | 0.00 | test | NULL | localhost | test | Waiting for table metadata lock | SHARED_UPGRADABLE | TRANSACTION | GRANTED | 1171 | | 1056 | 1031 | statement/com/Field List | 0.00 | test | NULL | localhost | test | Waiting for table metadata lock | EXCLUSIVE | TRANSACTION | PENDING | 1254 | +-----------+----------------+--------------------------+---------------+----------------+-------------------------------------------+-----------+------+---------------------------------+---------------------+---------------+-------------+----------------+ 50 rows in set (0.02 sec)
原文：https://www.cnblogs.com/yeleifeng/p/14648828.html