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

什么是大事务

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

大事务产生的起因

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

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