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

Works for MySql version < 5.7.3

SHOW ENGINE INNODB STATUS \G

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;

Behold :) below is your image, it's just white triangle with transparency...

A list of blocking transactions:

SELECT *
FROM INNODB_LOCKS
WHERE LOCK_TRX_ID IN (SELECT BLOCKING_TRX_ID FROM INNODB_LOCK_WAITS);

[dragging the image around in your browser for visibility,

OR

SELECT INNODB_LOCKS.*
FROM INNODB_LOCKS
JOIN INNODB_LOCK_WAITS
ON (INNODB_LOCKS.LOCK_TRX_ID = INNODB_LOCK_WAITS.BLOCKING_TRX_ID);

the gray background and the border is not part of the image]

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.

worked for me.

Documentation here

It's probably better to use another mechanism for this.

The modern solution, with DateTime:

$dt = DateTime::createFromFormat("Y-m-d", $date);
return $dt !== false && !array_sum($dt::getLastErrors());

This validates the input too: $dt !== false ensures that the date can be parsed with the specified format and the array_sum trick is a terse way of ensuring that PHP did not do "month shifting" (e.g. consider that January 32 is February 1). See DateTime::getLastErrors() for more information.

This validates the input too: $dt !== false ensures that the date can be parsed with the specified format and the array_sum trick is a terse way of ensuring that PHP did not do "month shifting" (e.g. consider that January 32 is February 1). See DateTime::getLastErrors() for more information.

Old-school solution with explode and checkdate:

list($y, $m, $d) = array_pad(explode('-', $date, 3), 3, 0);
return ctype_digit("$y$m$d") && checkdate($m, $d, $y);

Old-school solution with explode and checkdate:

list($y, $m, $d) = array_pad(explode('-', $date, 3), 3, 0);
return ctype_digit("$y$m$d") && checkdate($m, $d, $y);

This validates that the input is a valid date as well. You can do that with a regex of course, but it's going to be more fuss -- and February 29 cannot be validated with a regex at all.

This validates that the input is a valid date as well. You can do that with a regex of course, but it's going to be more fuss -- and February 29 cannot be validated with a regex at all.

The drawback of this approach is that you have to be very careful to reject all possible "bad" inputs while not emitting a notice under any circumstances. Here's how:

The drawback of this approach is that you have to be very careful to reject all possible "bad" inputs while not emitting a notice under any circumstances. Here's how:

• explode is limited to return 3 tokens (so that if the input is "1-2-3-4", $d will become "3-4")
• explode is limited to return 3 tokens (so that if the input is "1-2-3-4", $d will become "3-4")
• ctype_digit is used to make sure that the input does not contain any non-numeric characters (apart from the dashes)
• ctype_digit is used to make sure that the input does not contain any non-numeric characters (apart from the dashes)
• array_pad is used (with a default value that will cause checkdate to fail) to make sure that enough elements are returned so that if the input is "1-2" list() will not emit a notice

in this case you'll get an object which is muck easier to use than just strings.

I just had this problem and none of the queries above showed any lock.

You can use a preg_match with a checkdate php function

$date  = "2012-10-05";
$split = array();
if (preg_match ("/^([0-9]{4})-([0-9]{2})-([0-9]{2})$/", $date, $split))
{
return checkdate($split[2], $split[3], $split[1]);
}


return false;

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

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.