How to Identify Transactions Causing \"Waiting for Table Metadata Lock\" in MySQL?

Identifying Transactions Causing "Waiting for Table Metadata Lock" State

When attempting to execute DDL operations on a table, it's possible to encounter the "Waiting for table metadata lock" message in SHOW PROCESSLIST. This indicates that another transaction is still referencing the table, preventing its metadata from being altered.

Identifying the Transaction

MySQL versions prior to 5.7.3:

• Run the command: SHOW ENGINE INNODB STATUS G
• Locate the "TRANSACTIONS" section.

For MySQL versions 5.7.3 and above:

• Utilize INFORMATION_SCHEMA tables:

To identify all waiting lock transactions:

USE INFORMATION_SCHEMA;
SELECT * FROM INNODB_LOCK_WAITS;

To identify blocking transactions:

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

To identify locks on a specific table:

SELECT * FROM INNODB_LOCKS 
WHERE LOCK_TABLE = db_name.table_name;

To identify 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';

Example Output:

The INNODB_LOCK_WAITS query might provide output similar to:

...
REQUESTING TRXs:
trx id 10,mysql thread id 1161142,query SELECT ...
trx id 16,mysql thread id 1161141,query SELECT ...
...

This indicates that transaction IDs 10 and 16 are waiting for the lock. Further analysis can be conducted using the other queries provided to identify the blocking transaction and the specific table being locked.

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