問題：
DB運(yùn)維過程中經(jīng)常遇到DDL操作掛起笼平，查看其狀態(tài)是waiting for waiting for table metadata lock. 而mysql的會話那么多惰说，不知道那個會話的操作沒有及時(shí)遞影響了DDL。在mysql5.6，我們排查這類問題暴浦，往往需要從information_schema.innodb_trx表中查詢未遞交事務(wù)洲尊，但當(dāng)SQL已經(jīng)執(zhí)行過了，沒有commit,這個時(shí)候這個表中是看不到SQL的专执。于是我們又與performance_schema庫中的相關(guān)表進(jìn)行關(guān)聯(lián)淮捆，來查詢到底這個會話執(zhí)行了什么SQL。 如果只是查詢本股，則可把它kill掉為DDL放行攀痊。 好復(fù)雜一個過程。
方案
在mysql5.7.22中拄显，performance_schema庫中新增了metadata_locks表苟径，專門記錄MDL的相關(guān)信息。
首先你要開啟這個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鎖情況躬审。
另開一個會話棘街，關(guān)閉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還要結(jié)合threads表關(guān)聯(lián)查詢:
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的運(yùn)維工作帶來了太多的麻煩，直到mysql5.7官方才提供了針對這個鎖細(xì)節(jié)信息的排查方案凸椿，DBA可以改善下生活了削祈！
