【MySQL】长事务

一. 长事务原因

1. set autocommit=0

  1. 这个命令会关闭当前线程的事务自动提交功能
  1. 意味着如果只执行一个 select 语句,这个事务就启动了,并且不会自动提交。
    这个事务持续存在直到主动执行 commit 或 rollback 语句,或者断开连接。如果是长连接,就导致了长事务。
  1. 有些客户端连接框架会默认连接成功后先执行一个 set autocommit=0 的命令。这就导致接下来的查询都在事务中,如果是长连接,就导致了长事务。
  1. 所以建议使用【set autocommit=1】, 通过显式语句的方式来启动事务。

2. 事务方法业务复杂,执行时间长


二. 长事务危害

1. 占用大量的存储空间

  1. 在 MySQL 中,实际上每条记录在更新的时候都会同时记录一条回滚操作。记录更新前的值,通过回滚操作,就可以得到前一个状态的值。
  2. 这些记录下来的回滚操作就是回滚段,长事务意味着系统里面会存在很老的回滚段。由于这些事务随时可能访问数据库里面的任何数据,所以这个事务提交之前,这些回滚记录数据都必须保留,这就会导致占用大量的存储空间。

在这里插入图片描述


2. 占用锁资源,甚至拖垮整个库

  1. 事务内的增删改操作都会对数据加锁,在事务提交或回滚前会一直占用锁资源

三. 查看长事务

  1. 查找持续时间超过 60s 的事务
select * from information_schema.innodb_trx where TIME_TO_SEC(timediff(now(),trx_started))>60

四. 解决长事务

1. 程序端

  1. 确认是否使用了【set autocommit=0】,建议使用【set autocommit=1】。
  2. 确认是否有不必要的只读事务。
  3. 通过 SET MAX_EXECUTION_TIME 命令,来控制每个语句执行的最长时间,避免单个语句执行太长时间。
  4. 通过【消息队列、异步线程】分离事务方法内的业务,减少事务方法的执行时间。

2. 数据库端

  1. 监控 information_schema.Innodb_trx 表,设置长事务阈值,超过就报警 或者 kill。
  2. 如果使用的是 MySQL 5.6 或者更新版本,把 innodb_undo_tablespaces 设置成 2(或更大的值)。如果真的出现大事务导致回滚段过大,这样设置后清理起来更方便。
  3. 在业务功能测试阶段要求输出所有的 general_log,分析日志行为提前发现问题。

五. 参考资料

1. mysql 之general_log日志开启详解以及清空

https://blog.csdn.net/u010735147/article/details/81871560

posted @ 2021-04-23 17:34    阅读(248)  评论(0编辑  收藏  举报