mysql如何避免长事务_mysql优化实践说明

长事务导致主从延迟和锁表，因其持续持锁、阻塞purge线程、延迟binlog刷盘，使从库回放卡在行锁等待；需通过INNODB_TRX定位超30秒事务，应用层设事务超时而非依赖lock_wait_timeout。

长事务为什么会导致主从延迟和锁表

MySQL 的长事务会持续持有锁、不释放 undo log、阻塞 purge 线程，还会让主库 binlog 无法及时刷盘。从库 replay 时若遇到被长事务占用的行级锁（尤其在 READ-COMMITTED 或 REPEATABLE-READ 隔离级别下），就会卡住，表现为 Seconds_Behind_Master 持续增长。

更隐蔽的问题是：即使事务没显式加锁，只要它执行了 SELECT ... FOR UPDATE 或更新了大量数据，就可能让 innodb_row_lock_time_avg 显著升高，拖慢其他并发请求。

• 避免在事务中做 HTTP 调用、文件读写、sleep() 等外部耗时操作
• 禁止在事务内调用 time.sleep()（Python）或 Thread.sleep()（Java）
• 应用层开启事务后，必须确保所有分支都执行 COMMIT 或 ROLLBACK，不能依赖连接池自动关闭来“清理”

如何快速定位正在运行的长事务

直接查 information_schema.INNODB_TRX 是最准的方式，配合 PROCESSLIST 可定位到具体连接和 SQL：

SELECT 
  trx_id,
  trx_started,
  TIMEDIFF(NOW(), trx_started) AS duration,
  trx_state,
  trx_query,
  trx_mysql_thread_id
FROM information_schema.INNODB_TRX 
WHERE TIME_TO_SEC(TIMEDIFF(NOW(), trx_started)) > 60
ORDER BY trx_s
tarted;

注意：trx_state = 'RUNNING' 不代表事务活跃，只是未提交；真正危险的是 trx_state = 'LOCK WAIT' 或持续超过 30 秒的 'ACTIVE' 状态。

• 搭配 SHOW PROCESSLIST 查看 Time 列是否远大于 trx_started 的差值（说明线程挂起）
• 如果 trx_query 为 NULL，说明事务处于空闲状态（比如应用拿到连接后没发 SQL 就卡住了）
• 监控脚本建议每 10 秒扫一次，阈值设为 30 秒而非 60 秒——很多业务超 30 秒就开始影响用户体验

SET SESSION innodb_lock_wait_timeout=5 有用吗

没用。这个参数只控制「等待锁」的超时，不控制「事务本身存活时间」。一个事务可以不争锁、纯计算、或者只读查询，照样能跑几小时，innodb_lock_wait_timeout 完全不生效。

真正有效的控制手段是数据库层的硬限制：

• MySQL 5.7+ 支持 max_execution_time：对单条语句生效，但对事务内的多条语句需每条都加 Hint，例如 SELECT /*+ MAX_EXECUTION_TIME(3000) */ * FROM t1
• 更可靠的是在应用层设置事务超时：Spring 的 @Transactional(timeout = 30)、Django 的 transaction.atomic(..., timeout=30)
• Proxy 层拦截（如 ProxySQL）可配置 mysql-query_rules 对匹配 BEGIN 的连接自动注入 SET SESSION wait_timeout = 30，但要注意 wait_timeout 是连接空闲超时，不是事务超时

binlog_format=ROW 下长事务对磁盘和网络的影响

在 ROW 格式下，长事务期间产生的所有 DML 都不会写入 binlog，直到 COMMIT 才一次性刷出全部变更事件。这意味着：

• 主库 binlog 文件体积突增，可能瞬间写满磁盘（尤其批量更新百万行）
• 主从之间产生“脉冲式”流量，从库 IO 线程在 COMMIT 时刻集中解析大量 event，容易触发 Slave_SQL_Running_State: Reading event from the relay log 卡顿
• binlog_cache_size 默认仅 32KB，大事务会频繁使用磁盘临时文件（binlog_cache_use 和 binlog_cache_disk_use 计数器飙升）

解决方案不是调大缓存，而是拆事务：用 LIMIT 分批提交，例如每次更新 5000 行后 COMMIT，并确保 WHERE 条件能命中索引，避免全表扫描拖慢每一批。