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

select \* from information\_schema.innodb\_trx where TIME\_TO\_SEC(timediff(now(),trx\_started))>10

# 查询所有正在运行的事务及运行时间

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