SHOW ENGINE INNODB STATUS \G
Look for the Section -
TRANSACTIONS
Or
SELECT * FROM INFORMATION_SCHEMA.INNODB_TRX;
We can use INFORMATION_SCHEMA Tables.
Useful Queries -
To check about all the locks transactions are waiting for -
USE INFORMATION_SCHEMA
SELECT * FROM INNODB_LOCK_WAITS;
A list of blocking transactions
SELECT *
FROM INNODB_LOCKS
WHERE LOCK_TRX_ID IN (SELECT BLOCKING_TRX_ID FROM INNODB_LOCK_WAITS);
OR
SELECT INNODB_LOCKS.*
FROM INNODB_LOCKS
JOIN INNODB_LOCK_WAITS
ON (INNODB_LOCKS.LOCK_TRX_ID = INNODB_LOCK_WAITS.BLOCKING_TRX_ID);
A List of locks on perticular table:
SELECT * FROM INNODB_LOCKS
WHERE LOCK_TABLE = db_name.table_name;
A list of transactions waiting for locks:
SELECT TRX_ID, TRX_REQUESTED_LOCK_ID, TRX_MYSQL_THREAD_ID, TRX_QUERY
FROM INNODB_TRX
WHERE TRX_STATE = 'LOCK WAIT';
SQL
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 *
FROM INFORMATION_SCHEMA.INNODB_TRX t
JOIN INFORMATION_SCHEMA.PROCESSLIST p ON t.trx_mysql_thread_id = p.id;
SELECT *
FROM INFORMATION_SCHEMA.INNODB_TRX t
JOIN INFORMATION_SCHEMA.PROCESSLIST p ON t.trx_mysql_thread_id = p.id
WHERE trx_weight > 0;
SELECT trx_id,
trx_state,
trx_started,
trx_weight,
trx_mysql_thread_id,
trx_query,
HOST,
command,
TIME,
state,
info
FROM INFORMATION_SCHEMA.INNODB_TRX t
JOIN INFORMATION_SCHEMA.PROCESSLIST p ON t.trx_mysql_thread_id = p.id
WHERE trx_weight > 0;
Reference