什么是大事务

运行工夫比拟长，长时间未提交的事务就能够称为大事务

大事务产生的起因

  • 操作的数据比拟多
  • 大量的锁竞争
  • 事务中有其余非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-长事务详解

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