How do I find which transaction is causing a "Waiting for table metadata lock" state?

Question

I am trying to perform some DDL on a table and SHOW PROCESSLIST results in a " Waiting for table metadata lock " message.

How can I find out which transaction is not yet closed?

I'm using MySQL v5.5.24.

Answer 1

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.