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

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 5

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 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.

Answer 7

None of these answers totally worked for me on Mysql 5.6. For locking transactions that don't show up in process list, I had to use

SHOW ENGINE INNODB STATUS \G

And look in the Transactions section as others have suggested. Usually the bottom row of transactions should be something not too old like

---TRANSACTION 1746333130055, ACTIVE 20 sec
MySQL thread id 2078245871, OS thread handle 0x7fb1ab3bb700, query id 64608927411 10.0.200.123 app_user
Trx read view will not see trx with id >= 1746333130056, sees < 1746332958368

But sometimes it might look more like

---TRANSACTION 1742251019746, ACTIVE 283392 sec
230145 lock struct(s), heap size 29685288, 1531889 row lock(s), undo log entries 1527774
MySQL thread id 1891102408, OS thread handle 0x7f7d8c132700, query id 59116541146 10.0.200.224 app_user
Trx read view will not see trx with id >= 1742251019747, sees < 1742250438378

^ Note the 283392 sec indicating that this transaction is the culprit of the locked table. The associated process is labeled as the "thread id", which is 1891102408 in this case. To root cause what about that process has caused the lock, I run from the command line of the db server:

mysql -u root -e 'show processlist' | grep "1891102408"

Which indicates which server the query originated from, along with the a unique identifier (the port number), like:

32907376794 app_user    10.0.200.224:46092  production_database Sleep   124144      NULL    0   1

After logging in to the 10.0.200.224 server, one can use netstat -apN | grep 46092 to see which process on the server is responsible for the Mysql process and kill it from its source, debugging what went wrong to whatever extent desired.