如何查看mysql的在执行的事务

监控数据，主库有大事物运行，可通过如下命令检查下是哪些相关语句导致。

事务未提交sql

SELECT
        it.trx_id AS trx_id,
        it.trx_state AS trx_state,
        it.trx_started AS trx_started,
        it.trx_mysql_thread_id AS trx_mysql_thread_id,
        CURRENT_TIMESTAMP - it.trx_started AS RUN_TIME,
        pl.USER AS USER,
        pl.HOST AS HOST,
        pl.db AS db,
        pl.time AS trx_run_time,
        pl.INFO AS INFO
FROM
        information_schema.INNODB_TRX it,
        information_schema.PROCESSLIST pl
WHERE
        pl.id = it.trx_mysql_thread_id
ORDER BY
        RUN_TIME DESC
        LIMIT 10;

哪个事务阻塞了另一个事务:

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 \G

如何查看mysql的在执行的事务

微信号：腾讯云折扣返现

技术讨论QQ群： 863102326

作者: catherine

搜索

标签