(转)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
来源：简书
著作权归作者所有。商业转载请联系作者获得授权，非商业转载请注明出处。
