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

2023年 4月 30日 46.3k 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避免大事务以及大事务解决的方法的详细内容,更多关于MySQL 大事务的资料请关注每日运维其它相关文章!

相关文章

Oracle如何使用授予和撤销权限的语法和示例
Awesome Project: 探索 MatrixOrigin 云原生分布式数据库
下载丨66页PDF,云和恩墨技术通讯(2024年7月刊)
社区版oceanbase安装
Oracle 导出CSV工具-sqluldr2
ETL数据集成丨快速将MySQL数据迁移至Doris数据库

发布评论