How do I find which transaction is causing a "Waiting for table metadata lock" state?
MysqlMysql Problem Overview
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.
Mysql Solutions
Solution 1 - Mysql
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.
Solution 2 - Mysql
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 https://stackoverflow.com/questions/5462249/clear-transaction-deadlock
you can try killing the transaction thread directly, here with
KILL 5208136;
worked for me.
Solution 3 - Mysql
mysql 5.7 exposes metadata lock information through the performance_schema.metadata_locks
table.
Documentation here
Solution 4 - Mysql
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.
Solution 5 - Mysql
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. |
+---------+------+-----------------------------------------------------------------------------------------------+
Solution 6 - Mysql
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.