Home > Database > Mysql Tutorial > body text

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

Patricia Arquette
Release: 2024-10-31 12:54:30
Original
775 people have browsed it

How to Identify and Resolve

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

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>
Copy after login

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>
Copy after login

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>
Copy after login

To check locks on a specific table:

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

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>
Copy after login

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!

source:php.cn
Statement of this Website
The content of this article is voluntarily contributed by netizens, and the copyright belongs to the original author. This site does not assume corresponding legal responsibility. If you find any content suspected of plagiarism or infringement, please contact admin@php.cn
Latest Articles by Author
Popular Tutorials
More>
Latest Downloads
More>
Web Effects
Website Source Code
Website Materials
Front End Template