详解mysql数据库事务相关命令总结，值得收藏

100人浏览 2024-08-13 15:32:10

概述

今天主要分享下关于mysql事务的相关sql,抽空做一下总结，整理如下：

查询正在执行的事务(kill事务的线程ID（trx_mysql_thread_id）)

SELECT * FROM information_schema.INNODB_TRX;

查看正在锁的事务

SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCKS;

查看等待锁的事务

SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCK_WAITS;

查看事务等待状况

SELECT
 r.trx_id waiting_trx_id,
 r.trx_mysql_thread_id waiting_thread,
 r.trx_query waiting_query,
 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;

查看更具体的事务等待状况

select
 b.trx_state,
 e.state,
 e.time,
 d.state as block_state,
 d.time as block_time,
 a.requesting_trx_id,
 a.requested_lock_id,
 b.trx_query,
 b.trx_mysql_thread_id,
 a.blocking_trx_id,
 a.blocking_lock_id,
 c.trx_query as block_trx_query,
 c.trx_mysql_thread_id as block_trx_mysql_tread_id
from
 information_schema.innodb_lock_waits a
left join information_schema.innodb_trx b on a.requesting_trx_id = b.trx_id
left join information_schema.innodb_trx c on a.blocking_trx_id = c.trx_id
left join information_schema.processlist d on c.trx_mysql_thread_id = d.id
left join information_schema.processlist e on b.trx_mysql_thread_id = e.id
order by
 a.requesting_trx_id;

查看未关闭的事务

select 
 a.trx_id, 
 a.trx_state, 
 a.trx_started, 
 a.trx_query, 
 b.id, 
 b. user, 
 b. host, 
 b.db, 
 b.command, 
 b.time, 
 b.state, 
 b.info 
 from 
 information_schema.innodb_trx a 
 left join information_schema.processlist b on a.trx_mysql_thread_id = b.id 
 where b.command = 'sleep';

未关闭事务信息

select
t1.trx_id,
t1.trx_started,
t1.trx_mysql_thread_id,
t3.event_id,
t3.end_event_id,
t3.sql_text,
concat('mysql --login-path=3306 -e ''kill ',t1.trx_mysql_thread_id,'''')
from 
 information_schema.innodb_trx t1 
left join `performance_schema`.threads t2
on t1.trx_mysql_thread_id=t2.processlist_id
left join `performance_schema`.events_statements_history t3
on t2.thread_id=t3.thread_id
where
 t1.trx_started < date_sub(now(), interval 1 minute)
and t1.trx_operation_state is null
and t1.trx_query is null
order by event_id desc;

查看某段时间以来未关闭事务

SELECT
 trx_id,
 trx_started,
 trx_mysql_thread_id 
FROM
 information_schema.innodb_trx 
WHERE
 trx_started < date_sub( now( ), INTERVAL 1 MINUTE ) 
 AND trx_operation_state IS NULL 
 AND trx_query IS NULL;