问题：
DB运维过程中经常遇到DDL操作挂起，查看其状态是waiting for waiting for table metadata lock. 而mysql的会话那么多，不知道那个会话的操作没有及时递影响了DDL。在mysql5.6，我们排查这类问题，往往需要从information_schema.innodb_trx表中查询未递交事务，但当SQL已经执行过了，没有commit,这个时候这个表中是看不到SQL的。于是我们又与performance_schema库中的相关表进行关联，来查询到底这个会话执行了什么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,执行一个查询，然后再查询下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表执行一个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官方才提供了针对这个锁细节信息的排查方案，DBA可以改善下生活了！
网友评论