MySQL長事務：潛伏的數(shù)據(jù)庫殺手！如何識別與消滅它？
當前位置：點晴教程→知識管理交流
→『 技術文檔交流 』
你的數(shù)據(jù)庫是否突然變慢甚至凍結？罪魁禍首可能正是那些被忽視的長事務！本文將揭示MySQL長事務的致命危害，并提供全套解決方案，讓你的數(shù)據(jù)庫重獲新生！一、什么是長事務？一個定時炸彈想象你在超市結賬：
|
|||||||||||||||||||||||||||||||||||||||||||||||||||||||||
| 特性 | 短事務（<1s） | 長事務（>5s） | ||||||
| 鎖持有時間 | 極短 | 很長 | ||||||
| Undo使用 | 少量 | 巨大 | ||||||
| MVCC版本 | 0-1個 | 數(shù)十上百 | ||||||
| 內存占用 | 低 | 高 | ||||||
| 影響范圍 | 局部 | 全局 |
400 186 1886
SELECT * FROM information_schema.INNODB_TRX\G
關鍵字段：
trx_started：事務開始時間
trx_query：最后執(zhí)行的SQL
trx_rows_locked：鎖定行數(shù)
-- 開啟監(jiān)控
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():
# 業(yè)務操作
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監(jiān)控
mysql_global_status_innodb_row_lock_time_avg
mysql_global_status_innodb_num_open_transactions
/* 創(chuàng)建長事務告警 */
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
-- 觸發(fā)告警
CALL send_alert('Long transactions detected!');
END IF;
END;
時間：大促期間
現(xiàn)象：數(shù)據(jù)庫凍結，訂單失敗率飆升
緊急響應：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);
-- 查詢優(yōu)化
SELECT count FROM order_count WHERE date = CURRENT_DATE();
# my.cnf 推薦配置
[mysqld]
# 事務超時
innodb_lock_wait_timeout = 30
max_execution_time = 5000
# 長事務監(jiān)控
long_query_time = 5
slow_query_log = 1
# Undo優(yōu)化
innodb_undo_log_truncate = ON
innodb_max_undo_log_size = 1G
最后行動：立即執(zhí)行以下命令檢查你的數(shù)據(jù)庫
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;
核心原則：
?? 事務越短越好 - 理想事務應在毫秒級完成
?? 監(jiān)控勝于救火 - 建立實時告警系統(tǒng)
??? 預防重于治療 - 從設計階段規(guī)避風險
閱讀原文：原文鏈接
