MySQL-查找长事务

例如：
查找大于60秒的长事务并显示持续时间(idle_time)：

select t.*,to_seconds(now())-to_seconds(t.trx_started) idle_time from information_schema.innodb_trx t where TIME_TO_SEC(timediff(now(),trx_started)) > 60\G

执行效果：
*************************** 1. row ***************************
                    trx_id: 421214897953896
                 trx_state: RUNNING
               trx_started: 2022-05-29 10:35:22
     trx_requested_lock_id: NULL
          trx_wait_started: NULL
                trx_weight: 0
       trx_mysql_thread_id: 1229
                 trx_query: NULL
       trx_operation_state: NULL
         trx_tables_in_use: 0
         trx_tables_locked: 0
          trx_lock_structs: 0
     trx_lock_memory_bytes: 1136
           trx_rows_locked: 0
         trx_rows_modified: 0
   trx_concurrency_tickets: 0
       trx_isolation_level: REPEATABLE READ
         trx_unique_checks: 1
    trx_foreign_key_checks: 1
trx_last_foreign_key_error: NULL
 trx_adaptive_hash_latched: 0
 trx_adaptive_hash_timeout: 0
          trx_is_read_only: 0
trx_autocommit_non_locking: 0
       trx_schedule_weight: NULL
                 idle_time: 1017

如何通过trx_id找到执行的事务？
information_schema.innodb_trx表中会显示trx_mysql_thread_id: 7416
也就是processlist_id,可以在performance_threads表中进行查询

开发同学可以通过 SET MAX_EXECUTION_TIME 命令，来控制每个语句执行的最长时间，避免单个语句意外执行太长时间

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

select t.*,to_seconds(now())-to_seconds(t.trx_started) idle_time from INFORMATION_SCHEMA.INNODB_TRX t \G
效果同上

查询未提交事务当前执行的SQL及事务持续时间:

select 
  now() cur_time,
  (UNIX_TIMESTAMP(now()) - UNIX_TIMESTAMP(isi.trx_started)) diff_sec,
  isp.id proc_id,
  isp.user,
  isp.host,
  isp.db,
  psec.SQL_TEXT,
  pst.thread_id thr_id
from 
  information_schema.innodb_trx isi
  join information_schema.PROCESSLIST isp on isi.TRX_MYSQL_THREAD_ID=isp.id and isp.command = 'Sleep'
  join performance_schema.threads pst ON isp.id = pst.PROCESSLIST_ID
  join performance_schema.events_statements_current psec ON psec.THREAD_ID = pst.THREAD_ID;
效果：
+---------------------+----------+------+------+-----------+------+------------------+
| now()               | diff_sec | id   | user | host      | db   | SQL_TEXT         |
+---------------------+----------+------+------+-----------+------+------------------+
| 2022-05-29 10:53:42 |     1100 | 1229 | root | localhost | haha | select * From t1 |
+---------------------+----------+------+------+-----------+------+------------------+

查询未提交事务执行过的所有历史SQL及事务持续时间:

SELECT
  ps.id 'PROC_ID',
  ps.USER,
  ps.HOST,
  ps.DB,
  #esh.EVENT_ID,
  trx.trx_started,
  (UNIX_TIMESTAMP(now()) - UNIX_TIMESTAMP(trx.trx_started)) 'diff_time',
  esh.event_name 'EVENT NAME',
  esh.sql_text 'SQL',
  ps.time 'state_time',
  th.thread_id 'THR_ID'
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
  ps.id,
  esh.EVENT_ID;
 /*效果：相比上边效果更详细*/