大家好我是小耶写功课只是为了我踩过的坑你们别再踩了前几周我们讲了死锁排查——怎么读SHOW ENGINE INNODB STATUS、怎么从日志里找到两个冲突的事务。但死锁只是锁问题的“冰山一角”。死锁会直接报错你一眼就能看到。但真正让系统“卡住”的往往是那些不报错、不告警、只默默等待的锁等待问题。一条SQL平时0.1秒今天突然3秒。执行计划没变、索引没坏、数据量也没暴涨。你翻遍慢查询日志找不到原因——问题可能不在SQL本身而在它“等锁”了。今天把锁等待的排查方法彻底拆开讲一遍。一、死锁 vs 锁等待两种截然不同的锁问题先搞清楚这两个概念的区别死锁Deadlock两个事务互相持有对方需要的锁形成循环等待。数据库检测到后会主动回滚其中一个事务报Deadlock found错误。特点是“报错”——DBA能直接看到。锁等待Lock Wait一个事务在等另一个事务释放锁但另一个事务还在正常执行没有形成循环。被阻塞的事务会一直等直到超时innodb_lock_wait_timeout默认50秒。特点是不报错——业务只是变慢没有错误日志。死锁是“急诊”——需要立即处理。锁等待是“慢性病”——它不会一下子让系统崩溃但会让系统越来越慢而你很难找到病根。二、锁等待的三种典型场景场景一长事务持有锁不释放一个事务执行了UPDATE但没有提交然后去调用外部API、做复杂计算、或者等用户输入。在此期间它持有的锁一直不释放其他事务全部被阻塞。场景二大事务分批执行不当一个事务里包含了大量操作——比如一次性更新100万行。事务执行期间锁一直持有其他事务被长时间阻塞。场景三热点行竞争多个事务同时操作同一行数据比如秒杀场景下的库存扣减。虽然每个事务都很快但高并发下锁等待时间叠加整体响应变慢。三、锁等待排查的核心工具工具一SHOW ENGINE INNODB STATUS这个命令除了显示死锁信息还会显示当前的锁等待情况。在LATEST DETECTED DEADLOCK之后还有一个TRANSACTIONS部分会列出当前所有活跃事务及其持有的锁。SHOW ENGINE INNODB STATUS\G找到TRANSACTIONS部分重点关注ACTIVE时间——事务活跃了多久LOCK WAIT——是否在等待锁HOLDS THE LOCK(S)——持有哪些锁WAITING FOR THIS LOCK——在等哪个锁工具二INFORMATION_SCHEMA.INNODB_TRX这是最常用的锁等待监控视图。它显示当前所有活跃事务的详细信息SELECT trx_id, trx_state, trx_started, trx_mysql_thread_id, trx_query, trx_rows_locked, trx_rows_modified, TIMESTAMPDIFF(SECOND, trx_started, NOW()) AS trx_duration_sec FROM information_schema.INNODB_TRX WHERE trx_state RUNNING ORDER BY trx_started;关键字段解读字段含义诊断价值trx_started事务开始时间判断是否长事务trx_rows_locked锁定的行数锁范围有多大trx_rows_modified修改的行数事务大小trx_query当前执行的SQL定位具体操作工具三INFORMATION_SCHEMA.INNODB_LOCK_WAITS这个视图直接显示谁在等谁SELECT * FROM information_schema.INNODB_LOCK_WAITS;输出中包含requesting_trx_id等待的事务和blocking_trx_id阻塞的事务可以直接看出锁等待链。四、锁等待链的完整排查流程第一步找出所有在等待锁的事务SELECT * FROM information_schema.INNODB_TRX WHERE trx_state LOCK WAIT;第二步找出谁在阻塞它们SELECT waiting_trx_id, waiting_thread, blocking_trx_id, blocking_thread FROM sys.innodb_lock_waits;schema是MySQL 5.7自带的系统库比直接查INFORMATION_SCHEMA更方便。第三步检查阻塞事务在做什么拿到blocking_trx_id后回到INNODB_TRX查看该事务的详细信息SELECT trx_id, trx_started, trx_query, trx_rows_locked, trx_rows_modified FROM information_schema.INNODB_TRX WHERE trx_id blocking_trx_id;第四步判断阻塞事务是否可以终止如果事务活跃时间很长10秒且一直没有提交 → 可能是应用代码忘记提交或回滚如果事务正在执行一个很慢的SQL → 可能需要优化该SQL如果事务处于SLEEP状态 → 可能是连接池中的空闲连接持有锁第五步决定处理方式如果阻塞事务是“僵尸事务”应用已经断开但事务未提交→ 执行KILL终止该连接如果阻塞事务是正常业务但执行时间过长 → 优化SQL或拆分事务如果阻塞事务是热点行竞争 → 考虑优化业务逻辑或使用乐观锁五、实战案例一个真实的锁等待排查现象某电商系统下午3点开始订单创建接口响应时间从200ms飙升到3秒持续了20分钟后自动恢复。排查过程查看INNODB_TRX发现有13个事务处于LOCK WAIT状态通过sys.innodb_lock_waits找到阻塞者一个事务ID为310298的事务查看该事务信息trx_started是25分钟前trx_rows_modified5000trx_queryNULL该事务处于SLEEP状态说明应用已经断开了连接但事务没有提交或回滚根因应用代码中有一个Transactional注解的方法内部调用了外部API。外部API超时导致方法异常退出但事务没有回滚锁一直持有。后续所有操作同一行数据的请求全部被阻塞。解决方案事务中不调用外部API将外部调用移到事务外。或者在Transactional中设置超时时间。六、锁等待对系统性能的“隐性影响”锁等待不像死锁那样“显眼”但它对系统性能的影响可能更大影响一响应时间线性增加一个查询本身0.1秒但等锁等了0.5秒总响应时间变成0.6秒。如果每秒有100个这样的查询系统整体响应时间就会明显变慢。影响二连接池耗尽大量请求被阻塞等待锁连接池中的连接被占用但无法释放。新请求无法获取连接业务直接报错。影响三连锁阻塞一个长事务阻塞了10个请求这10个请求又各自持有其他锁阻塞了更多请求——形成连锁反应。七、锁等待的预防策略1. 监控长事务建议设置阈值告警事务活跃时间超过10秒自动告警。虽然long_query_time无法覆盖事务场景SQL已经执行完但事务未提交慢查询日志不会记录但可以用INNODB_TRX定期扫描实现告警。-- 每10秒执行一次检测活跃超过10秒的事务 SELECT trx_id, trx_started, trx_mysql_thread_id, TIMESTAMPDIFF(SECOND, trx_started, NOW()) AS duration_sec FROM information_schema.INNODB_TRX WHERE trx_state RUNNING AND TIMESTAMPDIFF(SECOND, trx_started, NOW()) 10;2. 拆分长事务避免在一个事务中处理大量数据。将大事务拆分为多个小事务每批处理一部分数据并提交。3. 事务中不调用外部API事务中调用外部API是不可控的——外部API可能超时、可能报错、可能响应很慢。在外部API返回之前事务持有的锁一直不释放。正确做法是外部调用放在事务之前或之后事务内只做数据库操作。4. 设置合理的锁超时MySQL的innodb_lock_wait_timeout默认50秒。如果业务对响应时间敏感可以适当降低这个值让被阻塞的事务更快失败而不是长时间等待。八、总结锁等待是比死锁更隐蔽、更难发现的性能问题。它不报错、不告警只默默让系统变慢。排查锁等待的核心工具SHOW ENGINE INNODB STATUS查看当前锁状态INFORMATION_SCHEMA.INNODB_TRX查看所有活跃事务INFORMATION_SCHEMA.INNODB_LOCK_WAITS查看锁等待链sys.innodb_lock_waits一站式查看锁等待关系预防锁等待的三个关键监控长事务——设置阈值告警主动发现拆分长事务——大事务拆小减少锁持有时间事务中不调用外部API——外部调用不可控锁不能跟着等死锁是“急诊”锁等待是“慢性病”。一个DBA的进阶就是从只会处理“急诊”到能诊断“慢性病”。小耶在手SQL 不愁还有什么想了解的欢迎留言小耶一定知无不言言无不尽……我们下次见~
