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

Bertha 。 2022-08-09 11:56 69阅读 0赞

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

**查看事务等待状况：**

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;