1. 事故现场从一条“插入卡死”扯出整张表的排队风暴那个凌晨两点半值班群里陆陆续续有人喊“订单保存一直转圈”“后台报表导不出来”。我本来以为又是一次普通的慢查询顺手查一下监控结果发现数据库CPU、IO都很正常甚至内存都没什么压力但活跃连接数像被什么东西卡住一样一路往上顶最要命的是连接数到达阈值后新的连接根本进不去前端不断报“too many connections”。当时我心里就咯噔一下这不是慢查询的常见套路。慢查询通常是CPU飙高或者磁盘IO打满但这回两者都很平静更像是有东西把请求“堵”在某个环节里不让它们往前推进也不让它们结束于是连接池被活活拖死。我做的第一件事是登录到数据库实例上跑了一句SHOW PROCESSLIST。结果刷了一屏大部分会话状态都是Waiting for table metadata lock和updating而且从time列看很多会话已经卡了几十秒甚至几分钟。再往下翻有几条insert语句一直处于query end或者updating状态时间在持续增加。随后我看了错误日志里面有“Lock wait timeout exceeded; try restarting transaction”这种报错到了这一步基本可以断定这是一次典型的mysql事务锁阻塞而且已经上升到整库级别的连锁排队。这篇文章想分享的就是这次排查的完整思路。如果你是后端开发、DBA或者一个连MySQL内部结构都还没吃透但偶尔要救火的负责人这篇能帮你在下一次锁阻塞来临时少走一大圈弯路。我不会只贴一堆SELECT让大家抄而是把“为什么锁会被卡住”“怎么找到那个导致一切堵住的源头事务”“恢复之后怎么做事后整改”全部串起来讲清楚。先把结论放在前面大部分生产事故里锁阻塞的根因就那么几类——长事务一直不提交、事务内部掺了外部调用、更新条件没走索引导致锁范围被放大。知道结论不难难的是在故障现场快速判断属于哪一种然后用最小代价恢复业务。后面的章节就是围绕这个过程展开的。2. 锁的底层逻辑为什么一行更新能让所有路由全部堵住很多开发刚接触MySQL锁问题时第一反应是“我只更新了一行怎么会把整张表都堵了”这句话本身没错但它忽略了两个关键细节一是“更新一行”有时候并不只是锁一行二是某些锁的兼容性会让所有写操作在同一个坑里排队。2.1 InnoDB 到底锁住了什么记录锁、间隙锁与意向锁InnoDB的行锁并不是锁“物理行”而是锁“索引记录”。如果一个表没有显式主键InnoDB会生成隐藏的聚簇索引来承担这个职责。这带来的直接后果是凡是需要行锁的操作都必须先通过索引定位到目标记录。如果条件无法走索引执行计划就会退化成全表扫描有时候InnoDB会因此在海量记录上依次加锁你看着像只更新了少量行实际锁住的范围大得夸张。常见的锁类型有这么几种记录锁Record Lock锁住某条索引记录本身其他事务想修改/删除同一行就必须等待。间隙锁Gap Lock锁住两条记录之间的“空隙”在默认隔离级别REPEATABLE READ下用来防止幻读其他事务无法在这个间隙里插入新记录。临键锁Next-Key Lock记录锁加间隙锁的组合这是RR模式下InnoDB的默认加锁方式既锁已有记录也锁空隙。意向锁Intention Lock表级别的一种“预告”比如事务准备在某行加X锁会先在表上加IX锁这样其他事务做整表DDL或LOCK TABLES时能立刻知道自己会不会被阻塞。用一个生活化的类比记录锁相当于停在某一个停车位间隙锁相当于把停车位之间的通道也临时占了临键锁则是“车位通道”一起占住而意向锁相当于你在停车场门口立了一块“我进来了准备找个车位”的牌子。这里一定要记住加锁范围取决于你的SQL走了什么索引。走唯一索引精确匹配时间隙锁通常不参与走非唯一索引或者全表扫描时间隙锁可能大面积出现后面的INSERT会被卡得死死的。2.2 锁等待与死锁的本质区别排错第一步是分清当前的情况是“锁等待”还是“死锁”。这两者的处理策略完全不同。锁等待是一个事务在等另一个事务所持有的锁等多久由参数innodb_lock_wait_timeout控制默认50秒。如果50秒内对方还没释放等待方直接报错“Lock wait timeout exceeded; try restarting transaction”自己回滚对方继续跑。这种场景类似于排队买票前面的人一直不挪后面的人等得不耐烦离开队伍。死锁则是两个事务互相持有对方需要的锁形成环形等待。MySQL的内存里有一个死锁检测机制一旦检测到环会主动牺牲其中一个代价较小的事务让它回滚并报出死锁错误。死锁是“事故式”的几乎每次都会立刻暴露而锁等待往往更隐蔽因为有人在“排队”系统不会立刻报错只在超过50秒后才触发超时。在排错时怎么快速区分死锁会出现在SHOW ENGINE INNODB STATUS\G的LATEST DETECTED DEADLOCK段落里锁等待则需要通过事务列表判断“有没有一个老事务一直占着锁其他事务都在等它”。从故障现场看大量会话卡在updating状态大概率是锁等待而非死锁。2.3 一个长事务如何引爆连锁排队假设有一个事务在 00:00 开始执行了一条UPDATE order SET status1 WHERE user_id123这个user_id上有索引命中了100行于是这100行对应的索引记录被加上了X锁。更麻烦的是在RR隔离级别下这100行附近的“间隙”也可能被间隙锁锁住。这时候第二个事务来了它对同一批订单中的某一条执行UPDATE直接排队第三个事务想往这个user_id的用户新增一条订单插入的坐标一旦落在间隙锁覆盖的范围也排队第四个事务看起来只是查一下status2的订单数量执行计划里扫描到被锁的记录在没有开启非锁定读的情况下同样可能阻塞。也就是说一个本来只想更新100行的事务只要不提交就能让大量读、写操作全部堵在同一批索引记录上。出现这个局面时活跃连接数会猛增因为每个请求都在等待锁释放连接池里的连接全部被占用新请求就只能排队或者报连接数超限。这也是为什么我一直强调遇到锁阻塞故障先不要纠结为什么“一行更新能堵全表”而是第一时间找到那个“最老的、还攥着锁的事务”。如果这个事务已经跑了几十分钟那它撑起的锁队列就是整个故障的直接元凶。3. 排查过程全记录从 processlist 到 information_schema 再到 performance_schema现在进入这篇文章最核心的部分站在故障现场一步一步把锁阻塞的源点挖出来。3.1 先抓现场SHOW PROCESSLIST 里的那几个关键词我每次排查锁问题固定顺序是先看SHOW PROCESSLIST因为它是数据库当时状态的“快照”。注意最好用SHOW FULL PROCESSLIST\G的形式查看普通LIST里Info字段会被截断看不到完整SQL也就很难判断这个会话到底在执行什么。重点关注三列Command、State、Time。Command是Query说明正在执行SQL如果是Sleep则说明语句执行完了但事务没提交。State里如果出现Waiting for table metadata lock优先怀疑MDL锁问题比如ALTER TABLE或者没提交的查询卡住了表结构锁如果大量出现updating、query end则大概率是行锁等待。Time字段可以提供会话持续时间找到那些时间特别长、State又可疑的会话它们通常就是“排队大军”的尾部。另外我在这一步最容易踩的一个坑是用业务连接去执行排查SQL。比如运维账号走了某个业务侧的跳板机连上来时复用了连接池的连接结果这条查询本身也被锁等待堵住了。我的习惯是开一个独立的MySQL客户端最好是从跳板机新起的连接不要走业务侧连接池。3.2 information_schema 两张表事务状态和锁等待关系PROCESSLIST能告诉我们“谁在等待”但还不能精确指向“谁在阻塞”。要回答这个“谁在阻塞”需要借助事务和锁的元数据。先查information_schema.innodb_trx这张表记录了当前所有InnoDB事务的状态SELECT trx_id, trx_state, trx_started, trx_mysql_thread_id, trx_query, trx_rows_locked, trx_rows_modified FROM information_schema.innodb_trx ORDER BY trx_started;这个查询很直白按事务开始时间排序最顶端的通常就是最老的事务。我在故障现场最关心三个字段trx_started事务开始时间和当前时间对比就能算出存活时长trx_state如果是RUNNING说明正在执行如果是LOCK WAIT说明它在等别人的锁trx_mysql_thread_id这个字段对应PROCESSLIST的Id等一下可以用KILL精准干掉它。还要留意trx_query为NULL的事务。在COMMIT之前的间隙里如果客户端没有发下一条语句trx_query就是NULL但事务依然活着锁照样攥在手里。这种“空闲但未提交”的事务比正在跑大SQL的事务更容易被遗漏。接着查information_schema.innodb_lock_waits这张表能直接给出锁等待的依赖关系SELECT * FROM information_schema.innodb_lock_waits\G里面最关键的字段是requesting_trx_id等待方事务ID和blocking_trx_id阻塞方事务ID。把两张表JOIN起来就能串出一条完整的阻塞链。3.3 组合查询画出完整阻塞链为了不在一堆元数据里迷失方向我在服务器上专门存了下面这条查询故障时直接复制粘贴就能用SELECT r.trx_id AS waiting_trx_id, r.trx_mysql_thread_id AS waiting_thread, r.trx_query AS waiting_query, b.trx_id AS blocking_trx_id, b.trx_mysql_thread_id AS blocking_thread, b.trx_started AS blocking_started, TIMESTAMPDIFF(SECOND, b.trx_started, NOW()) AS blocking_seconds, l.lock_table AS locked_table, l.lock_type AS lock_type, l.lock_mode AS lock_mode FROM information_schema.innodb_lock_waits w JOIN information_schema.innodb_trx r ON w.requesting_trx_id r.trx_id JOIN information_schema.innodb_trx b ON w.blocking_trx_id b.trx_id JOIN information_schema.innodb_locks l ON b.trx_id l.lock_trx_id\G这条SQL输出之后你会看到类似这样的信息等待方是一个UPDATE语句卡在updating状态阻塞方是另一个事务已经跑了2000多秒blocking_seconds高达2000锁定的表是订单表锁类型是RECORD锁模式是X。看到这种结果基本可以锁定某个人为开启或遗漏提交的长事务全程握着这行记录的X锁把后续所有写操作全部挡在了门外。接下来只需要把blocking_thread对应的会话找出来确认然后决定是否KILL。3.4 MySQL 8.0 下的自查差异不同MySQL版本的查询方式有差异这一点很多人会在生产环境里踩坑。MySQL 5.7及之前information_schema.innodb_lock_waits和innodb_locks是主力表查起来很方便。到了MySQL 8.0这两张表虽然还在但我用下来感觉更完善的是performance_schema里的data_lock_waits和data_locks。如果实例开启了performance_schema可以用SELECT w.REQUESTING_ENGINE_TRANSACTION_ID AS waiting_trx, w.BLOCKING_ENGINE_TRANSACTION_ID AS blocking_trx, w.REQUESTING_ENGINE_LOCK_ID AS waiting_lock, w.BLOCKING_ENGINE_LOCK_ID AS blocking_lock FROM performance_schema.data_lock_waits w\GMySQL 8.0 还自带sys.innodb_lock_waits视图它会直接格式化输出等待表和阻塞线程比手写JOIN更省事SELECT * FROM sys.innodb_lock_waits\G如果没有这些视图又想快速看阻塞链还有一个通用的笨办法打开SHOW ENGINE INNODB STATUS\G在TRANSACTIONS段落里翻找LOCK WAIT字样InnoDB会在其中列出“当前事务正在等待哪把锁、哪把锁被哪个事务持有”。不过在锁很多的时候这段输出会非常长我一般只把它当辅助不会依赖它做主要定位。3.5 一个实战例子从“等待 20 分钟”到定位根因这里用一个脱敏的真实案例来说明整个定位过程。当时某支付系统的对账文件导入任务跑挂了连续几条告警刷过来表里全是锁等待。我的操作顺序是SHOW FULL PROCESSLIST看到十几个会话卡在updating而且Info里都是同一类SQLUPDATE account_balance SET balance? WHERE account_id?。查innodb_trx按trx_started排序发现最老的事务已经运行了大约20分钟但它对应的trx_query是NULL说明SQL执行结束了事务没提交。查innodb_lock_waits上面那条JOIN SQL直接显示20个会话都在等这同一个事务ID。顺着trx_mysql_thread_id找到对应连接看了下这个连接的来源IP和程序名确认是对账导入程序里的一个连接。整个过程不到两分钟就锁定了“对账程序打开事务后一直没提交”这个根因。后来一查代码果然是因为导入逻辑里事务边界没写对异常分支直接return了没走COMMIT/ROLLBACK。4. 根因复盘锁阻塞常见的三种业务打开方式锁被堵住的表象千奇百怪底层原因其实高度集中。复盘了这些年我遇到过的锁阻塞场景基本可以归为三类每一类都有很明显的业务特征。4.1 长事务一直不提交最典型的占锁姿势这种最容易理解也是故障率最高的。典型特征innodb_trx里有一个事务的开始时间远早于告警时间且trx_query可能为NULL也可能是一条很长的批处理SQL。事务一直不COMMIT或ROLLBACK锁也就一直不会释放。长事务的来源五花八门批量导入程序在循环里开了事务但没有批次提交代码里异常分支漏写了ROLLBACK用了ORM框架事务的传播行为设置不当一个小方法就拉起了事务并一直沿用到请求结束手动在命令行开启事务后忘记提交这种情况多见于临时的数据修复操作。这类问题的隐患不光在锁上还在于事务一旦存在过久InnoDB的 undo log 会一直保留到事务结束数据文件可能会异常膨胀。所以我经常跟团队说先不说锁光是“事务不结束”本身就足以把一个数据库拖垮。4.2 事务内掺入外部调用锁被“拉长”的元凶第二种情况更隐蔽我也认为是最值得后端开发警惕的。它和第一种的最大区别是事务本身没有“忘记结束”而是被外部调用人为拉长了。代码形态大概是这样的事务开启执行第一条UPDATE拿行锁在事务内调用远程接口比如调用支付网关或者一个第三方风控服务网络往返几百毫秒到几秒不等如果对方超时重试还会更久拿到响应后再执行第二条UPDATE最后COMMIT。问题在于远程调用的时间完全不可控。一次正常的接口调用可能200毫秒但对方抖动一下就是5秒在这5秒里该行数据的锁一直被事务攥着。如果有50个并发请求都在更新同一批数据连接池会在5秒内被占满业务立刻“假死”。我印象很深的一个案例是一个订单状态更新接口在某次上线后频繁出现锁等待告警。定位后发现代码里在事务中间调用了一个活动系统的优惠券核销接口那个接口偶尔会慢2-3秒导致事务存活时间被无限拉长。解法并不复杂把远程调用挪到事务外先本地落库提交再异步调用外部接口。但这类问题不是看代码一眼就能发现的必须结合锁等待记录和业务日志来回比对才能看到事务边界里的“猫腻”。4.3 更新条件不走索引行锁被放大成范围锁第三种情况侧重在“索引”这件事上。先说结论UPDATE和DELETE的WHERE条件如果没走索引InnoDB在定位目标记录时需要扫描大量数据页它只能在扫描过程中对符合条件或需要进一步判断的记录加锁锁的数量和范围都会被显著放大。举个例子有一张订单表字段status没有索引某次运营活动需要把一批订单改成已完成SQL写的是UPDATE orders SET statusFINISHED WHERE statusPROCESSING;如果没有索引这条SQL在扫描过程中会对大量statusPROCESSING的记录加锁而在RR隔离级别下扫描范围内的间隙也可能被盖上间隙锁。于是其他针对这些订单的插入、修改全部被堵住哪怕它们和这个更新任务在业务上毫无关系。反过来如果status上有合适的二级索引InnoDB会直接通过索引定位要改的记录锁的数量就控制在精确匹配的行上。这也是为什么我一直强调排查锁问题时顺手看一下EXPLAIN的执行计划重点看type和key字段。如果key是空或者type是ALL那么这次更新锁的范围大概率已经被放大了。5. 止损与恢复先让业务喘口气再逐层排雷定位到阻塞源头之后最紧迫的事情是恢复业务。但“杀掉阻塞事务”这个操作不能无脑执行否则会引发二次故障。5.1 KILL 前先做三件事我在准备KILL某个会话前会先确认三件事确认这个事务没有在执行重要的写操作。有时候阻塞方本身正在进行几十万行的大批量更新直接KILL会让它回滚回滚同样需要时间而且回滚期间undo log的清理压力会很大。如果这个事务已经跑了很久KILL之后回滚可能需要比KILL前更久。这时候要评估继续等它跑完还是杀掉回滚。确认它确实是最老的那个事务而不是某个关键链路。有些事务看起来老但它可能是某个核心批处理任务直接KILL会造成数据链路中断。至少要同时看代码上下文和业务日志判断它是不是可以被安全放弃。记录下当前所有相关信息。把trx_id、trx_mysql_thread_id、执行的SQL、开始时间全部记录下来这对后续复盘写故障报告非常重要。确认没问题后再执行KILL trx_mysql_thread_id;KILL命令发出后会话会收到终止信号事务开始回滚锁会逐步释放。在事务较大的时候回滚过程可能持续几秒到几十秒。实际操作中我经常是KILL完阻塞方之后再连续观察几轮SHOW PROCESSLIST确认锁等待的会话数量在明显下降。另外还有一个非常容易忽略的点KILL掉的是数据库层面的连接但业务侧的连接池会立刻感知到连接断了自动重连并重放应用层逻辑。如果重放失败或者重放后又开新事务锁问题可能马上复发。所以KILL之后如果业务侧没有自动恢复要迅速联系值班同事检查应用日志必要时先对入口做限流或者开关灰度。5.2 不要乱调 innodb_lock_wait_timeout 的参数陷阱故障恢复之后总有同事提一个建议既然锁等待老超时那把innodb_lock_wait_timeout调大一点多给点耐心不就好了这里一定要克制。这个参数默认值是50秒调大意味着所有锁等待的请求会在“排队”状态里停留更久表面上超时报错少了但连接的积压和业务的卡顿会更严重。调小则会让业务短暂撞锁就立刻失败原本可以等一下的请求全部变成报错加重应用层的重试风暴。我的态度是维护阶段可以微调这个参数但不能用来掩盖根因。比如特殊场景确实希望业务快速失败并走重试策略可以适当调到5-10秒但如果是为了让某个大事务有足够时间拿到锁那应该去优化事务本身而不是在超时时间上做文章。5.3 恢复验证从监控指标到实际 SQL 复测业务表面恢复不等于故障真正结束。我一般会做两层验证。第一层看监控指标活跃连接数是否回落到正常水位慢查询数量是否下降锁等待相关监控项是否归零。如果数据库有专门的锁等待指标比如Innodb_row_lock_current_waits、Innodb_row_lock_time直接查这些计数器最有说服力。第二层做“变造实弹”的复测挑一条和故障SQL同结构的语句手动执行一次确认不再出现等待。比如故障时UPDATE orders SET status? WHERE order_id?慢就在故障恢复后单独跑一次同款SQL观察执行时间是否回到毫秒级别。想再稳一点可以在业务低峰期开一个新事务故意不提交然后用另一个会话尝试更新同一行确认锁等待机制和超时逻辑能正常触发而不影响主流程。6. 故障之后的整改让这个坑不再出现在告警群里把业务救回来之后真正的活儿才刚刚开始。每次锁阻塞事故背后都对应代码、索引或监控上的某个薄弱环节。如果只是KILL一个事务就收工下次大概率会换个马甲再出现。6.1 从业务代码压缩事务边界代码层面是我整改时第一优先级的动作。核心思路其实就一句话事务里只留必须原子性表现的操作其余全部挪出去。具体可以落地的点包括禁止在事务内执行远程HTTP调用、RPC调用、消息队列操作大批量写操作拆成批次比如每500行或1000行提交一次避免一个事务跑几分钟代码里所有事务分支必须有完整的COMMIT/ROLLBACK覆盖异常分支要尤其小心ORM框架里检查事务传播属性避免非事务方法意外掺入外层事务把事务范围无意识拉大。我在整改时最喜欢给团队立一条“事务三不准”的规则事务内不准有IO等待事务内不准有外部调用事务内不准有循环写。这三条看着严但真正做到之后锁阻塞问题的发生率会直线下降。6.2 用执行计划锁定“会被放大”的 UPDATE 条件索引层面的整改就是针对之前提到的“更新条件没走索引导致锁范围被放大”。常态化的做法是把慢查询日志和锁等待记录里的高频更新语句捞出来逐一跑EXPLAIN检查type和key。我见过最典型的是把WHERE条件建在了一个无索引的状态字段上导致每条更新都要全表扫。这种问题的修复方案通常很简单根据实际业务查询模式建立合适的二级索引。不过也要注意不是所有字段都适合加索引索引过多会拖慢写入速度。一般会优先给“筛选性好、区分度高、出现在更新/删除条件里”的字段加索引比如order_id、user_id、trade_no这类业务主键。如果有些更新确实无法走索引比如统计类的全表扫描更新那就要从业务流程上审视这个更新能不能放到业务低峰期执行能不能改成小批次更新能不能用新建表替换的方式来绕过锁等待这些都是可落地的替代方案。6.3 把锁监控做成常态化巡检我始终相信生产故障的救命稻草不是临场反应而是日常监控。锁阻塞事故在发生之前几乎都有迹可循。只要监控到位通常会在业务完全卡死之前就收到告警并及时介入。我的巡检脚本会做这些事情每几分钟扫描一次information_schema.innodb_trx找出运行时间超过阈值的事务并关联出它的连接来源查询sys.innodb_lock_waits一旦出现锁等待关系立刻组合出等待方和阻塞方的线程ID并告警关注SHOW ENGINE INNODB STATUS里的死锁段落把死锁日志定期采集出来做分析如果开启了binlog可以定期统计大事务特征比如单事务写入量特别大或者事务持续时间特别长。另外我还会给关键业务表设置“锁等待超过N秒”的告警规则。不同业务的容忍度不同有的是毫秒级敏感有的可以接受几秒延迟这个阈值要根据实际压测结果来定不能拍脑袋。以我个人的排查习惯现在遇到锁问题已经不怎么慌了第一件事永远是看事务列表而不是看监控大盘先把“谁在攥锁”找出来再判断“该不该KILL”然后才是复盘根因。这套顺序看起来朴素但真的能在最混乱的故障现场帮你保持思路清晰。如果你也想在自己环境里练手建议故意开一个事务不提交再用另一个会话去更新同一行把本文中的查询语句全部跑一遍感受一下锁等待从产生、定位到处理的全过程。这种主动演练比等事故发生再救火要划算得多。