mysql> begin;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from t1;
+----+------+------+------+-------+
| id | name | age | num | num01 |
+----+------+------+------+-------+
| 1 | uu | NULL | NULL | NULL |
| 2 | uu | NULL | NULL | NULL |
| 3 | uu | NULL | NULL | NULL |
| 4 | uu | NULL | NULL | NULL |
| 5 | uu | NULL | NULL | NULL |
| 6 | uu | NULL | NULL | NULL |
+----+------+------+------+-------+
6 rows in set (0.00 sec)
mysql> update test01.t1 set age=12 where id=1;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> alter table test01.t1 add c1 int;
Query OK, 0 rows affected (0.14 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> 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 | performance_schema | metadata_locks | SHARED_READ | TRANSACTION | GRANTED | 27 |
| TABLE | performance_schema | metadata_locks | SHARED_READ | TRANSACTION | PENDING | 29 |
+-------------+--------------------+----------------+-------------+---------------+-------------+-----------------+
1 row in set (0.00 sec)
这里，重点关注lock_status，"PENDING"代表线程在等待MDL，而"GRANTED"则代表线程持有MDL
3、如何找出引起阻塞的会话
结合owner_thread_id，可以可到，是29号线程在等待27号线程的MDL，此时，可kill掉52号线程。
但需要注意的是，owner_thread_id给出的只是线程ID，并不是show processlist中的ID。如果要查找线程对应的processlist id，需查询performance_schema.threads表。
mysql> 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: 21
PROCESSLIST_USER: root
PROCESSLIST_HOST: localhost
PROCESSLIST_DB: test01
PROCESSLIST_COMMAND: Sleep
PROCESSLIST_TIME: 901
PROCESSLIST_STATE: NULL
PROCESSLIST_INFO: select object_type , object_schema , object_name , lock_type , lock_duration , lock_status , owner_thread_id from performance_schema . metadata_locks
PARENT_THREAD_ID: 1
ROLE: NULL
INSTRUMENTED: YES
HISTORY: YES
CONNECTION_TYPE: Socket
THREAD_OS_ID: 2066
1 row in set (0.00 sec)
*************************** 1. row ***************************
THREAD_ID: 29
NAME: thread/sql/one_connection
TYPE: FOREGROUND
PROCESSLIST_ID: 12
PROCESSLIST_USER: root
PROCESSLIST_HOST: localhost
PROCESSLIST_DB: test01
PROCESSLIST_COMMAND: Query
PROCESSLIST_TIME: 901
PROCESSLIST_STATE: Waiting for table metadata lock
PROCESSLIST_INFO: alter table test01.t1 add c1 int;
PARENT_THREAD_ID: 1
ROLE: NULL
INSTRUMENTED: YES
HISTORY: YES
CONNECTION_TYPE: Socket
THREAD_OS_ID: 2066
1 row in set (0.00 sec)
将这两张表结合，借鉴sys.schema_table_lock_waits的输出，实际上我们也可以直观地呈现MDL的等待关系。
mysql> select * from sys.schema_table_lock_waits\G;