查看MYSQL长事务对应的SQL语句

郑全

我们想看一些trx_state=running，now-trx_started > 10秒的会话的SQL语句， 可以使用以下语句：
select * from information_schema.innodb_trx ;
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'
      and ps.id=前面列出的trx_mysql_thread_id
     ORDER BY
       esh.EVENT_ID;