💥 生产故障复盘
MySQL 全库锁死:一条 UPDATE 引发的雪崩
大事务锁表 → 连接池耗尽 → 从库延迟暴涨 → 全站写入阻塞
一、事故背景
周五下午 16:47,正在准备下班。监控系统突然连续报警:订单服务响应时间从 50ms 飙升到 15 秒,数据库连接池全部占满,新请求全部超时。紧接着客服群里涌进大量"下单失败""支付超时"的投诉。
这是一个典型的 MySQL 大事务锁表雪崩——从一条看似普通的 UPDATE 开始,经过行锁升级为表锁、连接池耗尽、从库延迟暴涨,最终演变成全站写入阻塞。
| 信息项 | 详情 |
|---|
| MySQL 版本 | MySQL 8.0.35(主从架构:1主2从) |
| 实例规格 | 16C 64GB,SSD 云盘,innodb_buffer_pool_size=48GB |
| 故障时间 | 周五 16:47 - 17:35(恢复约 48 分钟) |
| 影响范围 | 下单、支付、退款、库存扣减全部受影响 |
| 业务损失 | 约 2300 笔订单失败,直接交易额损失约 80 万 |
二、故障时间线
🔴 16:47 — 大面积超时告警爆发
Prometheus 告警连续触发:mysql_connection_pool_usage > 95%、http_request_duration_seconds{quantile="0.99"} > 10s。应用日志大量 ConnectionTimeoutException。
🔴 16:49 — 登录数据库排查
查看当前连接和锁情况
# 看当前线程数,已经打满 max_connections
SHOW GLOBAL STATUS LIKE "Threads_connected";
+-------------------+-------+
| Variable_name | Value |
+-------------------+-------+
| Threads_connected | 500 | # max_connections = 500,已经打满!
+-------------------+-------+
# 查看当前正在执行的线程
SHOW PROCESSLIST;
+-----+------+-----------------+------+---------+-------+-------+------------------+
| Id | User | Host | db | Command | Time | State | Info |
+-----+------+-----------------+------+---------+-------+-------+------------------+
| 12 | app | 10.0.1.101:45632| order| Query | 1800 | update| UPDATE order ... | # ⚠️ 已执行30分钟!
| 45 | app | 10.0.1.102:33010| order| Query | 120 | updating| UPDATE stock.. |
| 67 | app | 10.0.1.101:45678| order| Query | 89 | updating| INSERT INTO .. |
| ... | ... | ... | ... | ... | ... | ... | ... |
| 498 | app | 10.0.1.105:44901| order| Query | 45 | Waiting for table metadata lock|
| 499 | app | 10.0.1.105:44902| order| Query | 44 | Waiting for table metadata lock|
| 500 | app | 10.0.1.105:44903| order| Query | 43 | Waiting for table metadata lock|
+-----+------+-----------------+------+---------+-------+-------+------------------+
# ❗ 关键发现:线程 12 已执行 1800 秒(30分钟),状态是 "updating"!
🔴 16:52 — 锁等待分析
查看 InnoDB 锁信息
# InnoDB 状态中的锁等待
SHOW ENGINE INNODB STATUS\G
------------
TRANSACTIONS
------------
Trx id counter 12345678
Purge done for trx's n:o < 12340000 undo n:o < 0 state: 0 but
History list length 52300 # ⚠️ 未清理的 undo log 非常多!
---TRANSACTION 9876543, ACTIVE 1800 sec
mysql tables in use 1, locked 1
LOCK WAIT 3 lock struct(s), heap size 1136, 2 row lock(s)
MySQL thread id 12, OS thread handle 1401234567
# 大量线程在等这个事务释放锁:
---TRANSACTION 9876544, ACTIVE 120 sec starting index read
LOCK WAIT 2 lock struct(s), heap size 1136, 1 row lock(s)
MySQL thread id 45, query: UPDATE stock SET ...
------- TRX HAS BEEN WAITING 120 SEC FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 587 page no 4 n bits 72 index PRIMARY of table `order`.`t_order`
# ⚠️ 发现大量线程在等 trx 9876543 释放锁!
关键发现:线程 12(事务 9876543)已经运行了 30 分钟还没有提交或回滚,持有锁不释放,后面堆积了上百个等待锁的线程。
🔴 16:55 — 查看大事务 SQL
Performance Schema 查看完整 SQL
# 由于 SHOW FULL PROCESSLIST 可能截断,用 performance_schema
SELECT thread_id, processlist_id,
LEFT(sql_text, 500) as sql_text,
timer_wait/1000000000000 as seconds_wait
FROM performance_schema.events_statements_current
WHERE processlist_id = 12;
+-----------+----------------+--------------------------------------------------+
| thread_id | processlist_id | sql_text |
+-----------+----------------+--------------------------------------------------+
| 23 | 12 | UPDATE t_order SET status = 4, |
| | | updated_at = NOW() |
| | | WHERE create_time BETWEEN '2026-01-01' |
| | | AND '2026-03-31' |
| | | AND status IN (0,1,2,3) |
| | | AND ... |
| | | # 影响 280 万行! |
+-----------+----------------+--------------------------------------------------+
元凶找到了!这条 UPDATE 语句没有 LIMIT,按时间范围更新了 280 万行数据。在 InnoDB 中,UPDATE 会对每一行加行锁,直到事务提交才释放。280 万行的行锁意味着:
📌 所有涉及 t_order 表的读写请求都被阻塞
📌 连接池(500个)被等待锁的线程全部占满
📌 新的应用请求拿不到数据库连接,直接超时
📌 从库延迟暴增,因为 relay log 里有一个 30 分钟的大事务在回放
三、连锁反应分析
3.1 从库延迟暴涨
查看从库延迟
# 在从库上执行
SHOW SLAVE STATUS\G
# 关键指标:
Seconds_Behind_Master: 1847 # 从库延迟 30 分钟!
Relay_Log_Space: 12.5GB # relay log 已经堆积 12.5GB
# 从库为什么也卡了?
# 主库上的大事务是作为一个完整事务写入 binlog 的
# 从库回放时必须完整重放这 280 万行 UPDATE
# 回放期间,从库上同样会持有大量行锁
# 导致从库上的查询也全部被阻塞!
3.2 连接池雪崩
应用连接池配置:
应用连接池配置(Druid)
spring.datasource.druid:
max-active: 50 # 每个应用实例最大50个连接
min-idle: 10
max-wait: 5000 # 获取连接等待5秒
# 订单服务 10 个实例 × 50 = 500 个连接
# 雪崩过程:
# 1. 大事务占用1个连接,30分钟不释放
# 2. 其他请求拿到连接后,查询 t_order 被锁阻塞
# 3. 阻塞的连接不释放,新请求排队等连接
# 4. max-wait=5000ms 到后抛出 ConnectionTimeoutException
# 5. 应用不断重试,加剧连接池压力
# 6. 数据库 500 个 max_connections 全部占满
# 7. 新连接直接被拒绝:ERROR 1040: Too many connections
3.3 完整雪崩链路图
| 阶段 | 现象 | 影响 |
|---|
| T+0s | 运营后台执行全量 UPDATE t_order(280万行) | 大事务开始,持有大量行锁 |
| T+5s | 应用写入 t_order 开始等待锁 | 部分请求变慢,但不明显 |
| T+30s | 大量连接被锁等待占满 | 应用开始出现超时 |
| T+2min | 连接池耗尽,新请求全部拒绝 | 下单/支付 100% 失败 |
| T+5min | 从库延迟开始飙升 | 涉及读的页面也开始异常 |
| T+10min | max_connections 打满 | 数据库拒绝所有新连接 |
| T+30min | 大事务仍未完成 | History list length 持续增长,purge 线程跟不上 |
四、故障处理过程
第一步:紧急 kill 问题事务(16:58)
最快的方式就是杀掉那个长时间运行的大事务线程。
KILL 大事务线程
# ⚠️ 先确认线程信息,别杀错了
SELECT * FROM information_schema.innodb_trx\G
trx_id: 9876543
trx_state: RUNNING
trx_started: 2026-03-28 16:17:28
trx_requested_lock_id: NULL
trx_weight: 2847562 # 权重极高 = 持有大量行锁
trx_rows_modified: 2847562 # 修改了 284 万行!
# 确认无误,KILL
KILL 12; # 线程 ID
Query OK, 0 rows affected (0.00 sec)
# 等待几秒后,锁全部释放,连接数开始下降
SHOW GLOBAL STATUS LIKE "Threads_connected";
+-------------------+-------+
| Threads_connected | 120 | # 从 500 降到 120 ✅
+-------------------+-------+
✅ 17:02 — 大事务被 Kill,锁释放,应用连接恢复正常
第二步:检查数据一致性(17:05)
检查部分更新后的数据
# 大事务被 KILL 后,已更新的行不会回滚(已 COMMIT 的部分保留)
# 需要确认:UPDATE 是否是原子的(在一个事务内)
# 检查被更新了多少行
SELECT COUNT(*) FROM t_order
WHERE status = 4 AND updated_at > "2026-03-28 16:17:00";
+----------+
| COUNT(*) |
+----------+
| 1583247 | # ⚠️ 更新了一半就被 Kill 了
+----------+
# 由于 KILL 会导致事务回滚,已修改的 158 万行会自动回滚 ✅
# MySQL 的 InnoDB 保证事务原子性
# 验证回滚成功
SELECT COUNT(*) FROM t_order
WHERE status = 4 AND updated_at > "2026-03-28 16:17:00";
+----------+
| COUNT(*) |
+----------+
| 0 | # 已回滚 ✅
+----------+
第三步:确认从库同步恢复(17:10)
从库恢复检查
# 在从库上检查
SHOW SLAVE STATUS\G
Seconds_Behind_Master: 0 # 从库已追上 ✅
# 确认数据一致性
SELECT COUNT(*) FROM t_order;
# 主库结果: 12345678
# 从库结果: 12345678
# 数据一致 ✅
✅ 17:15 — 全部服务恢复正常,错误率归零
第四步:回溯问题 SQL 来源(17:15 - 17:35)
追溯问题 SQL 来源
# 查看 general_log(如果开启了)
# 或从应用审计日志中定位
# 最终定位到:运营后台的"批量关闭超时订单"功能
# 开发人员写了一个定时任务:
# 问题 SQL(由 MyBatis 生成):
UPDATE t_order
SET status = 4, updated_at = NOW()
WHERE create_time BETWEEN #{startTime} AND #{endTime}
AND status IN (0, 1, 2, 3)
AND ... -- 其他过滤条件
# ❌ 没有 LIMIT!也没有分批处理!
# 查看代码,发现问题出在定时任务:
# 1. 时间范围写错了:本来是清理 7 天前的,结果写成 3 个月前的
# 2. 没有 LIMIT 分页,一次性更新所有匹配行
# 3. 没有在事务中设置超时或行数限制
# 4. 定时任务在生产直接执行,没有先在测试环境验证
五、根因深度分析
5.1 直接原因
| 问题 | 详情 |
|---|
| 定时任务 SQL 无 LIMIT | 一次 UPDATE 影响 280 万行,无分批控制 |
| 时间参数错误 | 本应清理 7 天前订单,错写为 3 个月前 |
| 无事务超时控制 | 事务运行 30 分钟没有任何熔断 |
| 未走审批流程 | 开发直接上线定时任务,没有 DBA 审核 |
5.2 基础设施原因
| 问题 | 详情 |
|---|
| 没有慢查询拦截 | max_execution_time 未配置,大事务不告警 |
| 没有 SQL 审核 | 上线前无 SQL Review 流程 |
| 连接池缺少防护 | 没有配置连接泄漏检测和连接存活检查 |
| 监控维度不足 | 没有"活跃事务数"和"锁等待数量"的告警 |
5.3 流程原因
| 问题 | 详情 |
|---|
| 变更审批缺失 | 定时任务上线未经 DBA 和运维审批 |
| 测试覆盖不足 | 未在类生产数据量下测试定时任务 |
| 应急预案缺失 |
遇到大事务锁表没有标准 SOP |
六、修复与加固措施
6.1 代码修复:分批更新
修复后的定时任务代码(Java + MyBatis)
@Scheduled(cron = "0 0 3 * * ?") // 每天凌晨3点执行
public void closeTimeoutOrders() {
int batchSize = 1000;
int totalUpdated = 0;
int affected;
do {
// 每次只更新 1000 条
affected = orderMapper.closeTimeoutOrdersBatch(
LocalDateTime.now().minusDays(7), // 只清理7天前
batchSize
);
totalUpdated += affected;
if (affected > 0) {
log.info("批量关闭超时订单: 本批{}, 累计{}", affected, totalUpdated);
Thread.sleep(1000); // 每批间隔1秒,减少锁压力
}
} while (affected == batchSize); // 本批满额说明还有更多
log.info("超时订单关闭完成, 共处理 {} 条", totalUpdated);
}
修复后的 MyBatis SQL
UPDATE t_order
SET status = 4, updated_at = NOW()
WHERE status IN (0, 1, 2, 3)
AND create_time <= #{beforeTime}
ORDER BY create_time ASC
LIMIT #{batchSize}
6.2 MySQL 参数加固
my.cnf 加固配置
# 1. 事务超时(单位:秒)
# 超过此时间的事务会被自动回滚
innodb_kill_idle_transaction = 600 # 10分钟超时
# 2. 语句执行超时
# 单条 SELECT 的最大执行时间
max_execution_time = 300000 # 5分钟(毫秒)
# 3. 锁等待超时
# 等待锁超过此时间自动放弃
innodb_lock_wait_timeout = 10 # 10秒
# 4. 慢查询阈值
long_query_time = 2 # 超过2秒记录慢查询
slow_query_log = 1
# 5. 死锁检测
innodb_deadlock_detect = ON
# 6. undo log 清理
innodb_max_undo_log_size = 1G
innodb_purge_threads = 4 # 增加 purge 线程数
6.3 SQL 审核与拦截
SQL 审核规则(基于 GoInception 或 Yearning)
# 强制拦截规则:
# 1. UPDATE/DELETE 没有 WHERE 条件 → 拒绝
# 2. UPDATE/DELETE 没有 LIMIT 且影响行数 > 10000 → 人工审核
# 3. DDL 操作(ALTER TABLE/DROP TABLE)→ 人工审核
# 4. 不带索引列的 WHERE 条件 → 警告
# 示例:GoInception 审核结果
# [ER] UPDATE statement without LIMIT clause on large table
# Table: t_order, Estimated rows: 2800000
# Suggestion: Add LIMIT and process in batches
# [ER] Missing index on column used in WHERE clause
6.4 监控告警加固
Prometheus MySQL 告警规则
groups:
- name: mysql-production-alerts
rules:
# 活跃事务运行超过 60 秒
- alert: MysqlLongRunningTransaction
expr: mysql_global_status_innodb_row_lock_time_avg > 5000
for: 1m
labels:
severity: warning
annotations:
summary: "MySQL 存在长时间运行的事务"
# 锁等待数量超过 20
- alert: MysqlLockWaitHigh
expr: mysql_global_status_innodb_row_lock_waits > 20
for: 30s
labels:
severity: critical
# 连接数使用率超过 80%
- alert: MysqlConnectionUsageHigh
expr: mysql_global_status_threads_connected / mysql_global_variables_max_connections > 0.8
for: 2m
labels:
severity: warning
# 从库延迟超过 30 秒
- alert: MysqlReplicationLag
expr: mysql_slave_status_seconds_behind_master > 30
for: 1m
labels:
severity: critical
# undo log 堆积
- alert: MysqlUndoLogBacklog
expr: mysql_global_status_innodb_history_list_length > 100000
for: 5m
labels:
severity: warning
6.5 连接池加固
Druid 连接池防护配置
spring.datasource.druid:
max-active: 50
min-idle: 10
max-wait: 3000 # 等待时间缩短到 3 秒
# ✅ 连接泄漏检测
remove-abandoned: true # 开启泄漏检测
remove-abandoned-timeout: 180 # 连接超过 180 秒未归还则强制回收
log-abandoned: true # 记录泄漏的连接堆栈
# ✅ 连接存活检查
validation-query: SELECT 1
test-while-idle: true
test-on-borrow: false # 借出时不检查(性能考虑)
test-on-return: false
time-between-eviction-runs-millis: 60000 # 60秒检查一次空闲连接
min-evictable-idle-time-millis: 300000 # 空闲超过5分钟回收
6.6 运维 SOP:大事务应急手册
大事务应急处理 SOP
# ============== MySQL 大事务应急 SOP ==============
#
# 第一步:确认问题
# SHOW PROCESSLIST;
# 找到 Time 最大的线程
# 第二步:查看事务详情
# SELECT * FROM information_schema.innodb_trx\G
# 确认 trx_rows_modified(影响行数)和 trx_started(开始时间)
# 第三步:查看锁等待情况
# SELECT * FROM sys.innodb_lock_waits; # MySQL 5.7+
# 或 SELECT * FROM performance_schema.data_lock_waits; # MySQL 8.0+
# 第四步:评估是否 KILL
# 如果 trx_rows_modified > 10000 且 Time > 60s → 建议 KILL
# 如果 trx_rows_modified < 1000 → 可能快完成了,先观察
# 第五步:KILL 后检查
# KILL ;
# SHOW GLOBAL STATUS LIKE "Threads_connected";
# SHOW SLAVE STATUS\G # 确认从库延迟恢复正常
七、经验教训总结
这次故障暴露了六个致命短板,每个都值得所有团队反思:
❌ 教训一:大事务是 MySQL 的头号杀手
280 万行的 UPDATE 产生了 280 万把行锁,锁住了整张表。生产环境中的 DML(UPDATE/DELETE)必须有 LIMIT + 分批执行,绝不允许无限制的大事务。
✅ 规则:单事务影响行数不超过 5000 行,超过必须分批。
❌ 教训二:没有事务超时等于裸奔
一个事务运行 30 分钟都没有任何机制自动中断它。MySQL 的 innodb_kill_idle_transaction 参数可以设置事务超时自动回滚,但很多人不知道。
✅ 规则:生产环境设置事务超时 5-10 分钟,大查询执行超时 2-5 分钟。
❌ 教训三:SQL 上线必须有审核
定时任务代码直接上线,SQL 没有任何人审核。一个简单的 SQL Review 就能拦住这个低级错误。
✅ 规则:所有涉及 DML 的上线必须经过 DBA 审核,工具推荐 GoInception / Yearning。
❌ 教训四:缺少关键告警维度
监控了很多 CPU、内存、QPS 指标,但没有监控"活跃事务数"和"锁等待数量"。如果有这个告警,故障在发生 1 分钟内就能被发现。
❌ 教训五:连接池没有泄漏保护
连接池配置了 max-active=50,但没有配置 remove-abandoned。一个被锁住 30 分钟的连接永远不会被回收,直到耗尽所有连接。
❌ 教训六:定时任务没有防护
定时任务是最容易出问题的代码——它在非工作时间自动执行,出了问题没人第一时间发现,而且往往涉及大量数据处理。
✅ 规则:定时任务必须有行数上限、执行超时、异常告警。推荐用 XXL-JOB 等调度平台统一管理。
八、MySQL 锁表排查速查表(收藏备用)
| 排查目标 | 命令 | 说明 |
|---|
| 当前线程列表 | SHOW FULL PROCESSLIST; | 找 Time 最大的线程 |
| InnoDB 事务详情 | SELECT * FROM information_schema.innodb_trx\G | 看事务运行时间和影响行数 |
| InnoDB 锁等待 | SELECT * FROM sys.innodb_lock_waits; | MySQL 8.0 用 data_lock_waits |
| 锁等待的 SQL | SELECT * FROM performance_schema.events_statements_current WHERE ... | 完整 SQL 文本 |
| 杀掉问题线程 | KILL ; | 释放锁,回滚事务 |
| 连接数 | SHOW GLOBAL STATUS LIKE "Threads_connected"; | 看是否打满 |
| 从库延迟 | SHOW SLAVE STATUS\G | 看 Seconds_Behind_Master |
| undo log 堆积 | SHOW ENGINE INNODB STATUS\G | 看 History list length |
| 死锁信息 | SHOW ENGINE INNODB STATUS\G | 看 LATEST DETECTED DEADLOCK |
| 表锁信息 | SHOW OPEN TABLES WHERE In_use > 0; | 查看哪些表被锁 |
九、写在最后
这次故障的本质不是"代码写错了"这么简单,而是一连串防护缺失导致的必然结果:
代码没有分批处理 → SQL 没有审核 → 事务没有超时 → 连接池没有保护 → 监控没有告警 → 没有应急 SOP。
每一环单独看都不是致命问题,但全部缺失就会形成"完美风暴"。
📌 防大事务的核心六板斧:
• 代码层:UPDATE/DELETE 必须有 LIMIT + 分批执行
• 数据库层:配置事务超时 + 语句超时 + 锁等待超时
• 审核层:所有 DML 上线走 SQL Review 流程
• 监控层:活跃事务数 + 锁等待数量 + 连接数 + 从库延迟
• 连接池层:开启 remove-abandoned 泄漏检测
• 流程层:建立大事务应急 SOP,定期演练