重庆思庄Oracle、Redhat认证学习论坛

标题: 查看MYSQL长事务对应的SQL语句 [打印本页]

作者: 郑全 时间: 2024-4-16 08:44
标题: 查看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;

欢迎光临重庆思庄Oracle、Redhat认证学习论坛 (http://bbs.cqsztech.com/)