如何通过 `sys.schema_table_lock_waits` 视图诊断`元数据锁`（`MDL`）问题？

通过 sys.schema_table_lock_waits 视图诊断元数据锁（MDL）问题

大家好，今天我们来深入探讨如何利用 sys.schema_table_lock_waits 视图来诊断 MySQL 中的元数据锁（MDL）问题。 MDL 是 MySQL 用来保护数据库对象元数据一致性的关键机制，但当 MDL 竞争激烈时，会导致查询阻塞，甚至整个数据库hang住。 理解如何有效地利用 sys.schema_table_lock_waits 可以帮助我们快速定位并解决这些问题。

1. 什么是元数据锁（MDL）？

MDL 是一种轻量级的锁，用于协调对数据库对象（例如表、视图、存储过程等）元数据的并发访问。 当一个会话需要访问一个数据库对象时，它会尝试获取相应的 MDL。 不同类型的操作需要不同类型的 MDL，例如 MDL_SHARED_READ 用于读取数据，MDL_EXCLUSIVE 用于修改表结构。

MDL 的目的是防止在执行 DDL（数据定义语言，如 ALTER TABLE）操作时，DML（数据操作语言，如 SELECT, INSERT, UPDATE, DELETE）操作修改了相同的元数据，从而导致数据不一致或崩溃。

2. sys.schema_table_lock_waits 视图简介

sys.schema_table_lock_waits 视图是 MySQL 5.7 及更高版本中 sys 数据库的一部分。 它提供了一个实时的快照，显示了当前正在等待 MDL 的会话的信息。 这个视图对于识别哪些会话正在等待 MDL，以及哪些会话正在持有阻止其他会话获取 MDL 的锁至关重要。

3. sys.schema_table_lock_waits 视图的结构

该视图包含以下关键列：

列名	数据类型	描述
object_schema	varchar	正在等待锁的对象的数据库名称。
object_name	varchar	正在等待锁的对象的表名称。
waiting_pid	bigint	正在等待锁的进程 ID (PID)。
waiting_query	longtext	正在等待锁的会话正在执行的查询语句。
waiting_lock_type	varchar	正在等待的锁的类型 (例如, MDL_SHARED_READ, MDL_EXCLUSIVE)。
waiting_lock_duration	varchar	正在等待的锁的持续时间 (例如, TRANSACTION, STATEMENT)。
blocking_pid	bigint	持有阻止 waiting_pid 获取锁的锁的进程 ID (PID)。
blocking_query	longtext	持有阻止 waiting_pid 获取锁的锁的会话正在执行的查询语句。
blocking_lock_type	varchar	持有的阻止 waiting_pid 获取锁的锁的类型。
blocking_lock_duration	varchar	持有的阻止 waiting_pid 获取锁的锁的持续时间。

4. 使用 sys.schema_table_lock_waits 诊断 MDL 问题

现在，我们通过一些实际的例子来说明如何使用 sys.schema_table_lock_waits 视图来诊断 MDL 问题。

场景 1: 长时间运行的 DDL 阻塞了 DML

假设我们正在执行一个 ALTER TABLE 操作，该操作需要很长时间才能完成。 在此期间，其他会话尝试读取或写入相同的表，但它们会被阻塞，因为 ALTER TABLE 持有一个排他 MDL 锁。

首先，我们创建一个测试表：

CREATE TABLE test_mdl (
    id INT PRIMARY KEY,
    value VARCHAR(255)
);

INSERT INTO test_mdl (id, value) VALUES (1, 'initial value');

然后，在一个会话中，我们执行一个长时间运行的 ALTER TABLE 操作（这里我们用 SLEEP() 函数模拟）：

-- 会话 1
ALTER TABLE test_mdl ADD COLUMN new_column INT AFTER value;
SELECT SLEEP(60); -- 模拟长时间运行的 DDL
ALTER TABLE test_mdl DROP COLUMN new_column;

在另一个会话中，我们尝试读取该表：

-- 会话 2
SELECT * FROM test_mdl; -- 此查询会被阻塞

现在，我们可以使用 sys.schema_table_lock_waits 视图来查看等待锁的信息：

SELECT
    object_schema,
    object_name,
    waiting_pid,
    waiting_query,
    waiting_lock_type,
    waiting_lock_duration,
    blocking_pid,
    blocking_query,
    blocking_lock_type,
    blocking_lock_duration
FROM
    sys.schema_table_lock_waits;

输出结果可能如下所示：

object_schema	object_name	waiting_pid	waiting_query	waiting_lock_type	waiting_lock_duration	blocking_pid	blocking_query	blocking_lock_type	blocking_lock_duration
test	test_mdl	123	SELECT * FROM test_mdl	MDL_SHARED_READ	TRANSACTION	456	ALTER TABLE test_mdl ADD COLUMN new_column INT AFTER value; SELECT SLEEP(60); ALTER TABLE test_mdl DROP COLUMN new_column;	MDL_EXCLUSIVE	TRANSACTION

从这个结果中，我们可以看到：

• waiting_pid 为 123 的会话正在等待 MDL_SHARED_READ 锁，它正在执行 SELECT * FROM test_mdl。
• blocking_pid 为 456 的会话正在持有 MDL_EXCLUSIVE 锁，它正在执行 ALTER TABLE 操作。

通过这个信息，我们可以确定长时间运行的 ALTER TABLE 操作是导致 SELECT 语句被阻塞的原因。 解决办法是尽快完成 ALTER TABLE 操作，或者考虑使用在线 DDL 工具（例如 pt-online-schema-change）来最小化对其他查询的影响。

场景 2: 事务未提交导致 MDL 锁持有

另一种常见的 MDL 问题是事务未提交导致 MDL 锁被长时间持有。 例如，一个会话启动了一个事务，执行了一些 DML 操作，但忘记提交或回滚事务。 这会导致该会话持有 MDL 锁，阻止其他会话访问相同的表。

在一个会话中，我们启动一个事务并更新表：

-- 会话 1
START TRANSACTION;
UPDATE test_mdl SET value = 'updated value' WHERE id = 1;
-- 注意：这里没有提交或回滚事务

在另一个会话中，我们尝试修改表结构：

-- 会话 2
ALTER TABLE test_mdl ADD INDEX idx_value (value); -- 此查询会被阻塞

再次使用 sys.schema_table_lock_waits 视图来查看等待锁的信息：

SELECT
    object_schema,
    object_name,
    waiting_pid,
    waiting_query,
    waiting_lock_type,
    waiting_lock_duration,
    blocking_pid,
    blocking_query,
    blocking_lock_type,
    blocking_lock_duration
FROM
    sys.schema_table_lock_waits;

输出结果可能如下所示：

object_schema	object_name	waiting_pid	waiting_query	waiting_lock_type	waiting_lock_duration	blocking_pid	blocking_query	blocking_lock_type	blocking_lock_duration
test	test_mdl	789	ALTER TABLE test_mdl ADD INDEX idx_value (value)	MDL_EXCLUSIVE	TRANSACTION	101	UPDATE test_mdl SET value = ‘updated value’ WHERE id = 1	MDL_EXCLUSIVE	TRANSACTION

从这个结果中，我们可以看到：

• waiting_pid 为 789 的会话正在等待 MDL_EXCLUSIVE 锁，它正在执行 ALTER TABLE 操作。
• blocking_pid 为 101 的会话正在持有 MDL_EXCLUSIVE 锁，它正在执行 UPDATE 操作，并且事务未提交。

解决办法是找到 blocking_pid 对应的会话，并强制终止该会话或提交/回滚事务。 可以使用 KILL 命令终止会话：

KILL 101; -- 终止会话 101

场景 3: 大量并发查询导致 MDL 竞争

在高并发的场景下，即使查询本身运行很快，大量的并发查询也可能导致 MDL 竞争，从而降低整体性能。 例如，大量的 SELECT 语句同时访问同一个表，每个 SELECT 语句都需要获取 MDL_SHARED_READ 锁。

在这种情况下，sys.schema_table_lock_waits 视图可能显示大量的会话正在等待 MDL_SHARED_READ 锁。 但是，由于每个锁的持续时间都很短，所以很难确定哪个会话是主要的阻塞者。

解决办法是优化查询，减少对元数据的访问。 例如，可以考虑使用查询缓存，避免重复解析查询语句。 还可以优化表结构，减少锁的粒度。

5. 其他有用的诊断工具和技巧

除了 sys.schema_table_lock_waits 视图之外，还有其他一些工具和技巧可以帮助我们诊断 MDL 问题：

• SHOW PROCESSLIST: 显示当前 MySQL 服务器上正在执行的线程的信息，包括线程的状态、正在执行的查询等。 可以用来查找长时间运行的查询或未提交的事务。
• performance_schema: 提供更详细的性能监控信息，包括锁的等待时间和持有时间。 可以使用 performance_schema.metadata_locks 表来查看 MDL 的详细信息。
• INFORMATION_SCHEMA.PROCESSLIST: 类似于 SHOW PROCESSLIST，但是可以通过 SQL 查询来获取信息。
• 慢查询日志: 记录执行时间超过指定阈值的查询语句。 可以用来查找需要优化的查询。

6. 预防 MDL 问题

预防胜于治疗。 以下是一些可以帮助我们预防 MDL 问题的最佳实践：

• 保持事务简短: 尽量避免长时间运行的事务，尽快提交或回滚事务。
• 避免在生产环境中执行 DDL 操作: 尽量在非高峰期执行 DDL 操作，或者使用在线 DDL 工具。
• 优化查询: 优化查询语句，减少对元数据的访问。
• 使用查询缓存: 使用查询缓存，避免重复解析查询语句。
• 监控 MDL 锁: 定期监控 sys.schema_table_lock_waits 视图，及时发现并解决 MDL 问题。
• 合理设计表结构: 合理设计表结构，减少锁的粒度。例如，可以将大表拆分成小表，或者使用分区表。

7. 一个更复杂的示例

假设我们遇到一个非常棘手的 MDL 问题，sys.schema_table_lock_waits 显示多个会话相互阻塞，形成一个死锁。

-- 会话 A
START TRANSACTION;
UPDATE table1 SET col1 = 1 WHERE id = 1;
-- 等待会话 B 释放对 table2 的锁

-- 会话 B
START TRANSACTION;
UPDATE table2 SET col2 = 2 WHERE id = 2;
-- 等待会话 A 释放对 table1 的锁

在这种情况下，sys.schema_table_lock_waits 可能会显示：

object_schema	object_name	waiting_pid	waiting_query	waiting_lock_type	waiting_lock_duration	blocking_pid	blocking_query	blocking_lock_type	blocking_lock_duration
test	table2	PID_B	UPDATE table2 …	MDL_EXCLUSIVE	TRANSACTION	PID_A	UPDATE table1 …	MDL_EXCLUSIVE	TRANSACTION
test	table1	PID_A	UPDATE table1 …	MDL_EXCLUSIVE	TRANSACTION	PID_B	UPDATE table2 …	MDL_EXCLUSIVE	TRANSACTION

解决死锁的常见方法是终止其中一个会话。 但是，在生产环境中，盲目地终止会话可能会导致数据丢失或不一致。 因此，我们需要仔细分析死锁的原因，并采取适当的措施。

在这种情况下，我们可以考虑以下解决方案：

• 应用程序层面修改: 重新设计应用程序逻辑，避免跨多个表的事务，或者使用更细粒度的锁。
• 设置锁等待超时: 设置 innodb_lock_wait_timeout 参数，当锁等待时间超过指定阈值时，自动回滚事务。 这可以防止死锁长时间阻塞数据库。
• 调整事务隔离级别: 如果可能，可以考虑使用较低的事务隔离级别，例如 READ COMMITTED，以减少锁的竞争。

关键点的回顾

通过 sys.schema_table_lock_waits 视图，我们可以诊断由长时间运行的 DDL 操作、未提交的事务或高并发查询导致的 MDL 问题。 结合其他诊断工具，并采取预防措施，可以有效地避免和解决 MDL 问题，提高数据库的性能和稳定性。