How to Identify Which Transaction is Causing the “Waiting for Table Metadata Lock” State in MySQL?

Identifying Transactions Causing "Waiting for Table Metadata Lock" State

When attempting DDL operations on a table in MySQL version 5.5.24 but encountering "Waiting for table metadata lock" messages, it becomes crucial to determine the transaction responsible for this lock.

Solution

For MySQL versions below 5.7.3:

Execute the following command to retrieve detailed information about the state:

SHOW ENGINE INNODB STATUS \G

Locate the "TRANSACTIONS" section and identify the open transaction that is yet to be closed.

For all MySQL versions:

Retrieve information from the INFORMATION_SCHEMA tables:

All Waiting Locks:

USE INFORMATION_SCHEMA;
SELECT * FROM INNODB_LOCK_WAITS;

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);

Locks on a Specific Table:

SELECT * FROM INNODB_LOCKS WHERE LOCK_TABLE = db_name.table_name;

Waiting Transactions:

SELECT TRX_ID, TRX_REQUESTED_LOCK_ID, TRX_MYSQL_THREAD_ID, TRX_QUERY FROM INNODB_TRX WHERE TRX_STATE = 'LOCK WAIT';

Refer to MySQL Troubleshooting: What To Do When Queries Don't Work, Chapter 6, Page 96 for additional insights.

The above is the detailed content of How to Identify Which Transaction is Causing the “Waiting for Table Metadata Lock” State in MySQL?. For more information, please follow other related articles on the PHP Chinese website!