# 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 | | | | +---------------+-----------------+------+-----+---------+-------+ ``` 表1metadata_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; ```