1. 事故还原凌晨的连接数告警是怎么一路打到我这的先说结论这不是一条慢查询把数据库打挂的故事而是一张业务表被锁住之后所有线程挤在锁等待上把连接池和MySQL的连接数一起榨干的连锁事故。整个过程从出现第一个异常到数据库完全不可用大概只用了40分钟。那天凌晨2点17分值班手机开始响。先是Zabbix告警MySQL连接数超过3000紧接着业务方在群里刷屏——订单查询接口大面积超时后台管理系统登录直接转圈。我爬起来连上跳板机第一眼看到的监控曲线是这样活跃连接数从平时800左右直接拉满到max_connections上限3100QPS却掉了三分之二。这个组合非常反常。正常来说连接数涨、QPS应该跟着涨或者至少持平结果QPS跌了说明大量连接并不是在正常执行SQL而是堵在某个环节上。我马上想到两种情况要么是连接池配置被改爆了要么是大量会话在等待某个资源。登录到MySQL实例上执行了SHOW PROCESSLIST结果很直接——一屏全是Waiting for table metadata lock配合几百条SELECT堆积。也就是说表被锁了后来的读写全部进等待队列连接越堆越多最终撑满连接数上限。这里先解释一下元数据锁MDL的概念因为后面所有的排查和救火都是围绕它展开的。MySQL从5.5开始引入MDL机制目的是保护表结构定义预防DDL语句和DML语句同时修改表结构时出现不一致。任何一条SQL在开始执行前都需要先获取对应表的MDL读锁或写锁。读锁之间共享读锁和写锁互斥写锁之间也互斥。一条长时间挂起的DDL或者一个忘了提交的事务都可能把整张表的读写全堵死。8.0版本里MDL的等待被记录在performance_schema.metadata_locks表中这也成了这次事故定位的关键依据。如果你在工单或者群里看到类似的报错——Lock wait timeout exceeded、Waiting for table metadata lock、Too many connections——大概率就是我这次遇到的情况或者离它不远了。这篇文章我把整个事故的定位链路、连接打满的深层次原因、还有事后怎么做防御性改造一次性讲清楚。2. 定位锁源头从sys库到information_schema的完整取证链2.1 第一步先分清楚自己是哪种“锁”很多人一看到锁就慌先把innodb_lock_wait_timeout调大或者直接KILL几个进程。但不同的锁处理方式完全不同乱操作反而会让事态恶化。从SHOW PROCESSLIST的输出看我需要先区分当前是行锁等待还是MDL锁等待。如果是行锁等待会话状态通常显示为Waiting for handler commit或者Lock wait timeout exceeded相关信息主要落在information_schema.innodb_lock_waits、performance_schema.data_locks和data_lock_waits这几张表上。如果是MDL锁等待会话状态会明确显示Waiting for table metadata lock关键信息落在performance_schema.metadata_locks表里。我这次看到的是清一色的Waiting for table metadata lock所以把主战场放在MDL锁上。这里有个经验之谈在MySQL 8.0里行锁和MDL锁的排查入口已经分家了别再像5.7时代那样只盯着information_schema那几张表8.0的performance_schema才是完整依据。2.2 用sys库视图快速锁定“谁堵住了路”定位MDL锁的sql其实很经典如果你上了MySQL 8.0可以直接查sys.schema_table_lock_waitsSELECT waiting_pid, waiting_query, blocking_pid, blocking_query, waiting_lock_type, blocking_lock_type FROM sys.schema_table_lock_waits WHERE object_schema your_db AND object_name your_table\G注意一点sys.schema_table_lock_waits是基于performance_schema.metadata_locks做的聚合视图如果performance_schema没开启或者metadata_locks采集项被关了这个视图查出来就是空的。排查前先确认一下SHOW VARIABLES LIKE performance_schema; SELECT * FROM performance_schema.setup_consumers WHERE name global_instrumentation; SELECT * FROM performance_schema.setup_instruments WHERE name LIKE wait/lock/metadata/sql/mdl;正常生产环境这两个都应该处于开启状态。如果没开尤其在8.0里我会建议直接开启性能损耗几乎可以忽略。查出来的结果大致是blocking_pid是某个长时间运行的DDL或者某个处于Sleep状态的连接。我那次看到的是一个blocking_pid始终没变query列为NULL但state显示Sleep的会话。这种会话十有八九是一个开启了事务但没提交的连接。2.3 锁等待关系反查拿着blocking_pid追溯事务源头当sys.schema_table_lock_waits给出blocking_pid后我再进一步看这个连接在干什么SELECT trx_id, trx_state, trx_started, trx_mysql_thread_id, trx_query, trx_rows_locked, trx_rows_modified FROM information_schema.innodb_trx WHERE trx_mysql_thread_id 阻塞的thread_id;这一步很关键因为MDL锁的持有者有时候不是正在执行的SQL而是一个“开小差”的事务。比如一个应用连接在autocommit0的情况下查了一条记录接着又去写日志或调外部接口事务一直不提交事务持有的MDL读锁也不会释放。这时候它的trx_query往往是NULL看起来人畜无害实际上所有对这个表的DDL和部分DML全被它挡住。我查出来的结果正是这样trx_state是RUNNINGtrx_started时间戳显示这个事务已经开了将近4个小时。一个4小时前开启、一直处于空闲但未提交的事务这就是本次事故的源头。2.4 如果sys视图查不到怎么手工拼条件后退一步说有些环境会关闭performance_schema或者部分云的托管实例你碰不到这些视图。这时候可以退回SHOW PROCESSLIST手动排查用这个SQL把处于MDL等待和疑似持有的会话一起列出来SELECT id, user, host, db, command, time, state, LEFT(info, 80) AS query_text FROM information_schema.processlist WHERE state LIKE %metadata lock% OR (command Sleep AND time 1000) ORDER BY time DESC;command Sleep且time特别大的连接是重点怀疑对象。MySQL里连接时间以time字段为准它表示当前会话最后一次活动到现在的秒数。如果这个值动辄几千甚至上万基本可以确定是事务未提交的空闲连接。我判断锁源头的逻辑很简单谁SLEEP了最久谁的trx_started最早谁就是第一嫌疑人。3. 连接池与max_connections的连锁反应为什么一锁就崩定位到锁的源头只是第一步这个事故真正致命的地方在于连接数被撑满后DBA的救火操作也会被拒之门外。所以这一章我重点讲清楚锁等待是怎么演化成连接打满的。3.1 每个等待中的请求都在消耗一个连接回到业务侧。应用层Java服务基本都用了HikariCP或Druid连接池连接池的初始大小、最小空闲、最大连接数配置各有不同。但它们的共同行为是当连接池里的连接被占满新的请求会排队等待获取连接一旦等待超过connectionTimeout或maxWait直接抛异常。表被锁住以后连接池里那个连接执行SQL时卡在MDL等待上它不会自己释放回连接池。连接池连接数很快被这些卡住的请求消耗干净。新的请求进来拿不到连接要么排队超时要么在数据库侧新建连接。数据库侧更惨。MySQL为每个新连接至少分配thread_stack、net_buffer等内存连接数一旦逼近max_connections上限SQL执行效率和系统整体响应速度都会急速恶化。因为操作系统调度和内存分配的开销在暴涨而真正干活的连接反而变少。3.2 为什么KILL占满连接的操作那么难执行到了这个阶段我面临一个非常现实的问题连接数已经打满了我执行SHOW PROCESSLIST甚至都可能失败因为建立一个新的管理连接也要占用一个连接槽位。如果max_connections没有预留额外的超级管理员连接槽或者你用的是普通账号在连接满的情况下什么都干不了只能去云控制台重启或者通过VIP切流。我这次比较幸运当初给监控机上的账号预留了10个额外的SUPER权限配合5.7以后支持的max_connections和login策略还能勉强登上去。但别抱侥幸心理我见过不止一次连接数打满后DBA用普通账号就连不上实例、只能求运维重启物理机的场景。重启物理机在关键时刻反而是最粗暴但最有效的手段之一因为锁状态和连接状态会一起重置。3.3 救火时的第一条命令不是KILL是调大连接数我的应急顺序是这样的先用ALTER SYSTEM SET max_connections 50008.0支持动态修改把连接数上限提上去给自己和业务方预留操作空间。再单独开一个连接执行定位SQL找到blocking_pid。确认blocking连接是无用的空闲事务后KILL掉它。锁释放后连接池会慢慢恢复等连接数降到正常水位后再把max_connections调回原值。这里有个很重要的判断不是所有的blocking_pid都能直接KILL。如果一个事务正在执行大批量UPDATE这个事务已经跑了30分钟且预计还要跑10分钟此时KILL会产生两部分代价第一是事务回滚耗时回滚可能比正向执行还慢第二是KILL后释放锁但新的事务又立刻抢占资源相当于双重负担。有经验的DBA会先看trx_query和trx_rows_modified评估事务处于哪个阶段再决定是顺着等还是强制切。刚才提到临时调大max_connections一些老DBA可能更习惯直接改/etc/my.cnf然后重启但8.0支持动态调整是很大优势除非集群架构要求必须改配置文件否则救火阶段优先用动态参数。4. 从MDL到连接池参数这套组合拳怎么打才不复发事故恢复了但如果只停留在“KILL掉几个会话、调大max_connections”这个层面那只是打了补丁。下面这部分是我这次事故后做的几项防御性改造每一步都有明确的目的不是套模板。4.1 应用侧事务边界治理根本解MDL锁最脏的来源就是应用代码里的“隐式长事务”。很多业务代码长这样Connection conn dataSource.getConnection(); conn.setAutoCommit(false); // 执行一个查询 ResultSet rs stmt.executeQuery(SELECT ...); // 没有立即commit/rollback // 而是去做远程调用或其他耗时的业务逻辑这种代码在事务里混入RPC调用、文件读写、甚至等用户输入是常见问题。事务开启后数据库不知道你会不会执行下一步写操作它会一直持有MDL读锁。调用外部接口超时3分钟这3分钟里整个表的所有DDL和部分DML都被阻塞。治理方向很明确事务内只做数据库操作不做外部调用这个必须靠代码评审和规范卡住。在连接池层配置setAutoCommit默认关闭的框架要特别小心比如Spring的Transactional最好显式指定超时时间避免无限持有事务。引入事务超时控制MySQL侧设置innodb_lock_wait_timeout和lock_wait_timeout之外还要在Java侧设置Transactional(timeout 3)这样的维度。4.2 lock_wait_timeout给MDL等待设个止损线MySQL 8.0提供了一个重要参数lock_wait_timeout这是专门控制元数据锁等待超时的参数默认值是31536000秒也就是一年。你没看错默认就是一年。这也是为什么业务方会“无限期”卡在那里的原因。-- 设置全局合理值 SET GLOBAL lock_wait_timeout 60; -- 当前会话单独设置也可以 SET SESSION lock_wait_timeout 30;实测下来这个参数对DML的阻塞保护作用很明显。一个查询或更新如果等待MDL超过60秒直接报错ER_LOCK_WAIT_TIMEOUT把错误抛给应用层做重试或降级处理而不是无限堆积连接。但也要注意DDL不会因为这个超时而中断MySQL对DDL的MDL等待处理机制仍然以阻塞为主。所以在做结构变更时还是应该靠pt-online-schema-change或gh-ost这类在线变更工具。经过这次事故我把所有核心业务实例的lock_wait_timeout都设成了30秒和行锁的innodb_lock_wait_timeout控制在同量级。这样不管是MDL还是行锁最长等待都不会超过30秒宁可报错扔给逻辑重试也不允许把连接池打垮。4.3 连接池参数怎么配合数据库防御数据库侧的参数是最后一道闸门应用侧连接池在这套防御体系里承担的是“限流”职责。我所见的众多事故里连接池设置太大比设置太小更容易惹祸。比如有个服务最大连接数设置200数据库max_connections是3000平时100个连接就够用。遇到锁表200个连接全部卡在MDL等待上数据库的连接数就被占掉200。如果同时有15个这样的服务3000就没了。合理的配置思路是核心服务maximumPoolSize设置为日常并发的两倍左右不要再多了。非核心服务maximumPoolSize适当缩小宁愿排队也不要把数据库连接耗尽。读多写少场景可以分拆只读实例把查询连接引导到只读实例上。这样即使主库MDL锁导致部分写连接阻塞读流量依然能正常消化。HikariCP的具体设置可以参考spring: datasource: hikari: maximum-pool-size: 50 minimum-idle: 10 connection-timeout: 30000 validation-timeout: 3000 idle-timeout: 600000 max-lifetime: 1800000connection-timeout设30秒是个折中值。太短容易在数据库真正繁忙时误伤正常请求太长则会让用户等很久才看到超时。如果应用侧有熔断机制这个值其实可以再短一点比如10秒。4.4 监控里必须盯死的四个指标很多团队监控MySQL只盯CPU、内存、磁盘空间和慢查询这些当然重要但锁相关的指标才是这次事故的第一响应者。我现在固定的监控项是这样指标来源告警阈值作用活跃连接数SHOW GLOBAL STATUS LIKE Threads_running大于CPU核数*4持续5分钟判断数据库是否过载当前连接数SHOW GLOBAL STATUS LIKE Threads_connected达到max_connections的70%提前预警连接耗尽未提交事务数information_schema.innodb_trx统计大于10个且持续3分钟抓长事务MDL等待事件performance_schema.events_waits_current出现持续超过10秒的等待直接定位锁表我当时就是吃了“只看连接数”的亏。连接数告警出来的时候其实已经晚了半拍如果早一些盯住Threads_running和MDL等待事件这个事故完全可以被压在萌芽阶段。4.5 结构变更工具的引入从根上减少DDL锁冲突这次事故还有个附带的教训负责人工执行DDL的操作习惯存在很大隐患。业务高峰期执行ALTER TABLE哪怕只是加一个索引类型的小变更在8.0里虽然使用了INPLACE算法但仍需要在开始和结束时短暂获取MDL写锁。如果这个表和线上流量纠缠得很紧一次快速DDL也可能被某个长时间SELECT卡住然后反向阻塞后续所有DML。在8.0之前我比较依赖pt-online-schema-change8.0虽然原生支持ALGORITHMINSTANT和INPLACE但针对超大表、复杂变更我仍然推荐使用gh-ost或者pt-osc来做。它们的原则是建影子表、追增量、切换全程不长时间持有MDL写锁对业务的影响降到最低。具体到我们的场景后续凡是涉及大表结构变更统一走gh-ost并且在低峰期执行。这是把DDL变成“软锁”的最佳实践。5. 复盘总结这次事故里真正的坑比锁本身更值得说最后聊点这次事故对于团队协作和日常习惯的启示这不是技术参数的堆砌而是实操里真正会影响下次反应的细节。先说人的问题。当天凌晨真正的紧张点不是SQL怎么写而是大家在没有运行手册的情况下同时操作一台数据库。有同事准备KILL一个看似空闲的连接我赶紧拦住因为那个连接先查了trx_query后才确认它是空闲事务如果误杀了正在跑大事务的会话回滚带来的追加延迟会更大。这里必须强调KILL操作前必须通过information_schema.innodb_trx确认trx_state和trx_rows_modified而不是只看processlist里的Command列。其次是自动化脚本的问题。我在事后写了一个检测MDL锁的脚本定期扫描performance_schema.metadata_locks把阻塞时间超过30秒的会话信息推送到群里。这个脚本写起来不难核心SQL大概是SELECT ml.object_schema, ml.object_name, ml.lock_type, ml.lock_status, ml.source, p.id AS pid, p.command, p.time, p.state AS process_state, LEFT(p.info, 100) AS query_text FROM performance_schema.metadata_locks ml JOIN performance_schema.threads t ON ml.owner_thread_id t.thread_id JOIN information_schema.processlist p ON t.processlist_id p.id WHERE ml.object_schema NOT IN (mysql, performance_schema, sys) AND ml.lock_status PENDING;脚本里特别保留lock_status PENDING这个条件因为这代表有会话在等待MDL锁。如果只是看metadata_locks全表正常状态下的MDL锁记录会非常多噪声很大反而是PENDING状态最能触发告警。第三个经验是关于跨团队协作的。恢复之后业务方一直在问到底是哪条SQL把表锁死了。我把从performance_schema.events_statements_history_long里捞出来的最后一条语句发给开发才发现是某个定时任务先执行了ALTER TABLE随后程序未提交的SELECT事务把它卡住。这种场景在活动大促、数据订正窗口最容易触发运维和开发之间应该有明确的操作审批流程晚上两点执行DDL这种事以后绝对不打无准备之仗。说到底MySQL 8.0提供了足够的观测手段我们缺的不是工具而是把这些工具串成一整套响应机制的习惯。把MDL等待事件、长事务、连接数、锁等待超时这四项做成常态化巡检项很多问题都能在用户感知之前被消化掉。我现在每次收到连接数告警第一反应已经不再是紧张而是按这套链路去查、去切、去恢复。
MySQL连接数爆满？MDL元数据锁引发的连锁事故排查与防御
1. 事故还原凌晨的连接数告警是怎么一路打到我这的先说结论这不是一条慢查询把数据库打挂的故事而是一张业务表被锁住之后所有线程挤在锁等待上把连接池和MySQL的连接数一起榨干的连锁事故。整个过程从出现第一个异常到数据库完全不可用大概只用了40分钟。那天凌晨2点17分值班手机开始响。先是Zabbix告警MySQL连接数超过3000紧接着业务方在群里刷屏——订单查询接口大面积超时后台管理系统登录直接转圈。我爬起来连上跳板机第一眼看到的监控曲线是这样活跃连接数从平时800左右直接拉满到max_connections上限3100QPS却掉了三分之二。这个组合非常反常。正常来说连接数涨、QPS应该跟着涨或者至少持平结果QPS跌了说明大量连接并不是在正常执行SQL而是堵在某个环节上。我马上想到两种情况要么是连接池配置被改爆了要么是大量会话在等待某个资源。登录到MySQL实例上执行了SHOW PROCESSLIST结果很直接——一屏全是Waiting for table metadata lock配合几百条SELECT堆积。也就是说表被锁了后来的读写全部进等待队列连接越堆越多最终撑满连接数上限。这里先解释一下元数据锁MDL的概念因为后面所有的排查和救火都是围绕它展开的。MySQL从5.5开始引入MDL机制目的是保护表结构定义预防DDL语句和DML语句同时修改表结构时出现不一致。任何一条SQL在开始执行前都需要先获取对应表的MDL读锁或写锁。读锁之间共享读锁和写锁互斥写锁之间也互斥。一条长时间挂起的DDL或者一个忘了提交的事务都可能把整张表的读写全堵死。8.0版本里MDL的等待被记录在performance_schema.metadata_locks表中这也成了这次事故定位的关键依据。如果你在工单或者群里看到类似的报错——Lock wait timeout exceeded、Waiting for table metadata lock、Too many connections——大概率就是我这次遇到的情况或者离它不远了。这篇文章我把整个事故的定位链路、连接打满的深层次原因、还有事后怎么做防御性改造一次性讲清楚。2. 定位锁源头从sys库到information_schema的完整取证链2.1 第一步先分清楚自己是哪种“锁”很多人一看到锁就慌先把innodb_lock_wait_timeout调大或者直接KILL几个进程。但不同的锁处理方式完全不同乱操作反而会让事态恶化。从SHOW PROCESSLIST的输出看我需要先区分当前是行锁等待还是MDL锁等待。如果是行锁等待会话状态通常显示为Waiting for handler commit或者Lock wait timeout exceeded相关信息主要落在information_schema.innodb_lock_waits、performance_schema.data_locks和data_lock_waits这几张表上。如果是MDL锁等待会话状态会明确显示Waiting for table metadata lock关键信息落在performance_schema.metadata_locks表里。我这次看到的是清一色的Waiting for table metadata lock所以把主战场放在MDL锁上。这里有个经验之谈在MySQL 8.0里行锁和MDL锁的排查入口已经分家了别再像5.7时代那样只盯着information_schema那几张表8.0的performance_schema才是完整依据。2.2 用sys库视图快速锁定“谁堵住了路”定位MDL锁的sql其实很经典如果你上了MySQL 8.0可以直接查sys.schema_table_lock_waitsSELECT waiting_pid, waiting_query, blocking_pid, blocking_query, waiting_lock_type, blocking_lock_type FROM sys.schema_table_lock_waits WHERE object_schema your_db AND object_name your_table\G注意一点sys.schema_table_lock_waits是基于performance_schema.metadata_locks做的聚合视图如果performance_schema没开启或者metadata_locks采集项被关了这个视图查出来就是空的。排查前先确认一下SHOW VARIABLES LIKE performance_schema; SELECT * FROM performance_schema.setup_consumers WHERE name global_instrumentation; SELECT * FROM performance_schema.setup_instruments WHERE name LIKE wait/lock/metadata/sql/mdl;正常生产环境这两个都应该处于开启状态。如果没开尤其在8.0里我会建议直接开启性能损耗几乎可以忽略。查出来的结果大致是blocking_pid是某个长时间运行的DDL或者某个处于Sleep状态的连接。我那次看到的是一个blocking_pid始终没变query列为NULL但state显示Sleep的会话。这种会话十有八九是一个开启了事务但没提交的连接。2.3 锁等待关系反查拿着blocking_pid追溯事务源头当sys.schema_table_lock_waits给出blocking_pid后我再进一步看这个连接在干什么SELECT trx_id, trx_state, trx_started, trx_mysql_thread_id, trx_query, trx_rows_locked, trx_rows_modified FROM information_schema.innodb_trx WHERE trx_mysql_thread_id 阻塞的thread_id;这一步很关键因为MDL锁的持有者有时候不是正在执行的SQL而是一个“开小差”的事务。比如一个应用连接在autocommit0的情况下查了一条记录接着又去写日志或调外部接口事务一直不提交事务持有的MDL读锁也不会释放。这时候它的trx_query往往是NULL看起来人畜无害实际上所有对这个表的DDL和部分DML全被它挡住。我查出来的结果正是这样trx_state是RUNNINGtrx_started时间戳显示这个事务已经开了将近4个小时。一个4小时前开启、一直处于空闲但未提交的事务这就是本次事故的源头。2.4 如果sys视图查不到怎么手工拼条件后退一步说有些环境会关闭performance_schema或者部分云的托管实例你碰不到这些视图。这时候可以退回SHOW PROCESSLIST手动排查用这个SQL把处于MDL等待和疑似持有的会话一起列出来SELECT id, user, host, db, command, time, state, LEFT(info, 80) AS query_text FROM information_schema.processlist WHERE state LIKE %metadata lock% OR (command Sleep AND time 1000) ORDER BY time DESC;command Sleep且time特别大的连接是重点怀疑对象。MySQL里连接时间以time字段为准它表示当前会话最后一次活动到现在的秒数。如果这个值动辄几千甚至上万基本可以确定是事务未提交的空闲连接。我判断锁源头的逻辑很简单谁SLEEP了最久谁的trx_started最早谁就是第一嫌疑人。3. 连接池与max_connections的连锁反应为什么一锁就崩定位到锁的源头只是第一步这个事故真正致命的地方在于连接数被撑满后DBA的救火操作也会被拒之门外。所以这一章我重点讲清楚锁等待是怎么演化成连接打满的。3.1 每个等待中的请求都在消耗一个连接回到业务侧。应用层Java服务基本都用了HikariCP或Druid连接池连接池的初始大小、最小空闲、最大连接数配置各有不同。但它们的共同行为是当连接池里的连接被占满新的请求会排队等待获取连接一旦等待超过connectionTimeout或maxWait直接抛异常。表被锁住以后连接池里那个连接执行SQL时卡在MDL等待上它不会自己释放回连接池。连接池连接数很快被这些卡住的请求消耗干净。新的请求进来拿不到连接要么排队超时要么在数据库侧新建连接。数据库侧更惨。MySQL为每个新连接至少分配thread_stack、net_buffer等内存连接数一旦逼近max_connections上限SQL执行效率和系统整体响应速度都会急速恶化。因为操作系统调度和内存分配的开销在暴涨而真正干活的连接反而变少。3.2 为什么KILL占满连接的操作那么难执行到了这个阶段我面临一个非常现实的问题连接数已经打满了我执行SHOW PROCESSLIST甚至都可能失败因为建立一个新的管理连接也要占用一个连接槽位。如果max_connections没有预留额外的超级管理员连接槽或者你用的是普通账号在连接满的情况下什么都干不了只能去云控制台重启或者通过VIP切流。我这次比较幸运当初给监控机上的账号预留了10个额外的SUPER权限配合5.7以后支持的max_connections和login策略还能勉强登上去。但别抱侥幸心理我见过不止一次连接数打满后DBA用普通账号就连不上实例、只能求运维重启物理机的场景。重启物理机在关键时刻反而是最粗暴但最有效的手段之一因为锁状态和连接状态会一起重置。3.3 救火时的第一条命令不是KILL是调大连接数我的应急顺序是这样的先用ALTER SYSTEM SET max_connections 50008.0支持动态修改把连接数上限提上去给自己和业务方预留操作空间。再单独开一个连接执行定位SQL找到blocking_pid。确认blocking连接是无用的空闲事务后KILL掉它。锁释放后连接池会慢慢恢复等连接数降到正常水位后再把max_connections调回原值。这里有个很重要的判断不是所有的blocking_pid都能直接KILL。如果一个事务正在执行大批量UPDATE这个事务已经跑了30分钟且预计还要跑10分钟此时KILL会产生两部分代价第一是事务回滚耗时回滚可能比正向执行还慢第二是KILL后释放锁但新的事务又立刻抢占资源相当于双重负担。有经验的DBA会先看trx_query和trx_rows_modified评估事务处于哪个阶段再决定是顺着等还是强制切。刚才提到临时调大max_connections一些老DBA可能更习惯直接改/etc/my.cnf然后重启但8.0支持动态调整是很大优势除非集群架构要求必须改配置文件否则救火阶段优先用动态参数。4. 从MDL到连接池参数这套组合拳怎么打才不复发事故恢复了但如果只停留在“KILL掉几个会话、调大max_connections”这个层面那只是打了补丁。下面这部分是我这次事故后做的几项防御性改造每一步都有明确的目的不是套模板。4.1 应用侧事务边界治理根本解MDL锁最脏的来源就是应用代码里的“隐式长事务”。很多业务代码长这样Connection conn dataSource.getConnection(); conn.setAutoCommit(false); // 执行一个查询 ResultSet rs stmt.executeQuery(SELECT ...); // 没有立即commit/rollback // 而是去做远程调用或其他耗时的业务逻辑这种代码在事务里混入RPC调用、文件读写、甚至等用户输入是常见问题。事务开启后数据库不知道你会不会执行下一步写操作它会一直持有MDL读锁。调用外部接口超时3分钟这3分钟里整个表的所有DDL和部分DML都被阻塞。治理方向很明确事务内只做数据库操作不做外部调用这个必须靠代码评审和规范卡住。在连接池层配置setAutoCommit默认关闭的框架要特别小心比如Spring的Transactional最好显式指定超时时间避免无限持有事务。引入事务超时控制MySQL侧设置innodb_lock_wait_timeout和lock_wait_timeout之外还要在Java侧设置Transactional(timeout 3)这样的维度。4.2 lock_wait_timeout给MDL等待设个止损线MySQL 8.0提供了一个重要参数lock_wait_timeout这是专门控制元数据锁等待超时的参数默认值是31536000秒也就是一年。你没看错默认就是一年。这也是为什么业务方会“无限期”卡在那里的原因。-- 设置全局合理值 SET GLOBAL lock_wait_timeout 60; -- 当前会话单独设置也可以 SET SESSION lock_wait_timeout 30;实测下来这个参数对DML的阻塞保护作用很明显。一个查询或更新如果等待MDL超过60秒直接报错ER_LOCK_WAIT_TIMEOUT把错误抛给应用层做重试或降级处理而不是无限堆积连接。但也要注意DDL不会因为这个超时而中断MySQL对DDL的MDL等待处理机制仍然以阻塞为主。所以在做结构变更时还是应该靠pt-online-schema-change或gh-ost这类在线变更工具。经过这次事故我把所有核心业务实例的lock_wait_timeout都设成了30秒和行锁的innodb_lock_wait_timeout控制在同量级。这样不管是MDL还是行锁最长等待都不会超过30秒宁可报错扔给逻辑重试也不允许把连接池打垮。4.3 连接池参数怎么配合数据库防御数据库侧的参数是最后一道闸门应用侧连接池在这套防御体系里承担的是“限流”职责。我所见的众多事故里连接池设置太大比设置太小更容易惹祸。比如有个服务最大连接数设置200数据库max_connections是3000平时100个连接就够用。遇到锁表200个连接全部卡在MDL等待上数据库的连接数就被占掉200。如果同时有15个这样的服务3000就没了。合理的配置思路是核心服务maximumPoolSize设置为日常并发的两倍左右不要再多了。非核心服务maximumPoolSize适当缩小宁愿排队也不要把数据库连接耗尽。读多写少场景可以分拆只读实例把查询连接引导到只读实例上。这样即使主库MDL锁导致部分写连接阻塞读流量依然能正常消化。HikariCP的具体设置可以参考spring: datasource: hikari: maximum-pool-size: 50 minimum-idle: 10 connection-timeout: 30000 validation-timeout: 3000 idle-timeout: 600000 max-lifetime: 1800000connection-timeout设30秒是个折中值。太短容易在数据库真正繁忙时误伤正常请求太长则会让用户等很久才看到超时。如果应用侧有熔断机制这个值其实可以再短一点比如10秒。4.4 监控里必须盯死的四个指标很多团队监控MySQL只盯CPU、内存、磁盘空间和慢查询这些当然重要但锁相关的指标才是这次事故的第一响应者。我现在固定的监控项是这样指标来源告警阈值作用活跃连接数SHOW GLOBAL STATUS LIKE Threads_running大于CPU核数*4持续5分钟判断数据库是否过载当前连接数SHOW GLOBAL STATUS LIKE Threads_connected达到max_connections的70%提前预警连接耗尽未提交事务数information_schema.innodb_trx统计大于10个且持续3分钟抓长事务MDL等待事件performance_schema.events_waits_current出现持续超过10秒的等待直接定位锁表我当时就是吃了“只看连接数”的亏。连接数告警出来的时候其实已经晚了半拍如果早一些盯住Threads_running和MDL等待事件这个事故完全可以被压在萌芽阶段。4.5 结构变更工具的引入从根上减少DDL锁冲突这次事故还有个附带的教训负责人工执行DDL的操作习惯存在很大隐患。业务高峰期执行ALTER TABLE哪怕只是加一个索引类型的小变更在8.0里虽然使用了INPLACE算法但仍需要在开始和结束时短暂获取MDL写锁。如果这个表和线上流量纠缠得很紧一次快速DDL也可能被某个长时间SELECT卡住然后反向阻塞后续所有DML。在8.0之前我比较依赖pt-online-schema-change8.0虽然原生支持ALGORITHMINSTANT和INPLACE但针对超大表、复杂变更我仍然推荐使用gh-ost或者pt-osc来做。它们的原则是建影子表、追增量、切换全程不长时间持有MDL写锁对业务的影响降到最低。具体到我们的场景后续凡是涉及大表结构变更统一走gh-ost并且在低峰期执行。这是把DDL变成“软锁”的最佳实践。5. 复盘总结这次事故里真正的坑比锁本身更值得说最后聊点这次事故对于团队协作和日常习惯的启示这不是技术参数的堆砌而是实操里真正会影响下次反应的细节。先说人的问题。当天凌晨真正的紧张点不是SQL怎么写而是大家在没有运行手册的情况下同时操作一台数据库。有同事准备KILL一个看似空闲的连接我赶紧拦住因为那个连接先查了trx_query后才确认它是空闲事务如果误杀了正在跑大事务的会话回滚带来的追加延迟会更大。这里必须强调KILL操作前必须通过information_schema.innodb_trx确认trx_state和trx_rows_modified而不是只看processlist里的Command列。其次是自动化脚本的问题。我在事后写了一个检测MDL锁的脚本定期扫描performance_schema.metadata_locks把阻塞时间超过30秒的会话信息推送到群里。这个脚本写起来不难核心SQL大概是SELECT ml.object_schema, ml.object_name, ml.lock_type, ml.lock_status, ml.source, p.id AS pid, p.command, p.time, p.state AS process_state, LEFT(p.info, 100) AS query_text FROM performance_schema.metadata_locks ml JOIN performance_schema.threads t ON ml.owner_thread_id t.thread_id JOIN information_schema.processlist p ON t.processlist_id p.id WHERE ml.object_schema NOT IN (mysql, performance_schema, sys) AND ml.lock_status PENDING;脚本里特别保留lock_status PENDING这个条件因为这代表有会话在等待MDL锁。如果只是看metadata_locks全表正常状态下的MDL锁记录会非常多噪声很大反而是PENDING状态最能触发告警。第三个经验是关于跨团队协作的。恢复之后业务方一直在问到底是哪条SQL把表锁死了。我把从performance_schema.events_statements_history_long里捞出来的最后一条语句发给开发才发现是某个定时任务先执行了ALTER TABLE随后程序未提交的SELECT事务把它卡住。这种场景在活动大促、数据订正窗口最容易触发运维和开发之间应该有明确的操作审批流程晚上两点执行DDL这种事以后绝对不打无准备之仗。说到底MySQL 8.0提供了足够的观测手段我们缺的不是工具而是把这些工具串成一整套响应机制的习惯。把MDL等待事件、长事务、连接数、锁等待超时这四项做成常态化巡检项很多问题都能在用户感知之前被消化掉。我现在每次收到连接数告警第一反应已经不再是紧张而是按这套链路去查、去切、去恢复。
