一次慢改表引发的线上死锁事故复盘

一次慢改表引发的线上死锁事故复盘一、事故背景在一次常规的数据库表结构变更过程中对某核心业务表执行了慢改表操作使用 pt-online-schema-change。操作开始后短时间内触发报警部分接口响应时间显著上升出现请求超时影响约千级请求整个故障持续时间较短约几十秒通过手动干预终止异常事务后恢复。二、事故经过简要11:22:38执行慢改表11:22:40发现接口延迟升高11:22:45尝试中断操作但响应延迟11:22:50排查数据库发现长事务并手动终止11:23:09系统恢复正常三、直接原因在执行慢改表前未检查数据库中是否存在长事务导致慢改表过程中无法获取必要的锁资源从而进入锁等待最终引发类似死锁的阻塞现象。四、什么是“慢改表”在 MySQL 中对大表执行 DDLData Definition Language 数据定义语言如ALTER TABLE通常会带来严重问题表锁metadata lock数据重建业务写入阻塞因此在生产环境中通常采用在线变更方案例如pt-online-schema-change其核心思路是创建新表目标结构在旧表上创建 trigger同步增量数据分批拷贝历史数据最终通过 rename 完成切换整个过程避免了长时间锁表实现“在线变更”。五、关键知识点Metadata LockMDL这是本次事故的核心。MySQL 在访问表时会自动加metadata lock元数据锁普通查询SELECT → 持有MDL读锁DDL操作如 ALTER / CREATE TRIGGER → 需要MDL写锁锁冲突规则读锁共享可以并存 写锁排他必须等待所有读锁释放六、事故根因分析关键1. 长事务的存在系统中存在未提交的事务例如BEGIN;SELECT*FROMtable;-- 未提交该事务会长期持有MDL读锁2. 慢改表的行为pt-online-schema-change 在执行过程中需要创建 triggerDDL操作 → 需要MDL写锁3. 冲突产生此时出现操作锁长事务MDL读锁pt-oscMDL写锁等待中pt-osc 被阻塞4. 为什么会影响业务请求问题并不会停在“阻塞”这一层而是继续扩大pt-osc 已经占用部分资源连接、锁等新的业务请求进入数据库请求开始排队等待锁最终表现为请求RTResponse Time 响应时间升高接口超时系统报警七、为什么“Ctrl C”无法立即中断这是一个常见误区。MySQL 中DDL操作如创建 trigger正在等待锁的线程不会立即响应中断信号因此Ctrl C 可能延迟甚至无效必须通过KILL或处理阻塞源长事务八、正确的操作流程慢改表标准 Playbook为了避免类似问题再次发生所有线上慢改表操作必须遵循标准化流程。该流程不仅包含“是否能执行”的判断还包括“如何安全执行”。1. 执行前检查强制步骤1检查长事务SELECTtrx_id,trx_started,trx_mysql_thread_id,trx_queryFROMinformation_schema.innodb_trx;重点关注事务运行时间trx_started30秒需要评估风险60秒必须处理SQL内容trx_query全表扫描大查询未提交事务若存在长事务优先联系业务方确认紧急情况下可执行KILLthread_id;2检查锁等待情况SHOWPROCESSLIST;或SHOWENGINEINNODBSTATUS;确保当前数据库没有异常锁竞争3检查表结构SHOWCREATETABLEyour_table;确认表是否存在主键必须是否已有 triggerpt-osc 不支持2. 执行慢改表标准命令推荐使用 pt-online-schema-change 工具。示例命令pt-online-schema-change\--host127.0.0.1\--port3306\--userxxx\--passwordxxx\--databaseyour_db\--tableyour_table\--alterADD COLUMN new_col INT DEFAULT 0\--chunk-size1000\--max-loadThreads_running25\--critical-loadThreads_running50\--sleep0.1\--set-varsinnodb_lock_wait_timeout50\--print\--execute关键参数说明--alter要执行的DDL语句--chunk-size分批拷贝的数据量影响性能与风险--max-load数据库负载阈值超过自动暂停--critical-load极限负载超过直接终止--sleep每批之间的休眠时间用于降压--print输出执行过程建议开启3. 执行过程监控在慢改表执行期间需要持续观察系统状态1观察执行进度pt-osc 会输出当前进度如 10%、20%2观察数据库负载SHOWPROCESSLIST;关注Threads_running是否出现大量 Waiting 状态3异常处理若出现卡顿或异常检查是否有新产生的长事务必要时终止异常连接无法恢复时中断操作Ctrl C 或 KILL4. 执行完成后的确认1确认数据一致性SELECTCOUNT(*)FROMyour_table;2确认业务状态接口响应时间RT错误率3旧表处理慢改表会保留旧表如_old表DROPTABLE_old_table;建议保留一段时间后再删除用于回滚兜底5. 总结慢改表并不是“无风险操作”其安全性依赖于执行前的环境检查尤其是长事务执行中的负载控制执行后的完整验证任何一步缺失都可能导致线上故障。九、总结这次事故的本质可以归结为一句话在线DDL虽然避免了长时间锁表但仍依赖 metadata lock。一旦存在未提交的长事务会阻塞DDL获取锁进而引发锁等待甚至死锁最终影响业务请求。十、可沉淀的工程经验所有线上DDL必须有前置检查步骤长事务是线上数据库的隐形杀手不要相信“慢改表一定安全”必须建立标准化 playbook 并严格执行十一、后续优化方向建立慢改表执行 checklist强制流程增加长事务监控与报警自动化检测脚本执行前校验低峰期执行变更操作