MySQL查询正在运行的长事务，及长事务语句(未整理，仅作笔记，后续整理)

查询正在执行的长事务

-- 查询长事务
select c.thd_id,c.conn_id,concat(PROCESSLIST_USER,'@',PROCESSLIST_HOST) User,
FORMAT_PICO_TIME(a.timer_wait) AS trx_duration,c.command,
 c.current_statement as latest_statement
 from performance_schema.events_transactions_current as a
inner join information_schema.innodb_trx as b on a.thread_id = PS_THREAD_ID(b.trx_mysql_thread_id)
inner join sys.processlist as c on  c.thd_id=a.thread_id
INNER JOIN performance_schema.threads d  on a.thread_id = d.thread_id
order by  a.timer_wait desc;

FORMAT_PICO_TIME与PS_THREAD_ID函数是8.0.16及以后才提供的 performance_schema库函数
在8.0.16之前请用下面的语句查询长事务

select c.thd_id,c.conn_id,concat(PROCESSLIST_USER,'@',PROCESSLIST_HOST) User,
sys.format_time(a.timer_wait) AS trx_duration,c.command,
 c.current_statement as latest_statement
 from performance_schema.events_transactions_current as a
inner join information_schema.innodb_trx as b on a.thread_id = sys.ps_thread_id(b.trx_mysql_thread_id)
inner join sys.processlist as c on  c.thd_id=a.thread_id
INNER JOIN performance_schema.threads d  on a.thread_id = d.thread_id
order by  a.timer_wait desc;

查询长事务语句

-- 查询长事务语句
SELECT DATE_SUB(now(), INTERVAL (
         SELECT variable_value 
           FROM performance_schema.global_status 
           WHERE variable_name='UPTIME')-TIMER_START*10e-13 second) `start_time`,
       SQL_TEXT 
  FROM performance_schema.events_statements_history  
 WHERE nesting_event_id=(
               SELECT EVENT_ID 
                 FROM performance_schema.events_transactions_current t   
                 LEFT JOIN sys.processlist p ON p.thd_id=t.thread_id  
                 WHERE conn_id= 23) 
 ORDER BY event_id;

thread_id: 为 performance_schema 内部id

information_schema.innodb_trx.trx_mysql_thread_id、
sys.processlist.conn_id、
performance_schema.threads.processlist_id、
show full processlist 返回的id
INFORMATION_SCHEMA.PROCESSLIST.id
select connection_id()
都为同一个id.可以直接关联
PS_THREAD_ID(上面的id) 可以得到 thread_id