(转)MySQL 5.7中MDL锁排查

文章来自小红书：https://www.jianshu.com/p/c2efdde99e2f

问题：

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可以改善下生活了！

作者：wGrow
链接：https://www.jianshu.com/p/c2efdde99e2f
来源：简书
著作权归作者所有。商业转载请联系作者获得授权，非商业转载请注明出处。