加关注这种话银家怎么好意思说出口嘛
什么是大事务 运行时间比较长,长时间未提交的事务就可以称为大事务
大事务产生的原因
操作的数据比较多
大量的锁竞争
事务中有其他非DB的耗时操作
大事务造成的影响
如何查询大事务
注:本文的sql的操作都是基于mysql5.7版本
以查询执行时间超过10秒的事务为例:
select * from information_schema.innodb_trx where TIME_TO_SEC(timediff(now(),trx_started))>10select * from information_schema.innodb_trx where TIME_TO_SEC(timediff(now(),trx_started))>10
如何避免大事务
1、通用解法
2、基于mysql5.7的解法
附录查询事务相关语句
注: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 # 查询所有正在运行的事务及运行时间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记录SELECTps.id 'PROCESS ID',ps.USER,ps.HOST,esh.EVENT_ID,trx.trx_started,esh.event_name 'EVENT NAME',esh.sql_text 'SQL',ps.timeFROMPERFORMANCE_SCHEMA.events_statements_history eshJOIN PERFORMANCE_SCHEMA.threads th ON esh.thread_id = th.thread_idJOIN information_schema.PROCESSLIST ps ON ps.id = th.processlist_idLEFT JOIN information_schema.innodb_trx trx ON trx.trx_mysql_thread_id = ps.idWHEREtrx.trx_id IS NOT NULLAND ps.USER != 'SYSTEM_USER'ORDER BYesh.EVENT_ID;# 简单查询事务锁select * from sys.innodb_lock_waits# 查询事务锁详细信息SELECTtmp.*,c.SQL_Text blocking_sql_text,p.HOST blocking_hostFROM(SELECTr.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_queryFROMinformation_schema.innodb_lock_waits wINNER JOIN information_schema.innodb_trx b ON b.trx_id = w.blocking_trx_idINNER 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 tWHEREtmp.blocking_thread = p.idAND t.thread_id = c.THREAD_IDAND t.PROCESSLIST_ID = p.id
参考
https://juejin.im/post/5d837d2c6fb9a06b05182737
https://www.cnblogs.com/zhixiang-org-cn/p/12454275.html