为什么要避免大事务以及大事务如何解决？

什么是大事务

运行时间比较长，长时间未提交的事务就可以称为大事务

大事务产生的原因

• 操作的数据比较多
• 大量的锁竞争
• 事务中有其他非DB的耗时操作
• 。。。

大事务造成的影响

• 并发情况下，数据库连接池容易被撑爆
• 锁定太多的数据，造成大量的阻塞和锁超时
• 执行时间长，容易造成主从延迟
• 回滚所需要的时间比较长
• undo log膨胀
• 。。。

如何查询大事务

注：本文的sql的操作都是基于mysql5.7版本

以查询执行时间超过10秒的事务为例：

select * from information_schema.innodb_trx where TIME_TO_SEC(timediff(now(),trx_started))>10

如何避免大事务

通用解法

• 在一个事务里面， 避免一次处理太多数据
• 在一个事务里面，尽量避免不必要的查询
• 在一个事务里面， 避免耗时太多的操作，造成事务超时。一些非DB的操作，比如rpc调用，消息队列的操作尽量放到事务之外操作

基于mysql5.7的解法

• 在InnoDB事务中，行锁是在需要的时候才加上的，但并不是不需要了就立刻释放，而是要等到事务结束时才释放。如果你的事务中需要锁多个行，要把最可能造成锁冲突、最可能影响并发度的锁尽量往后放
• 通过SETMAX_EXECUTION_TIME命令， 来控制每个语句查询的最长时间，避免单个语句意外查询太长时间
• 监控 information_schema.Innodb_trx表，设置长事务阈值，超过就报警/或者kill
• 在业务功能测试阶段要求输出所有的general_log，分析日志行为提前发现问题
• 设置innodb_undo_tablespaces值，将undo log分离到独立的表空间。如果真的出现大事务导致回滚段过大，这样设置后清理起来更方便

附录查询事务相关语句

注：sql语句都是基于mysql5.7版本

# 查询所有正在运行的事务及运行时间select t.*,to_seconds(now())-to_seconds(t.trx_started) idle_time from INFORMATION_SCHEMA.INNODB_TRX t# 查询事务详细信息及执行的SQLselect now(),(UNIX_TIMESTAMP(now()) - UNIX_TIMESTAMP(a.trx_started)) diff_sec,b.id,b.user,b.host,b.db,d.SQL_TEXT from information_schema.innodb_trx a inner join information_schema.PROCESSLIST bon a.TRX_MYSQL_THREAD_ID=b.id and b.command = 'Sleep'inner join performance_schema.threads c ON b.id = c.PROCESSLIST_IDinner join performance_schema.events_statements_current d ON d.THREAD_ID = c.THREAD_ID;# 查询事务执行过的所有历史SQL记录SELECT ps.id 'PROCESS ID', ps.USER, ps.HOST, esh.EVENT_ID, trx.trx_started, esh.event_name 'EVENT NAME', esh.sql_text 'SQL', ps.time FROM PERFORMANCE_SCHEMA.events_statements_history esh JOIN PERFORMANCE_SCHEMA.threads th ON esh.thread_id = th.thread_id JOIN information_schema.PROCESSLIST ps ON ps.id = th.processlist_id LEFT JOIN information_schema.innodb_trx trx ON trx.trx_mysql_thread_id = ps.id WHERE trx.trx_id IS NOT NULL AND ps.USER != 'SYSTEM_USER' ORDER BY esh.EVENT_ID; # 简单查询事务锁 select * from sys.innodb_lock_waits # 查询事务锁详细信息 SELECT tmp.*, c.SQL_Text blocking_sql_text, p.HOST blocking_host FROM ( SELECT r.trx_state wating_trx_state, r.trx_id waiting_trx_id, r.trx_mysql_thread_Id waiting_thread, r.trx_query waiting_query, b.trx_state blocking_trx_state, b.trx_id blocking_trx_id, b.trx_mysql_thread_id blocking_thread, b.trx_query blocking_query FROM information_schema.innodb_lock_waits w INNER JOIN information_schema.innodb_trx b ON b.trx_id = w.blocking_trx_id INNER JOIN information_schema.innodb_trx r ON r.trx_id = w.requesting_trx_id ) tmp, information_schema.PROCESSLIST p, PERFORMANCE_SCHEMA.events_statements_current c, PERFORMANCE_SCHEMA.threads t WHERE tmp.blocking_thread = p.id AND t.thread_id = c.THREAD_ID AND t.PROCESSLIST_ID = p.id 

参考

MySQL-长事务详解

面试官：你知道大事务会带来什么问题以及如何解决么？

为什么要避免大事务以及大事务如何解决？递四方、 shopyy、 parenthood、 Amazon站内CPC广告到底应该怎样做？、 亚马逊站内广告投放新手攻略 、 亚马逊卖家研究竞争对手，需要关注哪些因素？.、 去泰国旅游注意事项 2017绝对实用泰国旅游攻略、 杭州旅游资讯 杭州千岛湖旅游全攻略、 厦门鼓浪屿旅游 厦门旅游全攻略、