How to Identify the Culprit Transaction Causing \"Waiting for Table Metadata Lock\" in MySQL?

Investigating the Source of "Waiting for Table Metadata Lock"

In MySQL, encountering a "Waiting for table metadata lock" message during DDL queries can be frustrating. This state often indicates that another transaction is holding a lock on the affected table, preventing the current transaction from proceeding.

Identifying the Culprit Transaction

To pinpoint which transaction is responsible for this hold, consider using the following approaches:

1. MySQL v5.5.24 and Below

For MySQL versions prior to 5.7.3, execute the following command:

SHOW ENGINE INNODB STATUS \G

Inspect the "TRANSACTIONS" section to identify the offending transaction.

2. Information Schema Tables

All Waiting Locks:

SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCK_WAITS;

Blocking Transactions:

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

Locks on a Specific Table:

SELECT *
FROM INFORMATION_SCHEMA.INNODB_LOCKS
WHERE LOCK_TABLE = db_name.table_name;

Transactions Waiting for Locks:

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

Additional Reference:

For a comprehensive guide on troubleshooting this issue, refer to "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 Culprit Transaction Causing \"Waiting for Table Metadata Lock\" in MySQL?. For more information, please follow other related articles on the PHP Chinese website!