How to Identify the Transaction Holding the \'Waiting for Table Metadata Lock\' in MySQL?

Locating the Transaction Causing "Waiting for Table Metadata Lock" State

In MySQL, encountering a "Waiting for table metadata lock" message during DDL operations indicates a lock held by another transaction. To determine the culprit transaction, consider the following methods:

MySQL Version < 5.7.3

• SHOW ENGINE INNODB STATUS G: This command displays the "TRANSACTIONS" section, listing open transactions.

INFORMATION_SCHEMA Tables

• SELECT * FROM INNODB_LOCK_WAITS: Retrieves information about all lock waits.
• SELECT * FROM INNODB_LOCKS WHERE LOCK_TRX_ID IN (SELECT BLOCKING_TRX_ID FROM INNODB_LOCK_WAITS): Identifies blocking transactions.
• SELECT * FROM INNODB_LOCKS INNER JOIN INNODB_LOCK_WAITS ON (INNODB_LOCKS.LOCK_TRX_ID = INNODB_LOCK_WAITS.BLOCKING_TRX_ID): Lists the same information as the previous query.
• SELECT * FROM INNODB_LOCKS WHERE LOCK_TABLE = db_name.table_name: Locates locks on a specific table.
• SELECT TRX_ID, TRX_REQUESTED_LOCK_ID, TRX_MYSQL_THREAD_ID, TRX_QUERY FROM INNODB_TRX WHERE TRX_STATE = 'LOCK WAIT': Lists transactions waiting for locks.

Reference:

• MySQL Troubleshooting: What To Do When Queries Don't Work, Chapter 6 - Page 96

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