Waiting for table metadata lock
Today, we will learn 等待表元数据锁定. This article focuses on the causes of metadata lock waits and provides solutions.
What are locks in MySQL?
A lock is a mechanism associated with a table to restrict unauthorized access to the table data. MySQL also allows table locking to prevent unauthorized updates to the same table within a specific time period.
Refer to the following infographic, where 用户 Athe records are updated and set SALARY = SALARY + 500. At the same time, User Awhen the records are updated, the table User Bis accessed Employeeand the ones that have not been updated are retrieved SALARY.
To avoid this, the user locks ( READLOCK or WRITELOCK) the table, which puts other users in a waiting state. Users in the waiting list receive 等待表元数据锁定the message.
What is Metadata Lock Wait in MySQL?
A metadata lock on a table restricts any changes to the table structure. In this locking mechanism, any other session cannot access the tables accessed by a transaction in the session and still in progress in data definition language statements until the transaction is completed.
When there is an active transaction, sessions requesting data writes will be put into a metadata lock wait state.
When does it appear in MySQLWaiting for table metadata lock
It may happen when we DELETEor index and modify the structure of the table .CREATEWaiting for table metadata lock
This also happens when we perform maintenance operations on a table, DELETEor when we try to access a table level WRITElock (using this query ).LOCK TABLE table_name WRITE;
Active transactions causing metadata lock waits
Different types of active transactions can cause metadata lock waits. Listed below are some of the situations where metadata lock waits can occur.
- Query a table that has existed for a long time.
- Failed to open a transaction,
COMMITeitherROLLBACKimplicitly or explicitly. - When we have an unsuccessful query transaction on a table.
How to Identify and ResolveWaiting for table metadata lock
We can use the following command to get the necessary information in a tabular form showing 等待表元数据锁定where it happened.
Order:
mysql> show processlist;
Output:
We can use the query below to TRANSACTIONSinvestigate further by checking the part and check which transaction is placed in Waiting for table metadata lock.
Order:
SHOW ENGINE INNODB STATUS;
TRANSACTIONSThe problem is partly in thread 189, which is idle and has transactions running; see below.
The metadata lock should be released by killing this thread as shown below.
KILL 189;
Remember, whenever a metadata lock wait occurs, it blocks all subsequent queries (requests) that attempt to access that table. This situation can cause connection congestion and affect your company.
For reprinting, please send an email to 1244347461@qq.com for approval. After obtaining the author's consent, kindly include the source as a link.
Related Articles
If ELSE in MySQL
Publish Date：2025/04/11 Views：85 Category：MySQL
-
In this tutorial, we aim to explore how to use IF ELSE the statement in MySQL. One of the key roles of a data analyst is to gather insights from the data and produce meaningful results. It can be done with the help of several data filtering
DATETIME vs. TIMESTAMP in MySQL
Publish Date：2025/04/11 Views：117 Category：MySQL
-
DATETIME and TIMESTAMP are two different data types that can be used to store values that must contain both a date and a time portion. In this article, we will understand how it is stored in the database and the memory required for ea
Execute multiple joins in one query in MYSQL
Publish Date：2025/04/11 Views：94 Category：MySQL
-
Have you ever wondered how to include multiple joins in one query in MySQL? You have come to the right place. Remember that joins allow us to access information from other tables. This information is included separately to avoid redundancy.
Joining 3 tables in MySQL
Publish Date：2025/04/11 Views：187 Category：MySQL
-
In this tutorial, we will learn how to join three tables in MySQL. Businesses and organizations may have to visualize three tables simultaneously based on certain matching columns common to all three tables. This operation is allowed in MyS
Use of UPDATE JOIN in MySQL
Publish Date：2025/04/11 Views：85 Category：MySQL
-
This tutorial will explain how to use the statement in MySQL database UPDATE JOIN . We generally use joins to iterate over the rows in a particular table which may or may not have similar rows in other tables. We can UPDATE use JOIN the cla
How to use the Row_Number() function in MySQL
Publish Date：2025/04/11 Views：142 Category：MySQL
-
In this tutorial, we will explain how to use the VALUES function in MySQL ROW_NUMBER() . This is a sorting method that assigns consecutive numbers within a partition starting from 1. It is important to note that no two rows within a partiti
Multiple primary keys in MySQL
Publish Date：2025/04/11 Views：66 Category：MySQL
-
In this tutorial, our goal is to explore the concept of multiple primary keys for a table in MySQL. Many times, businesses and organizations have to assign certain columns as primary keys. This primary key has multiple uses and reasons to b
Displaying foreign keys in MySQL
Publish Date：2025/04/11 Views：55 Category：MySQL
-
In this tutorial, we aim to explore how to display foreign keys for tables and columns in MySQL. The type of key that references a primary key, also known as the primary key of another table, is called a foreign key. Understanding the forei
Select first N rows in MySQL
Publish Date：2025/04/11 Views：85 Category：MySQL
-
Sometimes, you have to select first N rows of MySQL database according to your project requirements. n The value of varies according to the requirement; it can be TOP 1 row or TOP 30 rows. We will learn how to select top N rows using the cl
