Works for MySql version < 5.7.3
SHOW ENGINE INNODB STATUS \G
Look for the Section –
TRANSACTIONS
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 particular 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';
Reference – MySQL Troubleshooting: What To Do When Queries Don’t Work, Chapter 6 – Page 96.