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

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.

Answer 2

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.

Answer 3

mysql 5.7 exposes metadata lock information through the performance_schema.metadata_locks table.

Documentation here

Answer 4

I had a similar issue with Datagrip and none of these solutions worked.

Once I restarted the Datagrip Client it was no longer an issue and I could drop tables again.

Answer 5

For MySQL version >= 5.7.3 the Performance Schema now exposes metadata lock information. https://dev.mysql.com/doc/relnotes/mysql/5.7/en/news-5-7-3.html

Run this query to know who holds your metadata locks

SELECT OBJECT_TYPE,
       OBJECT_SCHEMA,
       OBJECT_NAME,
       LOCK_TYPE,
       LOCK_STATUS,
       THREAD_ID,
       PROCESSLIST_ID,
       PROCESSLIST_INFO
FROM performance_schema.metadata_locks
INNER JOIN performance_schema.threads ON THREAD_ID = OWNER_THREAD_ID
WHERE PROCESSLIST_ID <> CONNECTION_ID();

---

When I tried to run the query in the accepted answer, I received this:

Empty set, 1 warning (0.001 sec)

Checking that 1 warning, I found that INNODB_LOCK_WAITS is deprecated.

MySQL [ebdb]> SHOW WARNINGS;
+---------+------+-----------------------------------------------------------------------------------------------+
| Level   | Code | Message                                                                                       |
+---------+------+-----------------------------------------------------------------------------------------------+
| Warning | 1681 | 'INFORMATION_SCHEMA.INNODB_LOCK_WAITS' is deprecated and will be removed in a future release. |
+---------+------+-----------------------------------------------------------------------------------------------+

Answer 6

I just had this problem and none of the queries above showed any lock. But I had an alter locked with this " Waiting for table metadata lock " message. I found there was a long running query (it was running for more than two hours). I killed that query and the alter unlocked immediately.