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

什么是大事务

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

大事务产生的原因

  • 操作的数据比较多
  • 大量的锁竞争
  • 事务中有其他非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 大事务的资料请关注我们其它相关文章！

赞 (0)

相关推荐

  • 5个MySQL GUI工具推荐，帮助你进行数据库管理

    MySQL的数据库管理工具非常多,有哪些优秀的GUI工具可以帮助提高工作效率?不妨看一看这5个MySQL GUI工具. 1.Navicat for MySQL Navicat for MySQL是数据库管理工具,与MySQL,MariaDB和云数据库兼容,是目前非常受欢迎的数据库前端用户界面工具.Navicat提供多达7种语言,它的功能丰富,能帮助简化数据库开发过程,并在执行管理任务时提高工作效率.不过该工具是付费的,有提供14天的免费试用. Navicat for MySQL有什么特点和功能?

  • Centos7 mysql数据库安装及配置实现教程

    一.系统环境 yum update升级以后的系统版本为 [root@yl-web yl]# cat /etc/redhat-release CentOS Linux release 7.1.1503 (Core) 二.mysql安装 一般网上给出的资料都是 #yum install mysql #yum install mysql-server #yum install mysql-devel 安装mysql和mysql-devel都成功,但是安装mysql-server失败,如下: [root

  • Python如何爬取51cto数据并存入MySQL

    实验环境 1.安装Python 3.7 2.安装requests, bs4,pymysql 模块 实验步骤1.安装环境及模块 可参考https://www.jb51.net/article/194104.htm 2.编写代码 # 51cto 博客页面数据插入mysql数据库 # 导入模块 import re import bs4 import pymysql import requests # 连接数据库账号密码 db = pymysql.connect(host='172.171.13.229

  • MYSQL SERVER收缩日志文件实现方法

    事务日志记录着在相关数据库上的操作,同时还存储数据库恢复(recovery)的相关信息. 收缩日志的原因有很多种,有些是考虑空间不足,有些则是应用程序限制导致的. 下面介绍的是在简单模式下,进行收缩操作. 一.命令行搞定 USE MASTER GO ALTER DATABASE {DBName} SET RECOVERY SIMPLE WITH NO_WAIT GO ALTER DATABASE {DBName} SET RECOVERY SIMPLE --简单模式 GO USE {DBName

  • MySQL 8.0统计信息不准确的原因

    前言 不管是Oracle还是MySQL,新版本推出的新特性,一方面给产品带来功能.性能.用户体验等方面的提升,另一方面也可能会带来一些问题,如代码bug.客户使用方法不正确引发问题等等. 案例分享 MySQL 5.7下的场景 (1)首先,创建两张表,并插入数据 mysql> select version(); +------------+ | version() | +------------+ | 5.7.30-log | +------------+ 1 row in set (0.00 s

  • MySQL因大事务导致的Insert慢实例分析

    [问题] INSERT语句是最常见的SQL语句之一,最近有台MySQL服务器不定时的会出现并发线程的告警,从记录信息来看,有大量insert的慢查询,执行几十秒,等待flushing log,状态query end [初步分析] 从等待资源来看,大部分时间消耗在了innodb_log_file阶段,怀疑可能是磁盘问题导致,经过排查没有发现服务器本身存在硬件问题 后面开启线程上升时pstack的自动采集,定位MySQL线程等待的位置. [分析过程] 部署了pstack的自动抓取后,出现过6次thr

  • MySQL如何快速导入数据

    前言: 日常学习和工作中,经常会遇到导数据的需求.比如数据迁移.数据恢复.新建从库等,这些操作可能都会涉及大量数据的导入.有时候导入进度慢,电脑风扇狂转真的很让人崩溃,其实有些小技巧是可以让导入更快速的,本篇文章笔者会谈一谈如何快速的导入数据. 注:本篇文章只讨论如何快速导入由逻辑备份产生的SQL脚本,其他文件形式暂不讨论. 1.尽量减小导入文件大小 首先给个建议,导出导入数据尽量使用MySQL自带的命令行工具,不要使用Navicat.workbench等图形化工具.特别是大数据量的时候,用My

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

    什么是大事务 运行时间比较长,长时间未提交的事务就可以称为大事务 大事务产生的原因 操作的数据比较多 大量的锁竞争 事务中有其他非DB的耗时操作 ... 大事务造成的影响 并发情况下,数据库连接池容易被撑爆 锁定太多的数据,造成大量的阻塞和锁超时 执行时间长,容易造成主从延迟 回滚所需要的时间比较长 undo log膨胀 ... 如何查询大事务 **注**:本文的sql的操作都是基于mysql5.7版本 以查询执行时间超过10秒的事务为例: select \* from information\

  • Oracle和MySQL的数据导入为何差别这么大

    经常会有一些朋友咨询我一些数据库的问题,我注意到一个很有意思的现象,凡是数据导入的问题,基本上都是Oracle类的,MySQL类的问题脑子里想了下竟然一次都没有. 我禁不住开始思考这个未曾注意的问题: 为什么Oracle导入数据会碰到很多的问题? 我们来梳理一下这个问题,分别从导出导入的方式来聊聊. 首先Oracle导出的文件格式就没打算让你拿来即用,导出文件叫做dump,换句话说可以理解这是一个二进制文件.当然实际上这个文件还是有很多的方式去抓取一些关键的信息,比如dump头部的信息可以通过s

  • JAVA Spring中让人头痛的JAVA大事务问题要如何解决你知道吗

    JAVA Spring中让人头痛的JAVA大事务问题要如何解决你知道吗

    目录 前言 大事务引发的问题 解决办法 少用@Transactional注解 将查询(select)方法放到事务外 事务中避免远程调用 事务中避免一次性处理太多数据 非事务执行 总结 前言 最近有个网友问了我一个问题:系统中大事务问题要如何处理? 正好前段时间我在公司处理过这个问题,我们当时由于项目初期时间比较紧张,为了快速完成业务功能,忽略了系统部分性能问题.项目顺利上线后,专门抽了一个迭代的时间去解决大事务问题,目前已经优化完成,并且顺利上线.现给大家总结了一下,我们当时使用的一些解决办法,

  • Mysql表数据比较大情况下修改添加字段的方法实例

    Mysql表数据比较大情况下修改添加字段的方法实例

    前言 如果一张表在后期的维护中,发现需要加字段以满足当下的需求,但是数据量很大有百万甚至千万级的数据,要如何修改表字段呢. 直接执行使用alter语句肯定是不现实的,这涉及到锁表重建表结构等操作,假设这时候还有其他线程在跑,等一天都改不过来. 这里整理一个比较简单的方法 1.对照要操作的表结构创建一张临时表 CREATE TABLE product_copy LIKE product; 2.将要修改的表结构改在临时表上面 3.导出表product数据,并导入到零时表product_copy 4.

  • MySQL脏读幻读不可重复读及事务的隔离级别和MVCC、LBCC实现

    MySQL脏读幻读不可重复读及事务的隔离级别和MVCC、LBCC实现

    目录 前言 事务因并发出现的问题有哪些 脏读 不可重复读 幻读 不可重复读与幻读的区别 事务的四个隔离级别 InnoDB默认的隔离级别是RR Read UnCommited 读未提交 RU Read Commited 读已提交 RC Repeatable Read 可重复读 RR Serializable 串行化 undo 版本链 read view MVCC(Multi-Version Concurrent Control )多版本并发控制 可重复读实现 读已提交实现 LBCC 锁的类型 共享

  • PHP使用Redis实现防止大并发下二次写入的方法

    本文实例讲述了PHP使用Redis实现防止大并发下二次写入的方法.分享给大家供大家参考,具体如下: PHP调用redis进行读写操作,大并发下会出现:读取key1,没有内容则写入内容,但是大并发下会出现同时多个php进程写入的情况,这个时候需要加一个锁,即获取锁的php进程有权限写. $lock_key = 'LOCK_PREFIX' . $redis_key; $is_lock = $redis->setnx($lock_key, 1); // 加锁 if($is_lock == true){

  • Mysql事务操作失败如何解决

    Mysql事务操作失败如何解决 事务的原子性 :事务是数据库的逻辑工作单位,事务中包含的各操作要么都做,要么都不做 . 要实现事务的原子性,单单靠一条commit或是rollback命令还是不行的,因为例如commit命令它只是将一个事务中执行成功的DML语句提交给数据库里.如果要实现事务的原子性,则就需要commit和rollback命令配合上程序上的一个业务逻辑才能可以,具体业务逻辑代码如下示例代码: 1.现象 程序中打开了事务进行插入,但是没有commit,表中的数据已经存在,就是回滚也不

  • linux 查找大目录和大文件的方法(推荐)

    今天,在机器上执行命令的时候,发现tab键无法补全了,原因竟然是磁盘空间满了,使用df命令看了一下,确实如此,每个分区的使用率都得到100%了,因此想找到系统中的大目录和大文件,删除一部分. 主要涉及到两个命令 du和find du命令(查找系统中的大目录): -h已易读的格式显示指定目录或文件的大小 -s选项指定对于目录不详细显示每个子目录或文件的大小 -m或–megabytes 以1MB为单位 –max-depth=1:其中,数字"1"是指查询结果中最多显示的目录层数,这里指最多显

  • win2008 iis7 上传大文件限制的真正解决办法

    win2008 iis7 上传大文件限制的真正解决办法

    iis7 上传大文件限制的真正解决办法 修改IIS_schema.xml这个文件要先获得这个文件的控制权; 进入目录C:\Windows\System32\inetsrv\config\schema,修改文件IIS_schema.xml 权限: 进入IIS_schema.xml文件权限修改,选择"高级" 选择"所有者" 选中 administrators 确定 再进入权限编辑,修改administrators 完全控制. 再去掉IIS_schema.xml的只读属性

  • asp.net(C#)中上传大文件的几中常见应用方法

    几种常见的方法,本文主要内容包括: 第一部分:首先我们来说一下如何解决ASP.net中的文件上传大小限制的问题,我们知道在默认情况下ASP.NET的文件上传大小限制为2M,一般情况下,我们可以采用更改Web.Config文件来自定义最大文件大小,如下: 这样上传文件的最大值就变成了4M,但这样并不能让我们无限的扩大 MaxRequestLength的值,因为ASP.NET会将全部文件载入内存后,再加以处理.解决的方法是利用隐含的 HttpWorkerRequest,用它的GetPreloaded

随机推荐