問題：
DB運維過程中經常遇到DDL操作掛起，查看其狀態(tài)是waiting for waiting for table metadata lock. 而mysql的會話那么多，不知道那個會話的操作沒有及時遞影響了DDL。在mysql5.6，我們排查這類問題，往往需要從information_schema.innodb_trx表中查詢未遞交事務，但當SQL已經執(zhí)行過了，沒有commit,這個時候這個表中是看不到SQL的。于是我們又與performance_schema庫中的相關表進行關聯，來查詢到底這個會話執(zhí)行了什么SQL。 如果只是查詢，則可把它kill掉為DDL放行。 好復雜一個過程。
方案
在mysql5.7.22中，performance_schema庫中新增了metadata_locks表，專門記錄MDL的相關信息。
首先你要開啟這個instrument
UPDATE performance_schema.setup_instruments SET ENABLED = 'YES' WHERE NAME = 'wait/lock/metadata/sql/mdl';
然后查詢下這個表：
mysql> select * from metadata_locks;
+-------------+--------------------+----------------+-----------------------+-------------+---------------+-------------+-------------------+-----------------+----------------+
| OBJECT_TYPE | OBJECT_SCHEMA | OBJECT_NAME | OBJECT_INSTANCE_BEGIN | LOCK_TYPE | LOCK_DURATION | LOCK_STATUS | SOURCE | OWNER_THREAD_ID | OWNER_EVENT_ID |
+-------------+--------------------+----------------+-----------------------+-------------+---------------+-------------+-------------------+-----------------+----------------+
| TABLE | performance_schema | metadata_locks | 139974662269440 | SHARED_READ | TRANSACTION | GRANTED | sql_parse.cc:6020 | 32 | 225 |
+-------------+--------------------+----------------+-----------------------+-------------+---------------+-------------+-------------------+-----------------+----------------+
1 row in set (0.00 sec)
只能看到自己這個會話的MDL鎖情況。
另開一個會話，關閉autocommit,執(zhí)行一個查詢，然后再查詢下metadata_locks
mysql> select * from metadata_locks;
+-------------+--------------------+----------------+-----------------------+-------------+---------------+-------------+-------------------+-----------------+----------------+
| OBJECT_TYPE | OBJECT_SCHEMA | OBJECT_NAME | OBJECT_INSTANCE_BEGIN | LOCK_TYPE | LOCK_DURATION | LOCK_STATUS | SOURCE | OWNER_THREAD_ID | OWNER_EVENT_ID |
+-------------+--------------------+----------------+-----------------------+-------------+---------------+-------------+-------------------+-----------------+----------------+
| TABLE | performance_schema | metadata_locks | 139974662269440 | SHARED_READ | TRANSACTION | GRANTED | sql_parse.cc:6020 | 32 | 225 |
| TABLE | test | t1 | 139975131976512 | SHARED_READ | TRANSACTION | GRANTED | sql_parse.cc:6020 | 33 | 14 |
+-------------+--------------------+----------------+-----------------------+-------------+---------------+-------------+-------------------+-----------------+----------------+
2 rows in set (0.00 sec)
即可看到另一個會話對test庫中的t1表持有MDL鎖，我們再看一個新會話，對t1表執(zhí)行一個DDL操作，如truncate,之后再查詢metadata_locks.
mysql> select * from metadata_locks;
+-------------+--------------------+----------------+-----------------------+---------------------+---------------+-------------+-------------------+-----------------+----------------+
| OBJECT_TYPE | OBJECT_SCHEMA | OBJECT_NAME | OBJECT_INSTANCE_BEGIN | LOCK_TYPE | LOCK_DURATION | LOCK_STATUS | SOURCE | OWNER_THREAD_ID | OWNER_EVENT_ID |
+-------------+--------------------+----------------+-----------------------+---------------------+---------------+-------------+-------------------+-----------------+----------------+
| TABLE | performance_schema | metadata_locks | 139974662269440 | SHARED_READ | TRANSACTION | GRANTED | sql_parse.cc:6020 | 32 | 225 |
| TABLE | test | t1 | 139975131976512 | SHARED_READ | TRANSACTION | GRANTED | sql_parse.cc:6020 | 33 | 14 |
| GLOBAL | NULL | NULL | 139975198834672 | INTENTION_EXCLUSIVE | STATEMENT | GRANTED | sql_base.cc:5533 | 34 | 15 |
| SCHEMA | test | NULL | 139975198835008 | INTENTION_EXCLUSIVE | TRANSACTION | GRANTED | sql_base.cc:5518 | 34 | 15 |
| TABLE | test | t1 | 139975198851888 | EXCLUSIVE | TRANSACTION | PENDING | sql_parse.cc:6020 | 34 | 15 |
+-------------+--------------------+----------------+-----------------------+---------------------+---------------+-------------+-------------------+-----------------+----------------+
5 rows in set (0.00 sec)
看到MDL鎖等待了吧。我們的truncate操作，持有了兩把鎖，都是INTENTION_EXCLUSIVE ， 一個是global 級別，一個是schema級別，另外在表他上等待EXCLUSIVE MDL鎖。非常明了了。而這個MDL鎖有誰持有呢？是有thread_id為33的一個會話，持有鎖類型是SHARED_READ（只讀，可以kill）。注意這個thread_id， 不是 processlist表中的會話ID, 具體是那個會話ID還要結合threads表關聯查詢:
mysql> select m.*,t.PROCESSLIST_ID from metadata_locks m left join threads t on m.owner_thread_id=t.thread_id;
+-------------+--------------------+----------------+-----------------------+---------------------+---------------+-------------+-------------------+-----------------+----------------+----------------+
| OBJECT_TYPE | OBJECT_SCHEMA | OBJECT_NAME | OBJECT_INSTANCE_BEGIN | LOCK_TYPE | LOCK_DURATION | LOCK_STATUS | SOURCE | OWNER_THREAD_ID | OWNER_EVENT_ID | PROCESSLIST_ID |
+-------------+--------------------+----------------+-----------------------+---------------------+---------------+-------------+-------------------+-----------------+----------------+----------------+
| TABLE | performance_schema | metadata_locks | 139974662269440 | SHARED_READ | TRANSACTION | GRANTED | sql_parse.cc:6020 | 32 | 225 | 7 |
| TABLE | performance_schema | threads | 139974674625392 | SHARED_READ | TRANSACTION | GRANTED | sql_parse.cc:6020 | 32 | 235 | 7 |
| TABLE | test | t1 | 139975131976512 | SHARED_READ | TRANSACTION | GRANTED | sql_parse.cc:6020 | 33 | 14 | 8 |
| GLOBAL | NULL | NULL | 139975198834672 | INTENTION_EXCLUSIVE | STATEMENT | GRANTED | sql_base.cc:5533 | 34 | 15 | 9 |
| SCHEMA | test | NULL | 139975198835008 | INTENTION_EXCLUSIVE | TRANSACTION | GRANTED | sql_base.cc:5518 | 34 | 15 | 9 |
| TABLE | test | t1 | 139975198851888 | EXCLUSIVE | TRANSACTION | PENDING | sql_parse.cc:6020 | 34 | 15 | 9 |
+-------------+--------------------+----------------+-----------------------+---------------------+---------------+-------------+-------------------+-----------------+----------------+----------------+
6 rows in set (0.00 sec)
是不是很方便啊！
MDL自mysql5.5引入以來給DBA的運維工作帶來了太多的麻煩，直到mysql5.7官方才提供了針對這個鎖細節(jié)信息的排查方案，DBA可以改善下生活了！
