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.
The Waiting for table metadata lock may occur when we DELETE or CREATE an index and modify the table's structure. It can also occur when we perform maintenance operations on tables, DELETE tables, or try to access the WRITE lock on table-level (using this query LOCK TABLE table_name WRITE; ).
A metadata lock on a table prevents changes to the table's structure. This locking approach has the implication that a table that is being used by a transaction within one session cannot be used in DDL statements by other sessions until the transaction ends.
The correct way to use LOCK TABLES and UNLOCK TABLES with transactional tables, such as InnoDB tables, is to begin a transaction with SET autocommit = 0 (not START TRANSACTION ) followed by LOCK TABLES , and to not call UNLOCK TABLES until you commit the transaction explicitly.
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.
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.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With