How do I find which transaction is causing a "Waiting for table metadata lock" state?
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.
If you cannot find the process locking the table (cause it is alreay dead), it may be a thread still cleaning up like this
section TRANSACTION of
show engine innodb status;
at the end
---TRANSACTION 1135701157, ACTIVE 6768 sec
MySQL thread id 5208136, OS thread handle 0x7f2982e91700, query id 882213399 xxxIPxxx 82.235.36.49 my_user cleaning up
as mentionned in a comment in Clear transaction deadlock?
you can try killing the transaction thread directly, here with
KILL 5208136;
worked for me.
mysql 5.7 exposes metadata lock information through the performance_schema.metadata_locks
table.
Documentation here