MYSQL怎么发现及处理没有commit 留下的大麻烦

发布时间：2021-10-25 09:20:25 作者：柒染
来源：亿速云阅读：159

# MySQL怎么发现及处理没有commit留下的大麻烦

## 引言

在MySQL数据库运维中，未提交的事务（uncommitted transaction）就像一颗定时炸弹，轻则导致锁等待和性能下降，重则引发数据不一致甚至系统崩溃。本文将深入剖析未提交事务的检测方法、危害场景以及系统性解决方案。

## 一、未提交事务的危害场景

### 1. 锁资源长期占用
```sql
-- 示例：会话A执行后未提交
BEGIN;
UPDATE users SET balance = balance - 100 WHERE id = 1;
-- 忘记执行COMMIT;

此时其他会话尝试修改同一行数据时会被阻塞：

-- 会话B将被挂起
UPDATE users SET balance = balance + 200 WHERE id = 1;

2. 连接池耗尽

长时间运行的事务会导致： - 连接无法释放回连接池 - 新请求无法获取连接 - 应用出现”Too many connections”错误

3. 主从复制延迟

在REPEATABLE-READ隔离级别下，事务持续期间： - 二进制日志无法purge - 从库需要维护大量undo日志 - 可能导致复制延迟数小时

二、检测未提交事务的5种方法

1. 查询information_schema

SELECT * FROM information_schema.INNODB_TRX\G

关键字段说明： - trx_started：事务开始时间 - trx_mysql_thread_id：连接线程ID - trx_query：最后执行的SQL

2. 监控processlist

SHOW FULL PROCESSLIST;

特征识别： - Command列显示”Sleep”但Time值很大 - Info列为NULL但State持续不变

3. 性能日志分析

# my.cnf配置
[mysql]
log_queries_not_using_indexes = 1
long_query_time = 2

通过慢查询日志发现： - 没有明确结束标记的事务 - 执行时间异常长的DML语句

4. InnoDB状态检查

SHOW ENGINE INNODB STATUS\G

重点关注：

---TRANSACTION 123456, ACTIVE 3600 sec
2 lock struct(s), heap size 1136, 1 row lock(s)

5. 操作系统层面检测

# 查看持有锁的线程
mysqladmin debug | grep -A 10 "Locked threads"

三、系统化解决方案

1. 预防性措施

-- 设置事务超时（MySQL 5.7+）
SET GLOBAL innodb_lock_wait_timeout = 30;
SET GLOBAL max_execution_time = 60000;

-- 应用层最佳实践
START TRANSACTION READ WRITE;
-- 业务逻辑
COMMIT; -- 或 ROLLBACK;

2. 自动化监控方案

# 监控脚本示例
import pymysql
from datetime import datetime, timedelta

def check_long_transactions():
    conn = pymysql.connect(host='localhost', user='monitor')
    with conn.cursor() as cursor:
        cursor.execute("""
            SELECT t.trx_started, t.trx_mysql_thread_id, 
                   TIMESTAMPDIFF(SECOND, t.trx_started, NOW()) as duration_sec
            FROM information_schema.INNODB_TRX t
            WHERE t.trx_started < NOW() - INTERVAL 10 MINUTE
            ORDER BY duration_sec DESC
        """)
        return cursor.fetchall()

3. 紧急处理流程

-- 步骤1：确认事务详情
SELECT * FROM performance_schema.events_statements_history 
WHERE thread_id = (SELECT THREAD_ID FROM performance_schema.threads 
                  WHERE PROCESSLIST_ID = 问题线程ID);

-- 步骤2：尝试温和终止
KILL QUERY 线程ID;

-- 步骤3：强制终止
KILL 线程ID;

4. 架构层面优化

使用连接中间件（如ProxySQL）实现：
- 事务超时自动kill
- 最大执行时间控制
部署PT-kill工具定期清理

四、经典案例分析

案例1：电商系统库存死锁

现象： - 凌晨促销期间出现大量”Lock wait timeout”错误 - 库存扣减接口成功率降至60%

根因： - 应用框架未正确处理事务边界 - 15%的事务持续超过30秒未提交

解决方案： 1. 引入Spring @Transactional注解超时配置 2. 增加事务监控看板 3. 优化批量处理为小事务

五、总结 checklist

[ ] 配置事务超时参数
[ ] 部署自动化监控脚本
[ ] 定期审计应用代码事务逻辑
[ ] 建立事务处理SOP文档
[ ] 关键业务实现事务补偿机制

通过系统化的预防、检测和处理机制，可以有效避免未提交事务引发的生产事故。建议结合业务特点制定适合的治理策略。 “`

（注：实际字数约1150字，可根据需要调整细节部分的详略程度）