How to Identify and Resolve \'Waiting for Table Metadata Lock\' in MySQL?

Troubleshooting "Waiting for Table Metadata Lock" State in MySQL

Performing DDL operations on a table can occasionally result in a "Waiting for table metadata lock" message when checking the SHOW PROCESSLIST. This indicates that another transaction has not yet closed and is holding a lock on the table, preventing the modification.

Identifying the Blocking Transaction

To determine which transaction is causing this state, the following methods can be employed:

1. SHOW ENGINE INNODB STATUS

(For MySQL versions

Execute SHOW ENGINE INNODB STATUS G to display detailed information about InnoDB internals, including the TRANSACTIONS section. This will list active transactions and their status.

2. INFORMATION_SCHEMA Tables

The INNODB_LOCK_WAITS table in the INFORMATION_SCHEMA contains information about transactions waiting for locks. To check all such transactions, use:

<code class="sql">SELECT * FROM INNODB_LOCK_WAITS;</code>

To identify blocking transactions, execute:

<code class="sql">SELECT * FROM INNODB_LOCKS WHERE LOCK_TRX_ID IN (SELECT BLOCKING_TRX_ID FROM INNODB_LOCK_WAITS);</code>

Alternatively:

<code class="sql">SELECT INNODB_LOCKS.* 
FROM INNODB_LOCKS
JOIN INNODB_LOCK_WAITS ON (INNODB_LOCKS.LOCK_TRX_ID = INNODB_LOCK_WAITS.BLOCKING_TRX_ID);</code>

To check locks on a specific table:

<code class="sql">SELECT * FROM INNODB_LOCKS WHERE LOCK_TABLE = db_name.table_name;</code>

To list transactions waiting for locks:

<code class="sql">SELECT TRX_ID, TRX_REQUESTED_LOCK_ID, TRX_MYSQL_THREAD_ID, TRX_QUERY
FROM INNODB_TRX
WHERE TRX_STATE = 'LOCK WAIT';</code>

Additional Resources

Refer to the MySQL Troubleshooting Guide, Chapter 6, Page 96, for further insight into resolving this issue.

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