一些查看数据库中事务和锁情况的常用语句
查询 正在执行的事务：
SELECT * FROM information_schema.INNODB_TRX
根据这个事务的线程ID（trx_mysql_thread_id）:
查看事务等待状况：
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;
● 1
● 2
● 3
● 4
● 5
● 6
● 7
● 8
● 9
● 10
● 11
查看更具体的事务等待状况：
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;
● 1
● 2
● 3
● 4
● 5
● 6
● 7
● 8
● 9
● 10
● 11
● 12
● 13
● 14
● 15
● 16
● 17
● 18
● 19
● 20
● 21
● 22
查看未关闭的事务：
–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;
● 1
● 2
● 3
● 4
● 5
● 6
● 7
● 8
● 9
● 10
● 11
● 12
● 13
● 14
● 15
● 16
● 17
● 18
● 19
● 20
● 21
● 22
–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';
● 1
● 2
● 3
● 4
● 5
● 6
● 7
● 8
● 9
● 10
● 11
● 12
● 13
● 14
● 15
● 16
● 17
● 18
查看某段时间以来未关闭事务：
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;