一条 ALTER 拖死全表:MySQL 8.4.11 MDL 阻塞完整时间线实测(19.76s / 17.76s / 0.48s 对照)

简介: 一条 ALTER 会不会锁表?8.4.11 实测:长事务持锁 20 秒,ALTER 等 19.76s、后面的 COUNT 连带等 17.76s,无对手轮 0.48s。附六行锁表与取证链

「高并发实测」系列第 8 篇。

场景声明:MySQL 8.4.11 单实例(本地 Docker 实验环境,非生产),故障注入演练,全部操作在独立实验库 mysql_lab 完成,业务库未被触及。

先把一个被搜得最多的问题摆出来:一条 ALTER TABLE 到底会不会锁表? 发布窗口执行完 DDL,整张表的查询集体超时,是网络抖动、连接池打满,还是慢 SQL 突然变多?常规排查顺序几乎总是网络、GC、连接池、慢 SQL,但这次的答案不在这一层。

因为「锁表」不是一个开关,而是一条队列:决定后果的不是 ALTER 本身,而是它前面有没有一个没提交的长事务、以及它排队时后面还跟着多少正常查询。

所以本文把这个大问题收窄成一句可验证的话:本实验中,一个未提交事务持锁 20 秒,会让一条 ALTER 和它后面的普通 SELECT 各等多久? 实测 19.76 秒 / 17.76 秒;无对手的对照轮只要 0.48 秒。答案落在下面三个会话的逐秒时间线、六行 metadata_locks 现场,以及处置时两个 KILL 为什么不等价——而不在"会锁表 / 不会锁表"这个二选一里。

每个后端都见过(或迟早会见到)这个场景:发布窗口里执行一条 ALTER,访问这张表的查询开始排队,接口超时告警刷屏。元凶是元数据锁(MDL):一个没人注意的长事务把 DDL 卡在队列里,而排队的 DDL 又让后面需要申请冲突 MDL 的语句跟着排队。

这次我用三个会话把它完整复现了一遍,拿到了从"持锁"到"排队"再到"恢复"的每一秒。

标题里的「拖死全表」先澄清:不是说整张 InnoDB 表一直处于排他锁定状态,而是待处理的 DDL 锁请求会让后续访问同一对象、需要申请冲突 MDL 的语句排队。本文实际观测到的例子只有一条 SELECT COUNT(*),不足以推广到"所有查询"。

〇、一分钟带走版

在本次实验中,未提交事务访问目标表后,事务结束前仍持有该表的元数据锁。ALTER 等待目标表所需的锁时,后续访问该表、需要申请冲突 MDL 的查询也可能排在它后面。实测本轮 ALTER 等待约 19.76 秒,SELECT COUNT(*) 等待约 17.76 秒;对照轮无对手的 DDL 耗时 0.48 秒。具体锁模式和 DDL 执行路径以本文给出的原始语句及 metadata_locks 采样为准。

治理两板斧:DDL 显式带 lock_wait_timeout(生产要短);处置前先从目标对象的 GRANTED 锁定位线程,再关联事务信息确认身份——取消 DDL 当前语句和结束持锁事务,风险不等价。

一、实验设计:三个会话,以及可复算的语句

环境与对象(全部来自采集前的正控查询,非事后补写):VERSION() = 8.4.11,单实例 mysql-project-mysql,实验库 mysql_lab,目标表 1 行 InnoDB 小表。基线建表语句:

CREATE TABLE `lab_mdl_demo` (
  `id` bigint NOT NULL,
  `note` varchar(100) COLLATE utf8mb4_general_ci NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

三个会话实际执行的语句(顺序与错开的秒数由脚本控制,非并发抢跑):

会话 语句 发起时刻(相对 t+0)
A START TRANSACTION; SELECT id, note FROM lab_mdl_demo WHERE id=1; SELECT SLEEP(20); t+0.0
B SET SESSION lock_wait_timeout=60; ALTER TABLE lab_mdl_demo ADD COLUMN extra_note VARCHAR(40) NULL; t+3.0
C SELECT COUNT(*) FROM lab_mdl_demo; t+5.0

关键不是 SLEEP(20) 本身:A 在事务中只做了一次一致性读取且始终未提交,该表的 SHARED_READ MDL 因此维持到事务结束;SLEEP(20) 只是把这段时间撑到可观测长度。这是本实验的最小复现条件,不外推为只读事务的普遍行为。

⚠ 本轮未显式指定 ALGORITHM 或 LOCK,也没有采集实际选用的算法字段;执行路径以现场锁采样为准,不能凭 ADD COLUMN 反推是否重建表。8.4 中部分 ADD COLUMN 可走 INSTANT,列位置与表定义都会影响结果。

三会话并存时的锁采样语句(这一条也是下面 §五 取证顺序的依据):

SELECT o.OBJECT_SCHEMA, o.OBJECT_NAME, o.LOCK_TYPE, o.LOCK_DURATION,
       o.LOCK_STATUS, o.OWNER_THREAD_ID,
       t.PROCESSLIST_ID, LEFT(t.PROCESSLIST_INFO, 60) AS info
FROM performance_schema.metadata_locks o
LEFT JOIN performance_schema.threads t ON o.OWNER_THREAD_ID = t.THREAD_ID
WHERE o.OBJECT_SCHEMA = 'mysql_lab'
ORDER BY o.LOCK_STATUS, t.PROCESSLIST_ID;

注意 PROCESSLIST_ID 来自 threads,不是 metadata_locks 的列(8.4 的列名正控见 §五)。

二、完整时间线:相对时刻与原始时间戳

相对时刻(秒) 事件 原始时间戳(UTC)
t+0.0 A 起事务、读表、开始 SLEEP(20) 15:13:26.962
t+3.0 B 发起 ALTER(lock_wait_timeout=60)——进入等待 15:13:29.971
t+5.0 C 发起普通 SELECT COUNT(*)——也进入等待 15:13:31.978
t+7.4 采样现场:三会话并存,锁表出现 2 条 PENDING 15:13:34.356
t+20.4 A 的 SLEEP 到点,事务结束,锁释放 15:13:47.322
t+22.8 B 的 ALTER 执行成功 15:13:49.733
t+22.8 C 的 COUNT 返回 15:13:49.737

两个口径要说清楚:相对时刻是四舍五入到一位小数的展示值(t+0.0 = 15:13:26.962);等待时长用原始时间戳相减得出,非仪器直读:

  • B:49.733 − 29.971 = 19.762 → 19.76 秒
  • C:49.737 − 31.978 = 17.759 → 17.76 秒
  • A 释放到 B 完成:49.733 − 47.322 = 2.411 → 2.4 秒(不是从 20.7 到 22.8 相减的 2.1;表格里 A 的时刻按原始时间戳应为 t+20.4)

对照轮(运行二):先开事务立刻 COMMIT,再发 ALTER TABLE lab_mdl_demo DROP COLUMN extra_note;,15:13:50.825 → 15:13:51.309 = 0.484 → 0.48 秒。

⚠ 两轮不构成单变量对照:19.76 ÷ 0.48 ≈ 41.17 倍,但对照轮是回滚用的 DROP COLUMN,与实验轮的 ADD COLUMN 不是同一条语句,表结构状态也不同(对照轮起始已带 extra_note 列)。两轮只有「有无持锁对手」这一个条件被刻意改变,DDL 本身没有对齐 ⇒ 这个比值只是本次两轮观测的量级参照,不是单变量因果,不能写成「长事务让 DDL 慢 40 倍」。要拿到干净的倍数,得让对照轮跑同一条 ADD COLUMN 并复位表结构后重测,本轮没做。

fig1-三会话时间线.png

三、六行锁表:为什么连 SELECT 都被挡了

三会话并存时(15:13:34.356)的原始采样,6 行。OWNER_THREAD_ID 是 metadata_locks 自带列,PROCESSLIST_ID 由 §一 那条 JOIN 从 threads 带出来:

持有者 OWNER_THREAD_ID → PROCESSLIST_ID 对象 锁类型 DURATION 状态
A(持锁方) 58 → 15 lab_mdl_demo SHARED_READ TRANSACTION GRANTED
B(DDL) 60 → 17 —(非表级,OBJECT_NAME=NULL) INTENTION_EXCLUSIVE TRANSACTION GRANTED
B(DDL) 60 → 17 lab_mdl_demo SHARED_UPGRADABLE TRANSACTION GRANTED
B(DDL) 60 → 17 #sql-1_11 EXCLUSIVE STATEMENT GRANTED
B(DDL) 60 → 17 lab_mdl_demo EXCLUSIVE TRANSACTION PENDING
C(查询) 61 → 18 lab_mdl_demo SHARED_READ TRANSACTION PENDING

这六行里最有价值的是最后两行:C 只需要 SHARED_READ(和 A 一样的共享读锁),却被挡住了——因为 B 的 EXCLUSIVE 请求已经在队列里且排在 C 前面。MDL 队列的公平性在这里变成了杀伤力:一个卡住的 DDL,会让排在它后面、访问同一目标对象且需要申请冲突 MDL 的语句一起排队。本文观测到的只有 C 这一条 COUNT。

第 4 行的 #sql-1_11 上有一把 EXCLUSIVE 锁票据,说明 ALTER 已经进入自身的执行阶段。⚠ 但不能据此说「在线拷表已经开始」:本轮未指定 ALGORITHM、没有采集算法字段,也没做 INSTANT/INPLACE/COPY 的显式对照,#sql-* 与算法的对应关系在原始记录里本就列为「未验证假设」。内部临时表锁票据只登记为现象,不外推为普遍机制。

同样地,本实验只有 A/B/C 三个会话,没有单独的写入会话,也没有测量并发 DML 的推进情况。所以这里能写的只有一句:DDL 等待目标表的元数据锁,后续 COUNT 也进入等待;"DML 在持续推进"这类三层排队的说法,本轮没有数据支撑。

fig2-六行锁表.png

四、lock_wait_timeout:本轮参数与超时边界

本轮 B 的 lock_wait_timeout 全程为 60,脚本里只有这一轮;执行卡文档中的 30 只是示例值,从未实际执行。因此不存在「设 30 秒 → B 在 A 释放前超时 → 只观察到超时分支」这一轮观测。lock_wait_timeout 作用于等待中的语句(本轮是 B 的 ALTER),不会终止持有锁的 A;它只决定这条 DDL 在队列中等待多久后退出。

按时间戳复算也能排除这种可能:B 在 t+3.0 发起、A 在 t+20.4 释放,若真设 30,B 要到 t+33.0 才超时,会先等到锁释放,不会先超时。本轮参数与生产建议如下:

  • 本轮实际参数:A = SLEEP(20),B = lock_wait_timeout=60。取 60 是给 20 秒持锁 + 采样开销留余量,避免卡在超时边界。
  • 另一次重跑是采样失败,别混:真正重跑过的失败只有"列名写错"那一次(§五 的 ERROR 1054),失败原始文件保留没删;它与超时参数无关。
  • 生产上的方向仍然相反:演练里超时要设得比持锁方长(才看得到完整链路),生产里 lock_wait_timeout 恰恰要设短——别让 DDL 长期挂在队列头上拖死后继查询,宁可快速失败、错峰重试。差别取决于你要"观察"还是"止损"。

五、诊断:怎么找到那个长事务

先把列名量准。采集前查 information_schema.columns 得到 8.4.11 的 metadata_locks 11 个列:OBJECT_TYPE / OBJECT_SCHEMA / OBJECT_NAME / COLUMN_NAME / OBJECT_INSTANCE_BEGIN / LOCK_TYPE / LOCK_DURATION / LOCK_STATUS / SOURCE / OWNER_THREAD_ID / OWNER_EVENT_ID——没有 PROCESSLIST_ID,也没有 SQL 文本列。所以取证必须走 JOIN,顺序如下:

  1. 在 metadata_locks 里按目标对象筛 LOCK_STATUS='GRANTED' 的行,取 OWNER_THREAD_ID(本轮 A = 58,B = 60,C = 61);
  2. 用 o.OWNER_THREAD_ID = t.THREAD_ID 关联 performance_schema.threads,取 PROCESSLIST_ID(15/17/18)、PROCESSLIST_INFO、连接来源;
  3. 用 innodb_trx.trx_mysql_thread_id = PROCESSLIST_ID 关联事务,看 trx_started、trx_rows_modified 与身份信息,确认它到底是什么、跑了多久。

「按最早 trx_started 找持锁方」只能当线索:最早的事务不一定访问了目标表,也不一定持有那把 MDL。正确顺序是先从目标对象的 GRANTED 锁定位线程,再回查事务信息核实,不能反过来用事务年龄指认持锁方。

还有两个容易骗到自己的地方:

  • PROCESSLIST_INFO 是当前语句,可能为空或已变化,不能当历史语句记录。本轮 A 的 info 显示的是 SELECT SLEEP(20),而真正让它持锁的是前面那条读表 SELECT——如果只读 info,会以为持锁方只是在睡觉。
  • 会话 ID 每次重跑都变(另一轮是 14/16/17,本轮是 15/17/18)。Runbook 里要写死的不是 ID,而是「目标对象 GRANTED 锁 → OWNER_THREAD_ID → PROCESSLIST_ID → trx_mysql_thread_id + trx_started + 连接来源」这条取证链。

采集前先量探针,且结论只限本次实例:在这个 8.4.11 实验实例上,setup_instruments 里 wait/lock/metadata/sql/mdl 实测 ENABLED=YES / TIMED=YES;setup_consumers WHERE NAME LIKE '%metadata%' 返回 Empty set,即 metadata_locks 不受 consumer 开关控制,旧资料里"先打开 metadata consumer"这一步不存在。⚠ 这是本次实验实例采集时的结果,不代表所有 8.4 实例都必然相同,换环境仍要自己量一遍。

第一次采样按旧写法查 o.STATUS,吃了 ERROR 1054 Unknown column 'o.STATUS';8.4 的正确列名是 LOCK_STATUS。脚本修正后重跑,本文所有锁表读数都来自修正后那次采样——失败那次的原始文件保留没删,记录的是结论怎么改过来的,而不是凭空冒出来的正确结果。

所谓"排查经验",很大一部分是先验证你手里的探针本身可信。连列名和开关都没量过,读到的空白就可能被误读成"没有锁"。

六、治理:取消 DDL 和结束事务,风险不等价

本次演练未使用任何 KILL——靠 A 的 SLEEP 自然结束,观察 B、C 立即恢复。所以下面这张表是按 InnoDB 语义给出的生产推演,不是本轮实测:

处置 具体动作 损失与边界 适用
取消 DDL 当前语句 先评估 KILL QUERY <id>:中断当前语句、保留连接 只损失这次变更,可重放 持锁方是核心业务事务时优先
结束持锁事务 确认身份后提交 / 回滚;或 KILL CONNECTION <id>(等价 KILL <id>)终止连接 未提交写入全部回滚,损失由业务承担 确认是僵尸 / 调试会话时才用

⚠ KILL QUERY 只中断当前语句,不等于提交或回滚事务:当前语句结束后事务仍可能保持打开,MDL 也可能继续持有。只有连接结束或事务显式提交 / 回滚,锁才真正释放。

生产 Runbook 第一步永远是按 §五 的取证链确认身份——不确认身份就复制粘贴 KILL <id>,是这类事故处置里最危险的动作。另补一条:不要盲目自动重试 DDL;先确认上次语句已结束、锁队列已消退,再限速或退避重试,否则重试本身会再造一次排队。

fig3-治理与探针.png

七、局限

  • 本实验为 1 行小表 + 本地单实例 8.4.11,非生产环境;MDL 是实例级元数据锁,本轮不涉及主从复制行为,排队行为在表规模、并发度、拓扑下可能有差异
  • 持锁方只做了一致性读:A 是"读一次表 + SLEEP(20)",无业务写入、无行锁、无 undo 代价;§六 的风险表是推演,本次未实测 KILL 一个有未提交写入的业务事务
  • 只有三个会话,没有并发写入会话 ⇒ 未测量 DML 推进;"三层排队"不外推
  • 未显式指定 ALGORITHM/LOCK,也未采集实际算法字段;未覆盖 INSTANT/INPLACE/COPY 显式对照、gh-ost/pt-osc、8.0/8.4 跨版本调度差异。#sql-1_11 只登记为现象,不推断拷表
  • 对照轮是 DROP COLUMN 回滚动作,与实验轮 ADD COLUMN 不同语句、表结构状态不同 ⇒ 0.48s 与 19.76s 不构成单变量对照,倍数只作量级参照、不作因果结论
  • 等待时长由时间戳相减、非仪器直读;相对时刻为四舍五入展示值,原始 UTC 时间戳见 §二;会话 ID 每次重跑都变
  • 探针结论(instrument 默认开启、无 metadata consumer)仅限本次实验实例,不得外推所有 8.4 部署
  • 镜像 digest 未记录(流程未覆盖项);PROCESSLIST_INFO 是当前语句快照,不作为历史语句证据
  • 官方参考(按条目自查,不在正文外链):MySQL 8.4 Reference Manual 的 The metadata_locks Table、Metadata Locking、InnoDB Online DDL Operations、KILL Statement 四节

系列目录(本站陆续上架)

篇 主题
1 Redis String 三种编码实测:别再死背 44 字节
2 把最重的库改回同步,QPS 反而涨了 38%
3 Agent 工程化实测:可观测/舱壁/熔断
4 RAG 评测三层反转
5 AI Agent 落地全景
6 线程池到底设多大?4 台机器实测
7 Redis 编码压测 59 轮(本站已上架)
8 本文:一条 ALTER 拖死全表

本站内容陆续上架,规划版目录仅标示系列顺序,非全量互链。

相关文章
|
5天前
|
Linux Go 开发工具
手机上跑 DeepSeek 智能体:开源安卓客户端 DSH Go 安装与使用指南
DSH Go 是一款开源安卓客户端,将 DeepSeek Harness 智能体引擎装进手机:免 Root、免 Termux,一键安装即用;支持本机运行(内置 Ubuntu+Node.js)或远程接管电脑会话;具备插话打断、过程折叠、屏幕操控、端到端加密分享等特色功能,真正实现智能体“口袋化”。
6848 15
|
5天前
|
弹性计算 人工智能 API
Codex超详细入门指南(下载·安装·登录·上手,全版本覆盖,2026最新版)
建议收藏本教程,2026 全年各版本更新随时回来查阅。如果你在搭建过程中遇到任何问题,欢迎结合 阿里云工单系统 提交技术支持请求,或在 阿里云开发者社区 搜索 Codex 相关话题交流。 祝你编码愉快,效率翻倍!
2293 8
|
8天前
|
人工智能 前端开发 API
阿里云Qwen4-Max 大模型详解:定位、特征、能力、适用场景与相对Qwen3-Max差异解析
2026年11月11日,阿里发布第四代旗舰模型Qwen4-Max预览版,首发千问AI平台Token Plan并接入千问办公、Qoder等入口。该版本聚焦知识理解、世界感知、编程与Agentic四项升级,主打复杂编程、专业办公、前端设计与专业软件操作等真实任务。预览期为11月11日10:00至11月25日24:00,仅支持Token Plan调用,到期后模型标识下线;限时2折、新用户再享5折,最低1折可体验。
|
8天前
|
JSON 前端开发 API
阿里云最新Qwen4-Max大模型介绍:模型定位、特征、能力、适用场景解析
2026 年 11 月 11 日,阿里发布第四代旗舰模型Qwen4-Max预览版,首发千问 AI 平台 Token Plan,并同步接入千问办公、Qoder 等入口。该版本聚焦知识理解、世界感知、编程与 Agentic 四项升级,主打复杂编程、专业办公、前端设计与专业软件操作。预览期至 11 月 25 日 24:00,到期后模型标识下线;限时 2 折、新用户再享 5 折,最低 1 折可体验。
|
3天前
|
传感器 安全 区块链
Windows 电脑运行安卓应用教程:开源工具 ApkStart 双击 APK 即装、独立窗口运行
ApkStart 是一款开源Windows工具,让安卓APP像原生软件一样运行:双击APK即安装、生成真实图标、独立窗口启动、开始菜单可搜索。无需传统模拟器,内置QEMU运行时,支持数据互通、ARM兼容与命令行操作。(239字)
|
21天前
|
Linux 开发工具 iOS开发
Codex保姆级教程(下载+安装+登录+使用,含所有版本,2026最新)
Codex是OpenAI推出的开源AI编程智能体,本地运行,可理解整个代码库,支持自然语言指令改代码、执行命令、跑测试、修Bug,兼容Win/macOS/Linux。(239字)
10168 59
|
4天前
|
人工智能 弹性计算 开发工具
零成本玩各大模型!DeepSeek Harness dsh-our-free-model 插件,13 个厂商模型直接用
总结一下:装这个插件,你不用注册、不用绑卡、不用填一个 Key,就能在 DeepSeek Harness 里用上 DeepSeek V4.1 Flash、Kimi K3 这一批前沿模型,而且是白嫖的、还不限量。三步搞定:下载插件,复制包含 `package.json` 的插件目录路径,在「添加插件」里粘贴路径安装并重启。无论是在阿里云 ECS 上通过命令行部署的 Web 版,还是本地的 Electron 桌面端,都能顺利跑通,快速搭建一套零成本的免费模型使用环境。
1047 0
|
7天前
|
Linux API iOS开发
【DSH】DeepSeek Harness新手保姆教程(下载+安装+配置+使用,2026最新实测)
DeepSeek Harness(dsh)是DeepSeek AI开源的智能体框架,秉持“一切皆插件”理念,支持读文件、改代码、跑命令、处理表格、联网搜索等操作。免费开源,兼容Windows/macOS/Linux,提供桌面端与网页端,可灵活安装插件扩展能力。
|
15天前
|
Linux 开发工具 iOS开发
2026最新codex下载安装保姆级使用教程(下载+安装+登录+使用,含所有历史版本)
本文详解OpenAI Codex国内快速上手指南:提供Win/macOS/Linux全平台免安装包、npm/Homebrew等多方式安装、账号/API Key双登录、简体中文切换,覆盖下载、安装、汉化、使用及高频避坑(如微软商店失败、npm超时等),10分钟即可跑通。
3481 9