Detailed analysis of mysql MDL metadata lock

Preface:

When you execute a SQL statement in MySQL, the statement is not completed within the time you expected. At this time, we usually log in to the MySQL database to check if there is any problem. A command usually used is show processlist to see which sessions there are and what these sessions are doing. When you see waiting for table metadata lock, you are encountering an MDL metadata lock. This article will introduce the generation and troubleshooting process of MDL lock.

1. What is MDL lock?

MDL stands for metadata lock. The main function of the MDL lock is to maintain the data consistency of the table metadata. When there are active transactions (explicit or implicit) on the table, the metadata cannot be written. Therefore, MDL locks were introduced starting from MySQL version 5.5 to protect the metadata information of the table and to solve or ensure the consistency between DDL operations and DML operations.

The introduction of MDL mainly solves two problems. One is the transaction isolation problem. For example, under the repeatable isolation level, if session A modifies the table structure during two queries, the two query results will be inconsistent and cannot meet the repeatable read requirement. The other is the data replication problem. For example, if session A executes multiple update statements and another session B changes the table structure and submits it first, the slave will first redo the alter and then redo the update, which will cause replication errors.

The metadata lock is a server-layer lock, a table-level lock. An MDL lock is applied for each DML or DDL statement executed. DML operations require an MDL read lock, and DDL operations require an MDL write lock (the MDL locking process is automatically controlled by the system and cannot be directly intervened. Read-read is shared, read-write is mutually exclusive, and write-write is mutually exclusive). The operation of applying for an MDL lock will form a queue, and the write lock acquisition priority in the queue is higher than the read lock. Once a write lock wait occurs, not only the current operation will be blocked, but also all subsequent operations on the table will be blocked. Once a transaction applies for an MDL lock, the lock will not be released until the transaction is completed. (There is a special case here. If the transaction contains DDL operations, MySQL will implicitly commit before the DDL operation statement is executed to ensure that the DDL statement operation exists as a separate transaction and also ensure the release of the metadata exclusive lock).

Note: Both InnoDB engine tables that support transactions and MyISAM engine tables that do not support transactions will experience the Metadata Lock Wait phenomenon. Once the Metadata Lock Wait phenomenon occurs, all subsequent access to the table will be blocked on this wait, resulting in connection accumulation and business impact.

2. Simulate and find MDL locks

MDL locks usually occur when a DDL operation is suspended because there are uncommitted transactions performing DML operations on the table. However, there are so many MySQL sessions that it is not known which session's operations were not submitted in time and affected the DDL. Usually when we troubleshoot this type of problem, we often need to query the currently executing transaction from the information_schema.innodb_trx table. However, when the SQL has been executed and not committed, the SQL cannot be seen in this table.

In MySQL 5.7, a new metadata_locks table was added to the performance_schema library to record MDL-related information. First, you need to enable MDL lock logging. Execute the following SQL to enable it:

UPDATE performance_schema.setup_instruments
SET ENABLED = 'YES', TIMED = 'YES'
WHERE NAME = 'wait/lock/metadata/sql/mdl';

The following shows the process of simulating and finding MDL locks:

# Session 1 Executes DML operations in the transaction mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> insert into student_tb (stu_id,stu_name) values (1009,'xin');
Query OK, 1 row affected (0.00 sec)

mysql> select * from student_tb;
+--------------+--------+----------+---------------------+---------------------+
| increment_id | stu_id | stu_name | create_time | update_time |
+--------------+--------+----------+---------------------+---------------------+
| 1 | 1001 | from1 | 2019-11-28 16:36:14 | 2019-11-28 16:36:14 |
| 2 | 1002 | dfsfd | 2019-11-28 16:36:14 | 2019-11-28 16:36:14 |
| 3 | 1003 | fdgfg | 2019-11-28 16:36:14 | 2019-11-28 16:36:14 |
| 4 | 1004 | sdfsdf | 2019-11-28 16:36:14 | 2019-11-28 16:36:14 |
| 5 | 1005 | dsfsdg | 2019-11-28 16:36:14 | 2019-11-28 16:36:14 |
| 6 | 1006 | fgd | 2019-11-28 16:36:14 | 2019-11-28 16:36:14 |
| 7 | 1007 | fgds | 2019-11-28 16:36:14 | 2019-11-28 16:36:14 |
| 8 | 1008 | dgfsa | 2019-11-28 16:36:14 | 2019-11-28 16:36:14 |
| 9 | 1009 | xin | 2019-11-28 17:05:29 | 2019-11-28 17:05:29 |
+--------------+--------+----------+---------------------+---------------------+

# Session 2 Execute DDL operation to add fields to the table and find that DDL hangs mysql> alter table student_tb add stu_age int after stu_name;

# Session 3 Query all sessions and find that MDL lock occurs mysql> show processlist;
+----+------+-----------+--------+---------+------+---------------------------------+-------------------------------------------------------+
| Id | User | Host | db | Command | Time | State | Info |
+----+------+-----------+--------+---------+------+---------------------------------+-------------------------------------------------------+
| 31 | root | localhost | testdb | Sleep | 125 | | NULL |
| 32 | root | localhost | testdb | Query | 7 | Waiting for table metadata lock | alter table student_tb add stu_age int after stu_name |
| 33 | root | localhost | testdb | Query | 0 | starting | show processlist |
+----+------+-----------+--------+---------+------+---------------------------------+-------------------------------------------------------+

# Session 3 Check the metadata_locks table records and find that the student_tb table has an MDL lock conflict. mysql> select * from performance_schema.metadata_locks;
+-------------+--------------------+----------------+-----------------------+---------------------+---------------+------------+--------+-----------------+----------------+
| OBJECT_TYPE | OBJECT_SCHEMA | OBJECT_NAME | OBJECT_INSTANCE_BEGIN | LOCK_TYPE | LOCK_DURATION | LOCK_STATUS | SOURCE | OWNER_THREAD_ID | OWNER_EVENT_ID |
+-------------+--------------------+----------------+-----------------------+---------------------+---------------+------------+--------+-----------------+----------------+
| TABLE | testdb | student_tb | 94189250717664 | SHARED_WRITE | TRANSACTION | GRANTED | | 56 | 34 |
| GLOBAL | NULL | NULL | 139764477045472 | INTENTION_EXCLUSIVE | STATEMENT | GRANTED | | 57 | 18 |
| SCHEMA | testdb | NULL | 139764477697808 | INTENTION_EXCLUSIVE | TRANSACTION | GRANTED | | 57 | 18 |
| TABLE | testdb | student_tb | 139764477697904 | SHARED_UPGRADABLE | TRANSACTION | GRANTED | | 57 | 18 |
| TABLE | testdb | student_tb | 139764477697696 | EXCLUSIVE | TRANSACTION | PENDING | | 57 | 18 |
| TABLE | performance_schema | metadata_locks | 139764544135120 | SHARED_READ | TRANSACTION | GRANTED | | 58 | 20 |
+-------------+--------------------+----------------+-----------------------+---------------------+---------------+------------+--------+-----------------+----------------+

# Session 3 combines other system tables to find the session ID
mysql> select m.*,t.PROCESSLIST_ID from performance_schema.metadata_locks m left join performance_schema.threads t on m.owner_thread_id=t.thread_id;
+-------------+--------------------+----------------+-----------------------+---------------------+---------------+------------+--------+-----------------+----------------+----------------+
| OBJECT_TYPE | OBJECT_SCHEMA | OBJECT_NAME | OBJECT_INSTANCE_BEGIN | LOCK_TYPE | LOCK_DURATION | LOCK_STATUS | SOURCE | OWNER_THREAD_ID | OWNER_EVENT_ID | PROCESSLIST_ID |
+-------------+--------------------+----------------+-----------------------+---------------------+---------------+------------+--------+-----------------+----------------+----------------+
| TABLE | testdb | student_tb | 94189250717664 | SHARED_WRITE | TRANSACTION | GRANTED | | 56 | 34 | 31 |
| GLOBAL | NULL | NULL | 139764477045472 | INTENTION_EXCLUSIVE | STATEMENT | GRANTED | | 57 | 18 | 32 |
| SCHEMA | testdb | NULL | 139764477697808 | INTENTION_EXCLUSIVE | TRANSACTION | GRANTED | | 57 | 18 | 32 |
| TABLE | testdb | student_tb | 139764477697904 | SHARED_UPGRADABLE | TRANSACTION | GRANTED | | 57 | 18 | 32 |
| TABLE | testdb | student_tb | 139764477697696 | EXCLUSIVE | TRANSACTION | PENDING | | 57 | 18 | 32 |
| TABLE | performance_schema | metadata_locks | 139764544135120 | SHARED_READ | TRANSACTION | GRANTED | | 58 | 22 | 33 |
| TABLE | performance_schema | threads | 139764549217280 | SHARED_READ | TRANSACTION | GRANTED | | 58 | 22 | 33 |
+-------------+--------------------+----------------+-----------------------+---------------------+---------------+------------+--------+-----------------+----------------+----------------+

# Result interpretation: From the above results, it is obvious that session 31 holds the SHARED_WRITE lock of the student_tb table.
# You need to wait for it to be submitted or manually kill the session to release the MDL lock.

3. How to optimize and avoid MDL locks

Once an MDL lock occurs, it will have a huge impact on the business, because all subsequent access to the table will be blocked, causing a backlog of connections. We should try to avoid the occurrence of MDL locks in our daily life. Here are some optimization suggestions for your reference:

Enable the metadata_locks table to record MDL locks.
Set the parameter lock_wait_timeout to a smaller value so that the blocked end stops actively.
Use transactions in a standardized manner, submit transactions in a timely manner, and avoid using large transactions.
Enhance monitoring and alarm to detect MDL locks in a timely manner.
DDL operations and backup operations are performed during business off-peak hours.
Use fewer tools to open transactions for querying, and close graphical tools in a timely manner.

Summarize:

This article mainly explains the MDL lock in three aspects. First, it introduces the causes and functions of the MDL lock. Then we simulate the MDL lock and provide the search and solution methods. Finally, we give some suggestions to avoid the MDL lock. In fact, MDL locks are often encountered during DB operation and maintenance. They are not a scourge, but are only used to protect database objects and ensure data consistency. I hope that after reading this article, you will have a clearer understanding of MDL lock.

The above is a detailed analysis of the details of MySQL MDL metadata lock. For more information about MySQL MDL metadata lock, please pay attention to other related articles on 123WORDPRESS.COM!

You may also be interested in:

How to check where the metadata lock is blocked in MySQL
MYSQL METADATA LOCK (MDL LOCK) MDL lock problem analysis

<<: Detailed explanation of Vue.js directive custom instructions

>>: Nginx external network access intranet site configuration operation

Solution to find all child rows for a given parent row in MySQL

Detailed analysis of mysql MDL metadata lock

Solution to find all child rows for a given parent row in MySQL

How to achieve 3D dynamic text effect with three.js

Several methods of deploying multiple front-end projects with nginx

Understanding the MySQL query optimization process

Summary of B-tree index knowledge points in MySQL optimization

How to place large images in a small space on a web page

How to modify the previous command when an input error occurs in the MySQL command prompt

Detailed explanation of the process of Zabbix active, passive and web monitoring in distributed monitoring system

How to simplify Redux with Redux Toolkit

Detailed steps to build an independent mail server on Centos7.9

Recommend

You really need to understand the use of CSS variables var()

Detailed explanation of MySQL and Spring's autocommit

Mysql accidental deletion of data solution and kill statement principle

The difference between Div and table in HTML (discussed in detail in all aspects)

Say goodbye to hard coding and let your front-end table automatically calculate the instance code

Detailed explanation of the usage of 5 different values of CSS position

Docker image import, export, backup and migration operations

How to enter directory/folder in Linux without using CD command

Detailed explanation of the significance of standard commit msg in JavaScript development

Pure JavaScript to implement the number guessing game

Solution to the problem of Failed when configuring IDEA to connect to MYSQL database

3 solutions to make your website support old versions of IE6, 7, 8, and 9 browsers

Samba server configuration under Centos7 (actual combat)

Connector configuration in Tomcat

MySQL learning notes: data engine