mysql 数据库事务 （三）查询及杀死正在运行的 有哪些事务、都锁定哪些资源

 

查询当前数据库 正在运行的 有哪些事务、都锁定哪些资源

1、

sql：

select
	trx_id as `事务ID`,
	trx_state as `事务状态`,
	trx_requested_lock_id as `事务需要等待的资源`,
	trx_wait_started as `事务开始等待时间`,
	trx_tables_in_use as `事务使用表`,
	trx_tables_locked as `事务拥有锁`,
	trx_rows_locked as `事务锁定行`,
	trx_rows_modified as `事务更改行`
from
	information_schema.innodb_trx ;

结果：

杀掉对应事务：

根据这个事务的线程ID（trx_mysql_thread_id）:
可以使用mysql命令杀掉线程：kill 线程id

mysql>kill 887123412;

2、

sql：

select
	lock_id as `锁ID`,
	lock_trx_id as `拥有锁的事务ID`,
	lock_mode as `锁模式    `,
	lock_type as `锁类型`,
	lock_table as `被锁的表`,
	lock_index as `被锁的索引`,
	lock_space as `被锁的表空间号`,
	lock_page as `被锁的页号`,
	lock_rec as `被锁的记录号`,
	lock_data as `被锁的数据`
from
	information_schema.innodb_locks;

结果：

---

以下内容摘自：https://blog.csdn.net/zyz511919766/article/details/49335729

一些查看数据库中事务和锁情况的常用语句

查看事务等待状况：

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;

查看未关闭的事务：
–MySQL 5.6

SELECT
     a.trx_id,
     a.trx_state,
     a.trx_started,
     a.trx_query,
     b.ID,
     b.USER,
     b.DB,
     b.COMMAND,
     b.TIME,
     b.STATE,
     b.INFO,
     c.PROCESSLIST_USER,
     c.PROCESSLIST_HOST,
     c.PROCESSLIST_DB,
     d.SQL_TEXT
FROM
     information_schema.INNODB_TRX a
LEFT JOIN information_schema.PROCESSLIST b ON a.trx_mysql_thread_id = b.id
AND b.COMMAND = 'Sleep'
LEFT JOIN PERFORMANCE_SCHEMA.threads c ON b.id = c.PROCESSLIST_ID
LEFT JOIN PERFORMANCE_SCHEMA.events_statements_current d ON d.THREAD_ID = c.THREAD_ID;

–MySQL 5.5

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
        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;

---

更详细的请参考：https://blog.csdn.net/weixin_34321977/article/details/92516068