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

桃扇骨 2023-01-03 14:06 124阅读 0赞

什么是大事务 运行时间比较长，长时间未提交的事务就可以称为大事务 大事务产生的原因 操作的数据比较多 大量的锁竞争 事务中有其他非DB的耗时操作 。。。 大事务造成的影响…  
什么是大事务  
运行时间比较长，长时间未提交的事务就可以称为大事务

大事务产生的原因  
操作的数据比较多  
大量的锁竞争  
事务中有其他非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

# 查询事务详细信息及执行的SQL #

select 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 b  
on a.TRX\_MYSQL\_THREAD\_ID=b.id and b.command = ‘Sleep’  
inner join performance\_schema.threads c ON b.id = c.PROCESSLIST\_ID  
inner 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-长事务详解  
https://juejin.cn/post/6844903945920315405  
面试官：你知道大事务会带来什么问题以及如何解决么？  
https://www.cnblogs.com/zhixiang-org-cn/p/12454275.html  
原文：  
https://blog.csdn.net/javaxuexilu/article/details/104769223  
文章出处:为什么要避免大事务以及大事务如何解决？