MySQL为什么要避免大事务以及大事务解决的方法

什么是大事务

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

大事务产生的原因

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

大事务造成的影响

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

如何查询大事务

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

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

?

1	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版本

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121

# 查询所有正在运行的事务及运行时间   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避免大事务以及大事务解决的方法的详细内容，更多关于MySQL 大事务的资料请关注服务器之家其它相关文章！

原文链接：https://cloud.tencent.com/developer/article/1664089