How to Wait for Table Metadata Lock

1. What is a Lock in MySQL
2. What is the Metadata Lock Wait in MySQL
3. When Does the Waiting for table metadata lock Occur in MySQL

Today, we will learn about Waiting for table metadata lock. This article highlights the causes of metadata lock wait and provides the solution for this.

What is a Lock in MySQL

A lock is a mechanism associated with the table to confine unauthorized access to the table’s data. MySQL also permits the table locking to avoid unauthorized updates to the same table during a particular period.

See the following infographic where User A updates the records and sets the SALARY = SALARY + 500. In the meantime, when User A is updating the records, User B accesses the Employee table and retrieves the SALARY, which is not updated yet.

To avoid these kinds of situations, the user locks (READ lock or WRITE lock) the table, which puts other users on wait. The users on the waiting list get a message as Waiting for table metadata lock.

What is the Metadata Lock Wait in MySQL

The metadata lock on the table restricts any change to the structure of a table. In this locking mechanism, a table that is accessed by a transaction in a session and still in the process can’t be accessed in Data definition language statements by any other session until the transaction finishes.

When there is an active transaction, the sessions that request the data write would be placed in the metadata lock wait status.

When Does the Waiting for table metadata lock Occur in MySQL

The Waiting for table metadata lock may occur when we DELETE or CREATE an index and modify the table’s structure.

It can also occur when we perform maintenance operations on tables, DELETE tables, or try to access the WRITE lock on table-level (using this query LOCK TABLE table_name WRITE;).

The Active Transactions Leading to the Metadata Lock Waits

Different types of active transactions can lead to metadata lock waits. Some cases are listed below where the metadata lock wait can occur.

1. Query to a table that has been present for a long period.
2. Failed to COMMIT or ROLLBACK the transaction opened implicitly or explicitly.
3. When we have an unsuccessful query transaction on a table.

How to Identify and Resolve the Waiting for table metadata lock

We can use the following command to get necessary information in a tabular form showing where the Waiting for table metadata lock occurs.

Command:

mysql> show processlist;

Output:

We can use the query below, check the TRANSACTIONS section for further investigation and examine which transaction is placed in the Waiting for table metadata lock.

Command:

SHOW ENGINE INNODB STATUS;

The TRANSACTIONS section has an issue in thread 189, which is idle and left a transaction running; see the following.

The metadata lock should be released by killing this thread as follows.

KILL 189;

Remember, whenever the metadata lock wait happens, it blocks all the subsequent queries (requests) trying to access the table. This situation leads to congestion in the connections and impacts the company.