Deep understanding of MDL metadata locks

Foreword:

When you execute a SQL in MySQL, the statement is not completed within the time you expect. We usually log in to the MySQL database to see if something is wrong. One of the commands we usually use is show processlist to see which session s are there and what they are doing.When you see waiting for table metadata lock, you encounter an MDL metadata lock.This article describes the generation and troubleshooting of MDL locks.

1. What is an MDL lock

MDLs are all called metadata locks, which are metadata locks.The main purpose of an MDL lock is to maintain data consistency of table metadata, which cannot be written to when there are active transactions (explicit or implicit) on the table.Since MySQL version 5.5, MDL locks have been introduced to protect the metadata information of tables for resolving or ensuring consistency between DDL and DML operations.

For the introduction of MDL, it mainly solves two problems, one is transaction isolation. For example, in the repeatable isolation level, during the two queries of Session A, Session B modifies the table structure. The results of the two queries will be inconsistent and can not meet the requirements of repeatable reading. The other is the problem of data replication, such as during the execution of several update statements by Session A, and the other isSession B changes the table structure and commits it first, which results in a copy error when slave redoes alter and update.

Metadata locks are server-level locks, table-level locks, MDL locks are applied for each DML, DDL statement executed. DML operations require MDL read locks, DDL operations require MDL write locks (MDL locking process is a system automatic control, can not be directly interfered with, read-read sharing, read-write mutually exclusive, write-write mutually exclusive). Applications for MDL locks form a queue where write locks take precedence over read locks..Once a write lock wait occurs, not only will the current operation be blocked, but all subsequent operations on the table also be blocked.Once a transaction has applied for an MDL lock, it will not release the lock until the transaction has finished executing.(There is a special case where if a transaction contains a DDL operation, mysql implicitly commit s the DDL operation statement before it executes to ensure that the DDL statement operation exists as a separate transaction, as well as the release of metadata exclusive locks).

Note: Both InnoDB engine tables that support transactions and MyISAM engine tables that do not support transactions will have Metadata Lock Wait wait phenomena.Once a Metadata Lock Wait wait occurs, subsequent access to the table will block the wait, causing connections to pile up and business to suffer.

2. Simulating and finding MDL locks

MDL locks typically occur when a DDL operation is suspended because there are uncommitted transactions that DML the table.MySQL has so many sessions that it doesn't know which session's operation didn't submit in time to affect the DDL.Often, when we troubleshoot such problems, we need to query the information_schema.innodb_trx table for the currently executing transaction, but when the SQL has already been executed and there is no commit, the SQL is not visible in this table.

In MySQL 5.7, a new metadata_locks table was added to the performance_schema library to specifically record information about MDL.First, open the MDL lock record by performing the following SQL opening:

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:

# Perform DML operations in Session 1 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 performs DDL operations on this table plus fields and finds DDL pending
mysql> alter table student_tb add stu_age int after stu_name;

# Session 3 Query All Sessions Discover MDL Locks
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 looks at the metadata_locks table record and finds 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 federates with 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 on the student_tb table.
# You need to wait for it to commit or kill the session manually to unlock the MDL.

3. How to optimize and avoid MDL locks

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

Open the metadata_locks table record MDL lock.
Set the parameter lock_wait_timeout to a smaller value so that the blocked end stops actively.
Standardize the use of transactions, timely submission of transactions, avoid the use of large transactions.
Enhance monitoring alerts to detect MDL locks in time.
DDL and backup operations are performed during peak business hours.
Use fewer tools to open transactions for query, and graphical tools should be closed in time.

Summary:

This article will mainly discuss MDL locks in three aspects. First, it describes the causes and functions of MDL locks, then we simulate MDL locks, give the ways to find and solve them, and finally give some suggestions to avoid MDL locks.In fact, MDL locks are often encountered in the process of DB operation and maintenance. They are not a flood beast, but to protect database objects and ensure data consistency.I hope you will have a clearer understanding of MDL locks after reading this article.

Reference resources:

Posted by MytHunter on Sun, 08 Dec 2019 15:15:07 -0800

Programmer Group

Deep understanding of MDL metadata locks

1. What is an MDL lock

2. Simulating and finding MDL locks

3. How to optimize and avoid MDL locks

Hot Keywords