MySQL 元数据锁 MDL 阻塞排查实战：全站突然卡死时，那句没人执行的 SELECT 才是元凶

  1.  Home
  2. Text  
    3. Share to：

一、先认清一个事实：MDL 不是死锁，但比死锁更常见

很多站长第一次遇到「数据库突然全站卡住」时，第一反应是去查 SHOW ENGINE INNODB STATUS 看死锁，结果什么都没找到。这时候八成是元数据锁（Metadata Lock，简称 MDL）在作怪。

MDL 和 InnoDB 行锁是两套完全独立的机制。行锁锁的是数据行，由存储引擎层实现；MDL 锁的是表结构定义，由 MySQL Server 层实现，跟用什么存储引擎没关系。它的存在目的很简单：防止「有人在读表的时候，另一个人把表结构改了」。

正因为它保护的是表结构这个极容易被忽略的层面，所以它的加锁时机非常隐蔽——绝大多数普通 SELECT 语句也会自动加一把 MDL 读锁，而且这把锁会一直持有到事务提交或回滚为止。这就是 MDL 阻塞事故的根源。

举个真实场景：你的站点后台有个「文章浏览量统计」功能，代码里开了事务，先 SELECT 一下文章表，然后执行一堆逻辑，最后才 COMMIT。如果这段逻辑中间因为某个外部接口超时卡了 30 秒，那这 30 秒内文章表上就挂着一把 MDL 读锁。此时你恰好想给文章表加个索引执行 ALTER TABLE——恭喜，ALTER TABLE 会排在后面等，而它一旦开始等待，后面所有的读写请求也全部被它挡住，形成一条经典的「队列式雪崩」。

二、先用三个命令确认「是不是 MDL」

排查顺序非常重要，不要一上来就 KILL 连接，先把现场固定下来。以下三个查询按顺序执行。

2.1 看当前是否有阻塞关系

SELECT
  waiting_pid     AS 被阻塞线程,
  waiting_query   AS 被阻塞语句,
  blocking_pid    AS 阻塞方线程,
  blocking_query  AS 阻塞方语句,
  wait_age        AS 已等待时长
FROM sys.innodb_lock_waits;

注意：这个视图基于 InnoDB 锁等待构建，MDL 等待不一定出现在这里。如果它返回空，但你确信表被卡住了，继续往下走。

2.2 直接查 performance_schema 里的 MDL 表（关键一步）

SELECT
  t.THREAD_ID,
  t.PROCESSLIST_ID,
  t.PROCESSLIST_USER,
  t.PROCESSLIST_TIME,
  t.PROCESSLIST_STATE,
  t.PROCESSLIST_INFO,
  l.OBJECT_TYPE,
  l.OBJECT_SCHEMA,
  l.OBJECT_NAME,
  l.LOCK_TYPE,
  l.LOCK_DURATION,
  l.LOCK_STATUS
FROM performance_schema.metadata_locks l
JOIN performance_schema.threads t
  ON l.OWNER_THREAD_ID = t.THREAD_ID
WHERE l.OBJECT_SCHEMA NOT IN ('performance_schema','information_schema','mysql')
ORDER BY t.PROCESSLIST_TIME DESC;

这张表是排查 MDL 的核心工具，它能直接告诉你：谁（THREAD_ID / PROCESSLIST_ID）在什么对象（OBJECT_SCHEMA.OBJECT_NAME）上持有什么类型的锁（LOCK_TYPE），以及这个锁的状态是 GRANTED（已授予）还是 PENDING（等待中）。

读法很直接：找到 LOCK_STATUS = 'PENDING' 的行，看它是哪个 OBJECT_NAME、哪个 LOCK_TYPE；然后回到同一张表里找同一个 OBJECT_NAME 上 LOCK_STATUS = 'GRANTED' 并且 LOCK_DURATION = 'TRANSACTION' 的那个线程，它就是罪魁祸首。

LOCK_DURATION 的取值有三种，含义必须搞清楚：

  • STATEMENT：语句结束就释放，通常是 LOCK_TYPE = 'SHARED_READ' 或 'SHARED_WRITE' 的普通 DML。
  • TRANSACTION：事务提交才释放，这是最危险的一类，也是绝大多数事故的成因。
  • EXPLICIT：显式加锁，日常极少见到。

2.3 用 information_schema 兜底（老版本 MySQL 5.7 用这个）

SELECT * FROM information_schema.innodb_trx\G
SELECT * FROM information_schema.processlist WHERE Time > 30 ORDER BY Time DESC;

innodb_trx 里重点看 trx_started 和 trx_mysql_thread_id。只要看到某个事务的 trx_started 是很久以前，而这个线程的 STATE 是 Sleep，基本可以锁定它——一个开着事务却在睡觉的连接，就是 MDL 事故的标准画像。

这里有个经典陷阱：SHOW PROCESSLIST 里这类连接显示的 Info 往往是 NULL，因为它当前没有在执行的语句。如果你只看 Info 列，会完全看不到异常，必须结合 Time 列一起看。Time 表示线程处于当前状态已持续了多少秒，一条 Sleep 状态且 Time 高达几千的记录，就是在明晃晃地告诉你「我的事务没提交」。

三、一个可以手动复现的最小实验

不亲眼看到一次，很难建立起直觉。下面这个实验在三分钟内就能复现，建议在测试库上做一遍。

开三个终端会话，按顺序执行：

-- 会话 A：开启事务，查一次表，然后什么都不做
START TRANSACTION;
SELECT COUNT(*) FROM typecho_contents;
-- 注意：不要 COMMIT，就这样挂着

-- 会话 B：尝试改表结构，会立刻卡住
ALTER TABLE typecho_contents ADD INDEX idx_created (created);
-- 光标停在这里，等待中

-- 会话 C：一个完全无关的普通查询，也会被卡住
SELECT * FROM typecho_contents WHERE cid = 1;
-- 同样卡住，不会返回

此时在第四个会话里执行上面 2.2 节的 metadata_locks 查询，你会看到：会话 B 的 ALTER TABLE 处于 PENDING，而会话 A 的 SELECT 是 GRANTED 且 LOCK_DURATION = 'TRANSACTION'。

注意会话 C 也被卡住了——这是最容易被误解的一点。 很多人以为只有 DDL 会排队，其实一旦 DDL 开始等待，它为了保证拿到锁之后语义正确，会先把后面进来的所有请求一起挡住。所以线上表现为「全站卡死」，而不是「只有加索引卡住」。在会话 A 执行 COMMIT 后，会话 B 立刻拿到锁执行完，会话 C 也随之返回。

四、线上应急处置：怎么安全地解掉阻塞

确认了阻塞方线程后，处置要分两步，顺序不能反。

4.1 先尝试等待，而不是立刻 KILL

如果阻塞方是一个正常业务事务、只是执行慢，优先等它自己提交。贸然 KILL 一个正在写数据的事务会触发回滚，大事务的回滚可能比正向执行还慢，反而把磁盘拖垮。只有当确认阻塞方是「开着事务在睡觉」的空闲连接时，才果断动手。

4.2 精确 KILL，不要随手 KILL 一堆

-- 先看一眼要杀掉谁
SELECT PROCESSLIST_ID, PROCESSLIST_USER, PROCESSLIST_TIME, PROCESSLIST_STATE
FROM performance_schema.threads
WHERE PROCESSLIST_ID = 12345;

-- 确认后精确杀掉
KILL 12345;

-- 如果连接属于某个连接池、杀掉会被立刻重连并重新发起，可以连根拔起
KILL QUERY 12345;   -- 只终止当前语句，连接保留

KILL QUERY 和 KILL CONNECTION 的区别要记牢：KILL QUERY 只终止正在执行的语句，连接和事务上下文保留（事务通常会回滚当前语句）；KILL（等价于 KILL CONNECTION）直接断开连接，事务整体回滚。处置 MDL 阻塞时，通常需要 KILL CONNECTION，因为问题出在「事务没提交」而不是「语句在跑」，杀语句解决不了。

4.3 大批量阻塞时的批量定位脚本

SELECT
  t.PROCESSLIST_ID AS pid,
  t.PROCESSLIST_USER AS usr,
  t.PROCESSLIST_TIME AS secs,
  t.PROCESSLIST_STATE AS state,
  l.OBJECT_NAME AS tbl,
  l.LOCK_TYPE,
  l.LOCK_STATUS
FROM performance_schema.metadata_locks l
JOIN performance_schema.threads t ON l.OWNER_THREAD_ID = t.THREAD_ID
WHERE l.OBJECT_SCHEMA = DATABASE()
ORDER BY l.LOCK_STATUS DESC, t.PROCESSLIST_TIME DESC;

把 LOCK_STATUS 为 GRANTED 且 PROCESSLIST_TIME 最大的那条找出来，就是根源。批量场景下往往是「一个源头，堵住一片」，不要试图一个个去杀 PENDING 的线程，那是治标。

五、根治：五条工程化防线

应急只是止血，真正要解决得从代码和流程上防。以下五条按性价比排序。

5.1 缩短事务边界，绝不在事务里做外部调用

这是最重要的一条。下面这段代码是典型的反面教材：

// ❌ 错误示范：事务里做 HTTP 请求和文件操作
$db->beginTransaction();
$row = $db->query("SELECT * FROM contents WHERE cid = ?");
$remote = file_get_contents('https://api.example.com/check?id=' . $row['cid']);
$db->exec("UPDATE contents SET checked = 1 WHERE cid = ?");
$db->commit();   // 前面卡了 30 秒，这里才释放 MDL

正确做法是把外部调用挪到事务外面，或者先用无事务的普通查询取数据，远程调用完成后再开一个尽可能短的事务去写：

// ✅ 正确：事务只包裹纯粹的数据库写入
$row = $db->query("SELECT * FROM contents WHERE cid = ?");   // 无显式事务，语句级锁立即释放
$remote = file_get_contents('https://api.example.com/check?id=' . $row['cid']);
$db->beginTransaction();
$db->exec("UPDATE contents SET checked = 1 WHERE cid = ?");
$db->commit();

5.2 给事务设一个「超时闹钟」

MySQL 5.7.11 之后提供了两个参数，能有效防止「忘记提交」无限期挂住：

SET GLOBAL wait_timeout = 300;         -- 空闲连接最长存活秒数
SET GLOBAL innodb_lock_wait_timeout = 10;  -- 行锁等待超时秒数
SET GLOBAL max_execution_time = 30000; -- 单位毫秒，只对只读 SELECT 生效

注意 wait_timeout 只能处理「完全空闲」的连接，一个拿着 MDL 却在 Sleep 的连接确实会被它清掉，所以对这个场景有效。但它不能处理「事务开着、连接不空闲」的情况，也有应用层自动重连导致超时反复触发的问题，所以它只是兜底，不是根治。

innodb_lock_wait_timeout 管的是 InnoDB 行锁，对 MDL 无效——很多人误以为调小它就能防 MDL 阻塞，这是个常见误解。MDL 等待没有独立的超时参数（lock_wait_timeout 管的是元数据锁在存储引擎层面的表现，且主要是 DDL 之间的等待），所以MDL 阻塞本质上无法靠参数自动化解，只能靠事务纪律。

5.3 所有 UPDATE/DELETE 都套上事务提交保护

在框架层加统一钩子，保证任何异常路径都会 ROLLBACK。以 PHP PDO 为例：

try {
    $pdo->beginTransaction();
    // ... 业务逻辑
    $pdo->commit();
} catch (Throwable $e) {
    if ($pdo->inTransaction()) {
        $pdo->rollBack();
    }
    throw $e;
}

同时把 SET autocommit = 1 作为连接初始化的一部分写进连接配置，避免某些 ORM 或驱动默认关掉自动提交后忘了处理。

5.4 DDL 一律走低峰期，并且加超时自保

ALTER TABLE 在 MySQL 5.6+ 虽然大多支持 Online DDL，但「获取 MDL 排他锁」这一步始终需要短暂独占，如果拿不到就会无限等待并引发雪崩。给 DDL 加上明确的等待上限：

SET SESSION lock_wait_timeout = 5;
ALTER TABLE typecho_contents ADD INDEX idx_created (created);

这样如果 5 秒内拿不到锁，DDL 自己失败退出，不会拖垮全站。把它写进发布脚本的固定前置语句，是一条极低成本的保险。

对于千万级大表，更稳的做法是用 pt-online-schema-change 或 gh-ost，它们通过「建影子表 + 触发器/二进制日志同步 + 原子改名」的方式绕开长时间独占：

pt-online-schema-change \
  --alter "ADD INDEX idx_created (created)" \
  --host=127.0.0.1 --user=root --ask-pass \
  D=zz1984,t=typecho_contents \
  --critical-load Threads_running=80 \
  --max-load Threads_running=50 \
  --execute

--max-load 是安全阀：当 Threads_running 超过阈值时工具会暂停复制，避免把线上压垮。这条参数比 --critical-load 更常用。

5.5 加监控：把「长事务」当成一级告警

MDL 事故的共同特征是「事务开太久」。所以只要监控长事务，就能提前发现问题。写一个每分钟跑的巡检脚本：

#!/bin/bash
# /root/mdl_watch.sh —— 长事务监控，超过 60 秒即告警
THRESHOLD=60
RESULT=$(mysql -uroot -p"$DB_PW" -N -B -e "
  SELECT trx_mysql_thread_id, TIMESTAMPDIFF(SECOND, trx_started, NOW()) AS secs, trx_state
  FROM information_schema.innodb_trx
  WHERE TIMESTAMPDIFF(SECOND, trx_started, NOW()) > $THRESHOLD
")
if [ -n "$RESULT" ]; then
  echo "[$(date '+%F %T')] 发现长事务:" | tee -a /var/log/mdl_watch.log
  echo "$RESULT" | tee -a /var/log/mdl_watch.log
fi

再配合前面 metadata_locks 的查询，做一个「PENDING 锁数量超过 N 就告警」的规则，基本就能在用户投诉之前发现异常。

六、四个最容易踩的误区

  • 误区一：以为读不锁表。 普通 SELECT 确实不锁数据行，但它会加 MDL 读锁，且在显式事务里会持有到事务结束。MyISAM 的「读不阻塞写」经验完全不适用。
  • 误区二：以为 innodb_lock_wait_timeout 能救 MDL。 如前所述它只管行锁。调小它反而会让正常业务报 Lock wait timeout exceeded，得不偿失。
  • 误区三：用 SHOW PROCESSLIST 的 Info 列判断。 拿 MDL 的「睡眠事务」Info 是 NULL，必须看 Time 和 State。
  • 误区四：在连接池环境里直接 KILL。 应用会立即重连并重发同样的 SQL，可能形成「杀了又堵」的循环。这种情况要先摘掉应用层的失败重试（暂停发布、摘流），再 KILL。

七、小结：一张排查决策清单

把前面的内容压缩成可执行清单，遇到「数据库突然全卡」时按顺序走：

  1. 查 sys.innodb_lock_waits——有结果就是行锁问题，按死锁/锁等待处理。
  2. 结果为空但确实卡住，查 performance_schema.metadata_locks——找 LOCK_STATUS='PENDING' 的对象。
  3. 在同一对象上找 GRANTED 且 LOCK_DURATION='TRANSACTION' 的线程，它就是要处置的目标。
  4. 看它是「正在执行的大事务」还是「Sleep 状态的空闲事务」：前者等、后者杀。
  5. 处置用 KILL CONNECTION，不是 KILL QUERY。
  6. 恢复后立刻回查代码里的 beginTransaction，把事务里的一切网络/文件操作挪出去。
  7. 把 lock_wait_timeout 写进 DDL 发布脚本，把长事务监控加到巡检里。

MDL 这类问题的特点是「平时完全无感，一旦发作就是全站级故障」。它不需要多高深的知识，但需要你对「事务边界」这件事始终保持敬畏。作为个人站长，你未必天天遇到它，但只要能记住第 2 步那个 metadata_locks 查询，就已经超过大多数人了。

Last modification：September 25th, 2026 at 12:24 pm
© 允许规范转载

相关文章

Leave a Comment