前事不忘,后事之师,不忘国耻!

 找回密码
 用户注册
搜索
查看: 6|回复: 0

[开发应用] MySQL 元数据锁 MDL 治理实战:从一条睡眠事务堵死全表到短超时 DDL 与队列监控双线收敛

[复制链接]
发表于 4 小时前 | 显示全部楼层 |阅读模式

马上注册,结交更多好友,享用更多功能,让你轻松玩转社区。

您需要 登录 才可以下载或查看,没有账号?用户注册

×
MySQL 元数据锁 MDL 治理实战:从一条睡眠事务堵死全表到短超时 DDL 与队列监控双线收敛


一、具体的问题

一家电商平台的订单表 orders 半夜上线一个加列操作,语句写得很规范:ALTER TABLE orders ADD COLUMN remark VARCHAR(200), ALGORITHM=INPLACE, LOCK=NONE。开发预期是"在线 DDL,不锁表",结果四分钟后接口全线超时,SHOW FULL PROCESSLIST 里九百多个线程挤在同一状态:Waiting for table metadata lock。连接池先耗尽,接着报 too many connections,业务等于停摆。

最诡异的是堵住它的那个会话: Command 是 Sleep,什么都不在跑,坐在那里已经二十分钟。ALGORITHM=INPLACE、LOCK=NONE 一个没写错,可 ALTER 确实没开始执行。这不是行锁问题——innodb_lock_wait_timeout 默认 50 秒管的是 InnoDB 行锁,管不到这里;真正的原因是元数据锁(MDL)的排队规则,而 MDL 等待超时参数 lock_wait_timeout 的默认值是 31536000 秒,整整一年。也就是说,堵在上面的 ALTER 会一直排下去,排到堵它的事务提交为止。本文把定位、止血、改造、监控四步说透。

二、核心原理

1. MDL 保护的是表定义,生命周期到事务结束为止。 MDL 是 server 层的锁,和 InnoDB 行锁不在一个层面,覆盖范围也不止表:schema、存储过程、触发器、定时事件、表空间、GET_LOCK() 拿的用户级锁,全在 MDL 管辖之内。普通 SELECT 拿 SHARED_READ,增删改拿 SHARED_WRITE,这两类彼此兼容;ALTER、DROP、RENAME、TRUNCATE 拿 EXCLUSIVE,与所有类型冲突。关键设计是:自动提交打开时锁随语句释放;一旦显式 BEGIN,共享锁也要攥到 COMMIT 或 ROLLBACK。所以危险的不是"跑得久的查询"——自动提交下一条查询跑完锁就没了,而是"开着事务睡觉的会话":事务里只读过一次表,然后 idle 半小时,共享锁就一直不还。

2. 排队规则决定雪崩形态:后来者不许插队。 一个请求能否被授予,不只看与已持有锁是否兼容,还要看队列里有没有排在它前面的待授予请求。ALTER 在队列里挂着一个未授予的排他请求后,之后到达的每一个 SELECT 都必须排在它后面——哪怕读读本来兼容。这是有意设计的防饿死机制:不让 DDL 被源源不断的读请求挤到永远拿不到锁。反面代价就是:一个 idle 事务 + 一个等待的 ALTER,等于全表断流。max_write_lock_count 是反向泄压阀:当连续授予的写锁请求数超过它时,一直被压着的读请求有机会插队,防止读被写饿死;默认值极大,生产上基本不触发,别指望它救场。

3. 在线 DDL 的 LOCK=NONE 只管执行阶段。 手册把在线 DDL 分三段:初始化阶段拿 SHARED_UPGRADABLE,保护当前表定义;执行阶段视引擎与算法决定是否短暂升级;提交阶段一定升级为排他锁换出旧表定义、写入新定义。ALGORITHM=INSTANT 只改数据字典不动数据,同样免不了这一瞬。所以"在线 DDL 不锁表"准确说法是:锁持有时间短,但拿锁那一刻如果队列里已经有堵点,ALTER 就停在真正动数据之前,拷表都还没开始。另外 LOCK 参数不能强行放宽:指定的级别比该操作允许的更松时语句直接报错,不会悄悄降级成表拷贝。

4. 两个超时参数各管一段。 lock_wait_timeout 管 MDL 等待,默认 31536000 秒,全局与会话级可动态改;innodb_lock_wait_timeout 管 InnoDB 行锁,默认 50 秒。想避免 DDL 挂在队列里排一年,正确做法是在执行 DDL 的会话里把 lock_wait_timeout 设小,失败快速退出再重试,而不是排大队。

三、实例参考(动手步骤)

步骤 0:确认现状基线。 先量化雪崩规模,别急着猜:
  1. SELECT COUNT(*) FROM performance_schema.threads
  2. WHERE PROCESSLIST_INFO LIKE 'Waiting for table metadata lock%';
  3. SELECT VARIABLE_VALUE FROM performance_schema.global_status
  4. WHERE VARIABLE_NAME IN ('Threads_connected','Threads_running');
复制代码

特征签名:Threads_connected 陡增而 Threads_running 平稳——全是等着,没有在算的,九成是 MDL 队列雪崩。

步骤 1:定位堵点。 8.0 起 MDL instrument 默认开启,5.7 需手动启用 instrument 后才能查。直接用 sys 视图一步到位:
  1. SELECT blocking_pid, blocking_user, blocking_trx_id,
  2.        blocking_lock_mode, sql_kill_blocking_connection
  3.   FROM sys.schema_table_lock_waits;
复制代码

输出里 blocking_pid 就是堵住队列的连接号。再用它去 information_schema.innodb_trx 核对事务起始时间:SELECT trx_started FROM information_schema.innodb_trx WHERE trx_mysql_thread_id=blocking_pid;,看这个事务已经开了多久。sys 视图不可用的场合(sys 插件被卸载或版本过旧)退回手工三层:先从 metadata_locks 里挑 LOCK_STATUS='PENDING' 的行拿到 OWNER_THREAD_ID,再用 performance_schema.threads 换算成 PROCESSLIST_ID,最后拿这个连接号去 innodb_trx 与 processlist 对齐。注意一个坑:metadata_locks.OWNER_THREAD_ID 是 performance_schema 线程号,不等于 processlist 的 Id,换算必须过 threads 表。

步骤 2:止血。 堵点几乎都是 Sleep 状态的空闲事务——trx_query 为 NULL,KILL QUERY 无效,必须杀连接:KILL <blocking_pid>;(等价 KILL CONNECTION)。连接断开服务器回滚未提交事务,共享锁释放,队列放行,等锁线程应在一两秒内清空,业务随之恢复。杀之前确认两点:一是堵点事务是否已经跑了实际业务写入,杀掉意味着回滚半小时工作量,需要权衡;二是它是否为备份、同步类长任务,误杀会造成数据链路中断——雪崩在扩大时通常没有第二种选择,但至少要留痕。

步骤 3:DDL 安全化改造。 所有 DDL 会话统一加短超时,拿不到锁 3 秒退出、循环重试,绝不排大队:
  1. SET SESSION lock_wait_timeout = 3;
  2. ALTER TABLE orders ADD COLUMN remark VARCHAR(200),
  3.   ALGORITHM=INPLACE, LOCK=NONE;
复制代码

拿不到锁就报 ERROR 1205,脚本 sleep 几秒重试;重复失败说明表上仍有长事务,先处理事务再上 DDL。全局改 lock_wait_timeout 只对新建连接生效,对存量连接无效,所以必须会话级设。

步骤 4:源头治理。 三件事:一是应用侧审计,查 ORM 是否默认 SET autocommit=0,事务里"读一下然后闲置"的代码路径全部收掉,事务最长时长限到 30 秒内;二是备份与巡检工具自查,mysqldump 的 FLUSH TABLES WITH READ LOCK、以及任何在事务里摸过表的脚本都是潜在堵点;三是大表 DDL 一律走低峰期加 pt-online-schema-change,别和业务流量赌排队。

步骤 5:监控闭环。 每 30 秒两个探针:metadata_locks 里 LOCK_STATUS='PENDING' 且 LOCK_TYPE='EXCLUSIVE' 的计数、threads 表里等 MDL 的线程数,任一连续两次超过 5 就告警。再加一条连接数爬升告警:Threads_connected 五分钟内涨幅超过基线的 30% 且 Threads_running 没跟着涨,按 MDL 雪崩预案处置,而不是按连接泄漏处理。三条线配合,队列刚成型就能被发现,远早于连接池耗尽。

治理前后对比

指标治理前治理后
DDL 卡在队列挂死直至事务提交(可数小时)3 秒快速失败,重试到可执行
等锁线程峰值900+0(告警线 5)
排队雪崩事故每次上线必中近三个月 0 次
定位堵点耗时40 分钟靠猜30 秒查 sys 视图
Threads_connected 峰值890,连接池耗尽平稳无爬升
最长未提交事务22 分钟<30 秒
too many connections 报错每周 2-3 次0


四、实操检查清单


  • 确认 lock_wait_timeout 当前值,DDL 会话统一会话级设为 3-10 秒,禁止依赖默认值
  • 8.0 核对 MDL instrument 开启;5.7 先启用 wait/lock/metadata/sql/mdl 再查 metadata_locks
  • 所有 DDL 上线前先跑 sys.schema_table_lock_waits 确认队列为空
  • 监控双探针:PENDING 排他锁计数 + 等表元数据锁线程数,超 5 告警
  • 审计应用连接的 autocommit 与事务最长时长,idle in transaction 超时强制断开
  • 大表 DDL 走 pt-online-schema-change 或低峰窗口,禁止白天裸 ALTER
  • 告警文案区分"读阻塞读"伪命题:堵点一定是长事务 + 排队 ALTER 的组合
  • KILL 前确认堵点事务无未回滚不可的业务写入
  • DDL 脚本带重试与失败退出,不允许排队式等待
  • 定期导出 innodb_trx 中 trx_started 超过 60 秒的事务清单,归档跟踪高频来源
  • KILL 操作记录堵点 pid、事务时长、归属应用,事后复盘归属代码路径


几个容易踩的坑

坑一:杀会话用 KILL QUERY。 堵点会话是 Sleep,没有正在执行的语句,KILL QUERY 什么都不干,必须 KILL CONNECTION(KILL id 默认即是)。杀完再看 processlist,等锁线程应在一两秒内清空。

坑二:线程号混用。 metadata_locks 的 OWNER_THREAD_ID 与 SHOW PROCESSLIST 的 Id 是两个体系,直接拿去 KILL 会杀错人。用 performance_schema.threads 按 THREAD_ID 查 PROCESSLIST_ID,或干脆用 sys 视图给出的 blocking_pid。

坑三:以为 autocommit=1 就安全。 框架连接池常见"连接初始化 SET autocommit=0",每条查询自动落进显式事务,读完不提交就是空闲持锁。要用 SHOW VARIABLES 逐连接核对,别只看服务端默认值。

坑四:以为 INSTANT 零锁。 ALGORITHM=INSTANT 只是不拷数据,提交阶段仍要短暂排他锁。堵点在先,一样全表断流。

坑五:改了全局没生效。 SET GLOBAL 只影响新连接,存量连接各自带着会话级副本。正确做法是 DDL 脚本里显式 SET SESSION。

坑六:5.7 只开了 consumer。 5.7 的 global_instruments consumer 默认开,但 MDL instrument 本身默认关,只开 consumer 查出来永远是空表。

坑七:把排队当成 bug。 pending 排他锁挡住后来的读请求是防饿死的设计行为,max_write_lock_count 默认大到不触发。治理方向是消灭长事务与空闲持锁,不是改锁规则。

坑八:拿 innodb_lock_wait_timeout 说事。 它管行锁,MDL 一秒都不归它管。报表里"锁超时 50 秒"的配置给不出任何保护。

坑九:只盯 Threads_running。 雪崩时 running 平稳、connected 陡增,纯负载告警看不见它。签名必须两条曲线一起看。

坑十:指望 Info 字段找堵点。 SHOW PROCESSLIST 里等锁的线程 Info 显示的是各自正在跑的语句,堵点会话却是 Sleep 且 Info 为空——从等待者列表里永远找不到元凶,必须反着查:先抓 PENDING 的排他锁,再顺着锁找持有者。方向搞反,排查半小时一无所获。
免责申明1、欢迎访问本站,本文内容及相关资源来源于网络,版权归版权方所有!本站原创内容版权归本站所有,请勿转载!
2、本文内容仅代表作者观点,不代表本站立场,作者自负,本站资源仅供学习研究,请勿非法使用,否则后果自负!请下载后24小时内删除!
3、本文内容,包括但不限于源码、文字、图片等,仅供参考。本站不对其安全性,正确性等作出保证。但本站会尽量审核会员发表的内容。
4、如本帖侵犯到任何版权问题,请立即告知本站 ,本站将及时删除并致以最深的歉意!客服邮箱:admin@dbabbs.com
您需要登录后才可以回帖 登录 | 用户注册

本版积分规则

Archiver|小黑屋|DBA论坛中国 ( 鲁ICP备20017503号-2 )

GMT+8, 2026-11-19 10:27 , Processed in 0.028029 second(s), 9 queries , MemCached On.

Powered by Discuz! X5.0

© 2001-2026 Discuz! Team.

快速回复 返回顶部 返回列表