MySQL长事务：潜伏的数据库杀手！如何识别与消灭它？
当前位置：点晴教程→知识管理交流
→『 技术文档交流 』
你的数据库是否突然变慢甚至冻结？罪魁祸首可能正是那些被忽视的长事务！本文将揭示MySQL长事务的致命危害，并提供全套解决方案，让你的数据库重获新生！一、什么是长事务？一个定时炸弹想象你在超市结账：
|
|||||||||||||||||||||||||||||||||||||||||||||||||||||||||
| 特性 | 短事务（<1s） | 长事务（>5s） | ||||||
| 锁持有时间 | 极短 | 很长 | ||||||
| Undo使用 | 少量 | 巨大 | ||||||
| MVCC版本 | 0-1个 | 数十上百 | ||||||
| 内存占用 | 低 | 高 | ||||||
| 影响范围 | 局部 | 全局 |
400 186 1886
SELECT * FROM information_schema.INNODB_TRX\G
关键字段：
trx_started：事务开始时间
trx_query：最后执行的SQL
trx_rows_locked：锁定行数
-- 开启监控
UPDATE performance_schema.setup_instruments
SET ENABLED = 'YES'
WHERE NAME LIKE '%transaction%';
-- 查看长事务
SELECT * FROM performance_schema.events_transactions_current
WHERE TIMER_WAIT > 5000000000; -- 5秒
# my.cnf配置
[mysqld]
long_query_time = 5 -- 记录超过5秒的事务
log_slow_transactions = ON
slow_query_log = ON
SHOW ENGINE INNODB STATUS\G
---TRANSACTION 123456, ACTIVE 25 sec -- 长事务！
2 lock struct(s), 1354 lock(s), undo log entries 1200
MySQL thread id 789, OS thread handle 0x7f8b1c0a6700
-- 查找长事务ID
SELECT trx_mysql_thread_id
FROM information_schema.INNODB_TRX
WHERE TIME_TO_SEC(TIMEDIFF(NOW(), trx_started)) > 30;
-- 终止事务
KILL 789;
-- 问题查询（全表扫描）
SELECT * FROM orders
WHERE YEAR(create_time) = 2023
AND status = 'completed';
-- 添加索引
CREATE INDEX idx_create_status ON orders(create_time, status);
-- 重写查询
SELECT * FROM orders
WHERE create_time BETWEEN '2023-01-01' AND '2023-12-31'
AND status = 'completed';
-- 原始长事务
START TRANSACTION;
UPDATE ... -- 1万行
INSERT ... -- 5千行
COMMIT;
-- 拆分后
START TRANSACTION;
UPDATE ... LIMIT 1000; -- 分批处理
COMMIT;
START TRANSACTION;
UPDATE ... LIMIT 1000;
COMMIT;
-- 会话级超时
SET SESSION max_execution_time = 5000; -- 5秒
-- 全局超时
SET GLOBAL innodb_rollback_on_timeout = ON;
SET GLOBAL innodb_lock_wait_timeout = 30; -- 锁等待超时30秒
# Python伪代码示例
def execute_transaction():
attempts = 0
while attempts < 3:
try:
with db.transaction():
# 业务操作
db.execute("UPDATE ...")
db.execute("INSERT ...")
return True
except LockTimeoutError:
attempts += 1
sleep(1)
return False
-- 定期清理旧版本
SET GLOBAL innodb_purge_threads = 4; -- 增加清理线程
SET GLOBAL innodb_max_purge_lag = 100000; -- 控制清理延迟
// 错误示例
void processOrder() {
startTransaction(); // 过早开始
// 复杂计算...
updateInventory();
commit();
}
// 正确示例
void processOrder() {
// 复杂计算...
startTransaction();
updateInventory(); // 仅包装DB操作
commit();
}
-- 部署Prometheus监控
mysql_global_status_innodb_row_lock_time_avg
mysql_global_status_innodb_num_open_transactions
/* 创建长事务告警 */
CREATE EVENT check_long_transactions
ON SCHEDULE EVERY 1 MINUTE
DO
BEGIN
IF (SELECT COUNT(*) FROM information_schema.INNODB_TRX
WHERE TIME_TO_SEC(TIMEDIFF(NOW(), trx_started)) > 30) > 0
THEN
-- 触发告警
CALL send_alert('Long transactions detected!');
END IF;
END;
时间：大促期间
现象：数据库冻结，订单失败率飙升
紧急响应：15分钟无法恢复
-- 罪魁祸首
START TRANSACTION;
SELECT COUNT(*) FROM orders; -- 2亿行表，耗时5分钟
-- 忘记提交！
KILL 54321; -- 终止长事务
-- 添加汇总表
CREATE TABLE order_count (
date DATE PRIMARY KEY,
count INT
);
-- 定时更新
INSERT INTO order_count
SELECT CURRENT_DATE(), COUNT(*)
FROM orders ON DUPLICATE KEY UPDATE count = VALUES(count);
-- 查询优化
SELECT count FROM order_count WHERE date = CURRENT_DATE();
# my.cnf 推荐配置
[mysqld]
# 事务超时
innodb_lock_wait_timeout = 30
max_execution_time = 5000
# 长事务监控
long_query_time = 5
slow_query_log = 1
# Undo优化
innodb_undo_log_truncate = ON
innodb_max_undo_log_size = 1G
最后行动：立即执行以下命令检查你的数据库
SELECT
trx_id,
TIMEDIFF(NOW(), trx_started) AS duration,
trx_query
FROM information_schema.INNODB_TRX
WHERE TIME_TO_SEC(TIMEDIFF(NOW(), trx_started)) > 5;
核心原则：
🔄 事务越短越好 - 理想事务应在毫秒级完成
⚠️ 监控胜于救火 - 建立实时告警系统
🛡️ 预防重于治疗 - 从设计阶段规避风险
阅读原文：原文链接
