MDL锁视图

MDL锁视图简介

元数据锁（Metadata Lock，简称 MDL锁），主要用于管理对数据库对象的并发访问，当部分事务长时间持有MDL锁时，可能会阻塞其他会话获取相应的MDL锁。在业务场景较复杂的情况下，用户需要拥有快速定位这类问题的能力。

由于社区版MySQL需要打开Performance Schema性能分析监控插件的开关，才能获取元数据锁Metadata Lock（MDL）的详细信息，但通常情况下由于性能等因素的影响，Performance Schema插件会默认关闭。用户无法确定各个会话之间MDL锁的关联关系，只能盲目地Kill大量可疑的会话，甚至直接重启实例来快速恢复业务，继而增加了解决问题的成本，对业务产生了较大影响。

针对以上问题，TaurusDB推出了MDL锁视图特性，可以查看数据库各会话持有和等待的元数据锁信息，用户可以有效进行系统诊断，优化自身业务，有效降低对业务影响。

MDL锁视图详解

MDL锁视图以系统表的形式呈现，该表位于“information_schema”下，表名称是“metadata_lock_info”。表结构如下所示。

desc information_schema.metadata_lock_info;
+---------------+-----------------+------+-----+---------+-------+
| Field         | Type            | Null | Key | Default | Extra |
+---------------+-----------------+------+-----+---------+-------+
| THREAD_ID     | bigint unsigned | NO   |     |         |       |
| LOCK_STATUS   | varchar(32)     | NO   |     |         |       |
| LOCK_MODE     | varchar(32)     | YES  |     |         |       |
| LOCK_TYPE     | varchar(32)     | YES  |     |         |       |
| LOCK_DURATION | varchar(32)     | YES  |     |         |       |
| TABLE_SCHEMA  | varchar(64)     | NO   |     |         |       |
| TABLE_NAME    | varchar(64)     | NO   |     |         |       |
+---------------+-----------------+------+-----+---------+-------+

表1 metadata_lock_info字段
字段名	字段定义	字段说明
THREAD_ID	bigint unsigned	会话ID。
LOCK_STATUS	varchar(32)	MDL锁的两种状态。 PENDING：表示会话正在等待该MDL锁。 GRANTED：表示会话已获得该MDL锁。
LOCK_MODE	varchar(32)	加锁的模式。常用的加锁模式如下： MDL_SHARED：仅访问表结构的元数据，不涉及对数据的增删改查时添加MDL锁，例如SHOW CREATE TABLE语句。 MDL_SHARED_READ：读取表数据时添加MDL锁，例如SELECT语句。 MDL_SHARED_WRITE等：修改表数据时添加MDL锁，例如INSERT，UPDATE，DELETE语句。 MDL_EXCLUSIVE：修改表结构时添加（DDL语句）MDL锁，用来阻塞其他会话对表的访问。
LOCK_TYPE	varchar(32)	MDL锁的类型。常用的MDL锁类型如下： Table metadata lock：表级元数据锁，保护单个表的元数据。 Schema metadata lock：库级别的元数据锁，保护库内的所有表的元数据。 Global read lock：实例级别的读锁，用于阻塞实例的所有写请求。
LOCK_DURATION	varchar(32)	MDL锁范围，取值如下： MDL_STATEMENT：表示语句级别MDL锁。 MDL_TRANSACTION：表示事务级别MDL锁。 MDL_EXPLICIT：表示GLOBAL级别MDL锁。
TABLE_SCHEMA	varchar(64)	数据库名，对于部分GLOBAL级别的MDL锁，该值为空。
TABLE_NAME	varchar(64)	表名，对于部分GLOBAL级别的MDL锁，该值为空。

MDL锁视图案例分析

使用场景：长时间未提交的事务导致DDL语句被阻塞，继而阻塞所有同表的其他操作。

问题描述：如下表所示，由于session3存在一个长事务未提交，会一直持有t2表的MDL_SHARED_READ（SR）类型锁。当session4对表t2执行TRUNCATE操作时，需要获取MDL-EXCLUSIVE（X）锁，但由于MDL锁类型和SR锁不兼容而被阻塞。随后，session5的DML操作在添加SR类型的锁时，发现MDL锁等待队列中有比SR类型的锁优先级更高的X锁在等待，所以session5的SR锁请求也会处于等待状态。此时需要找到导致MDL锁阻塞的会话。

表2 会话信息
时间	会话
时间	session2	session3	session4	session5
t1	begin; select * from t1;	-	-	-
t2	-	begin; select * from t2;	-	-
t3	-	-	truncate table t2; (blocked)	-
t4	-	-	-	begin; select * from t2; (blocked)

排查分析

无MDL锁视图

当发现DDL语句被阻塞后，执行show processlist查看线程信息，结果如下所示。

show processlist;
+------+--------+--------------+--------+-----------+----------+-----------------------------------|-------------------------|
| Id   | User   |  Host        |  db    |  Command  |   Time   |   State                           |Info                     |
+---------------+-----------------------+-----------+----------+-----------------------------------+-------------------------|
| 2    | root   |  localhost   |  test  |  Sleep    |   73     |                                   | Null                    |
| 3    | root   |  localhost   |  test  |  Sleep    |   63     |                                   | Null                    |
| 4    | root   |  localhost   |  Null  |  Query    |   35     | Waiting for table metadata lock   | truncate table test.t2  |
| 5    | root   |  localhost   |  test  |  Query    |   17     | Waiting for table metadata lock   | select * from test.t2   |
| 6    | root   |  localhost   |  test  |  Query    |    0     | starting                          | show processlist        |
+------+--------+--------------+--------+-----------+----------+-----------------------------------|-------------------------|

上述线程列表信息显示：

ID=4的会话执行truncate操作时被其他会话持有的table metadata lock阻塞。
ID=5的会话执行查询操作时同样被阻塞。
无法确定哪个会话阻塞了ID=4的会话和ID=5的会话。

此时，如果随机KILL其他会话会给线上业务带来很大风险，因此只能等待其他会话释放该MDL锁。

使用MDL锁视图

执行select * from information_schema.metadata_lock_info查看元数据锁信息，结果如下所示。

select * from information_schema.metadata_lock_info;
+-------------+-------------+--------------------------+----------------------+-------------------+----------------+----------------+
| THREAD_ID   | LOCK_STATUS |  LOCK_MODE               | LOCK_TYPE            |  LOCK_DURATION    |  TABLE_SCHEMA  |   TABLE_NAME   |
+-------------+-------------+--------------------------+----------------------+-------------------+----------------+----------------+
| 2           | GRANTED     |  MDL_SHARED_READ         | Table metadata lock  |  MDL_TRANSACTION  |  test          |   t1           |
| 3           | GRANTED     |  MDL_SHARED_READ         | Table metadata lock  |  MDL_TRANSACTION  |  test          |   t2           |
| 4           | GRANTED     |  MDL_INTENTION_EXCLUSIVE | Global read lock     |  MDL_STATEMENT    |                |                |
| 4           | GRANTED     |  MDL_INTENTION_EXCLUSIVE | Schema metadata lock |  MDL_TRANSACTION  |  test          |                |
| 4           | PENDING     |  MDL_EXCLUSIVE           | Table metadata lock  |                   |  test          |   t2           |
| 5           | PENDING     |  MDL_SHARED_READ         | Table metadata lock  |                   |  test          |   t2           |
+-------------+-------------+--------------------------+----------------------+-------------------+----------------+----------------+

结合show processlist的结果，从元数据锁视图中可以明显看出：

上述线程信息和元数据锁视图信息显示：

THREAD_ID=4的会话正在等待表t2的metadata lock。
THREAD_ID=3的会话持有表t2的metadata lock，该MDL锁为事务级别，因此只要THREAD_ID=3的会话的事务不提交，THREAD_ID=4的会话将会一直阻塞

因此，用户只需在THREAD_ID=3的会话中执行命令commit或终止THREAD_ID=3的会话，便可以让业务继续运行。

常见问题

Q：如何快速找到导致MDL锁阻塞的会话信息？

A：虽然MDL锁视图有助于定位导致大量MDL锁等待的根源，但当会话数量较多时，表中大量不相关的MDL锁信息会耗费大量查询时间。为此，我们提供一个可以快速找到阻塞会话的SQL。在问题发生时，只需执行该语句，便能迅速定位需要终止的会话ID。

SELECT f.processlist_id, p.Info AS sql_info
FROM (
     SELECT DISTINCT c.blocking_processlist_id AS processlist_id
     FROM (
         SELECT DISTINCT b.THREAD_ID AS blocking_processlist_id
         FROM information_schema.metadata_lock_info a
             JOIN information_schema.metadata_lock_info b
             ON a.TABLE_SCHEMA = b.TABLE_SCHEMA
                 AND a.TABLE_NAME = b.TABLE_NAME
                 AND a.lock_status = 'PENDING'
                 AND b.lock_status = 'GRANTED'
                 AND a.THREAD_ID <> b.THREAD_ID
     ) c
     WHERE c.blocking_processlist_id NOT IN (
         SELECT DISTINCT d.THREAD_ID AS blocked_processlist_id
         FROM information_schema.metadata_lock_info d
             JOIN information_schema.metadata_lock_info e
             ON d.TABLE_SCHEMA = e.TABLE_SCHEMA
                 AND d.TABLE_NAME = e.TABLE_NAME
                 AND d.lock_status = 'PENDING'
                 AND e.lock_status = 'GRANTED'
                 AND d.THREAD_ID <> e.THREAD_ID
     )
 ) f
     JOIN information_schema.processlist p ON processlist_id = p.Id;

父主题：事务与锁管理

上一篇：事务与锁管理

下一篇：回收站