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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

mysql 5 7 exposes metadata lock information through the  performance schema metadata locks table   Documentation here

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

Examples related to mysql