社区所有版块导航
Python
python开源   Django   Python   DjangoApp   pycharm  
DATA
docker   Elasticsearch  
aigc
aigc   chatgpt  
WEB开发
linux   MongoDB   Redis   DATABASE   NGINX   其他Web框架   web工具   zookeeper   tornado   NoSql   Bootstrap   js   peewee   Git   bottle   IE   MQ   Jquery  
机器学习
机器学习算法  
Python88.com
反馈   公告   社区推广  
产品
短视频  
印度
印度  
Py学习  »  DATABASE

MySQL 全库锁死:一条 UPDATE 引发的雪崩(完整复盘)

院长技术 • 7 月前 • 219 次点击  

💥 生产故障复盘

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+10minmax_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
锁等待的 SQLSELECT * 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,定期演练

Python社区是高质量的Python/Django开发社区
本文地址:http://www.python88.com/topic/194657