Detailed explanation of Metadata Lock that you must know when changing the MySQL table structure

Preface

Those who have played with MySQL must be familiar with Waiting for table metadata lock. Generally, it is blocked during the alter operation, which causes us to see the thread status is waiting for metadata lock when we show processlist. This article will give a detailed introduction to Metadata Lock for MySQL table structure changes.

When performing DDL operations online, compared to the possible system load it may bring, what we are most worried about is the blocking problem that MDL may cause.

Once a DDL operation is blocked because the MDL cannot be obtained, all subsequent operations on the table will be blocked. A typical example is as follows: if the blockage lasts a while, we will see a surge in Threads_running and a CPU alarm.

mysql> show processlist;
+----+-----------------+-----------+-----------+---------+------+---------------------------------+------------------------------------+
| Id | User | Host | db | Command | Time | State | Info |
+----+-----------------+-----------+-----------+---------+------+---------------------------------+------------------------------------+
| 4 | event_scheduler | localhost | NULL | Daemon | 122 | Waiting on empty queue | NULL |
| 9 | root | localhost | NULL | Sleep | 57 | | NULL |
| 12 | root | localhost | employees | Query | 40 | Waiting for table metadata lock | alter table slowtech.t1 add c1 int |
| 13 | root | localhost | employees | Query | 35 | Waiting for table metadata lock | select * from slowtech.t1 |
| 14 | root | localhost | employees | Query | 30 | Waiting for table metadata lock | select * from slowtech.t1 |
| 15 | root | localhost | employees | Query | 19 | Waiting for table metadata lock | select * from slowtech.t1 |
| 16 | root | localhost | employees | Query | 10 | Waiting for table metadata lock | select * from slowtech.t1 |
| 17 | root | localhost | employees | Query | 0 | starting | show processlist |
+----+-----------------+-----------+-----------+---------+------+---------------------------------+------------------------------------+
rows in set (0.00 sec)

If it happens online, it will undoubtedly affect the business. Therefore, it is generally recommended to perform DDL operations during the off-peak period of business. In fact, there are two considerations: 1. Avoid a significant impact on the system load. 2. Reduce the probability of DDL being blocked.

Background of the introduction of MDL

MDL was introduced in MySQL 5.5.3 and is mainly used to solve two problems.

The problem of non-repeatable read under RR transaction isolation level

As shown below, the demonstration environment uses MySQL 5.5.0.

session1> begin;
Query OK, 0 rows affected (0.00 sec)

session1> select * from t1;
+------+------+
| id | name |
+------+------+
| 1 | a |
| 2 | b |
+------+------+
rows in set (0.00 sec)

session2> alter table t1 add c1 int;
Query OK, 2 rows affected (0.02 sec)
Records: 2 Duplicates: 0 Warnings: 0

session1> select * from t1;
Empty set (0.00 sec)

session1> commit;
Query OK, 0 rows affected (0.00 sec)

session1> select * from t1;
+------+------+------+
| id | name | c1 |
+------+------+------+
| 1 | a | NULL |
| 2 | b | NULL |
+------+------+------+
rows in set (0.00 sec)

It can be seen that although it is the RR isolation level, the second query has no results when the transaction is turned on.

Master-slave replication problem

Including inconsistency between master and slave data, interruption of master-slave replication, etc.

For example, the master-slave data is inconsistent as shown below.

session1> create table t1(id int,name varchar(10)) engine=innodb;
Query OK, 0 rows affected (0.00 sec)

session1> begin;
Query OK, 0 rows affected (0.00 sec)

session1> insert into t1 values(1,'a');
Query OK, 1 row affected (0.00 sec)

session2> truncate table t1;
Query OK, 0 rows affected (0.46 sec)

session1> commit;
Query OK, 0 rows affected (0.35 sec)

session1> select * from t1;
Empty set (0.00 sec)

Let's take a look at the results from the library

session1> select * from slowtech.t1;
+------+------+------+
| id | name | c1 |
+------+------+------+
| 1 | a | NULL |
+------+------+------+
row in set (0.00 sec)

Looking at the contents of the binlog, you can see that the truncate operation is recorded first and the insert operation is recorded later.

# at 7140
#180714 19:32:14 server id 1 end_log_pos 7261 Query thread_id=31 exec_time=0 error_code=0
SET TIMESTAMP=1531567934/*!*/;
create table t1(id int,name varchar(10)) engine=innodb
/*!*/;

# at 7261
#180714 19:32:30 server id 1 end_log_pos 7333 Query thread_id=32 exec_time=0 error_code=0
SET TIMESTAMP=1531567950/*!*/;
BEGIN
/*!*/;
# at 7333
#180714 19:32:30 server id 1 end_log_pos 7417 Query thread_id=32 exec_time=0 error_code=0
SET TIMESTAMP=1531567950/*!*/;
truncate table t1
/*!*/;
# at 7417
#180714 19:32:30 server id 1 end_log_pos 7444 Xid = 422
COMMIT /*!*/;

# at 7444
#180714 19:32:34 server id 1 end_log_pos 7516 Query thread_id=31 exec_time=0 error_code=0
SET TIMESTAMP=1531567954/*!*/;
BEGIN
/*!*/;
# at 7516
#180714 19:32:24 server id 1 end_log_pos 7611 Query thread_id=31 exec_time=0 error_code=0
SET TIMESTAMP=1531567944/*!*/;
insert into t1 values(1,'a')
/*!*/;
# at 7611
#180714 19:32:34 server id 1 end_log_pos 7638 Xid = 421
COMMIT /*!*/;

If session 2 executes the drop table operation, the master-slave relationship will be interrupted.

Interestingly, if session 2 executes the alter table operation, it will still be blocked, and the blocking time is limited by the innodb_lock_wait_timeout parameter.

mysql> show processlist;
+----+------+-----------+----------+---------+------+-------------------+---------------------------+
| Id | User | Host | db | Command | Time | State | Info |
+----+------+-----------+----------+---------+------+-------------------+---------------------------+
| 54 | root | localhost | NULL | Query | 0 | NULL | show processlist |
| 58 | root | localhost | slowtech | Sleep | 1062 | | NULL |
| 60 | root | localhost | slowtech | Query | 11 | copy to tmp table | alter table t1 add c1 int |
+----+------+-----------+----------+---------+------+-------------------+---------------------------+
rows in set (0.00 sec)

Basic Concepts of MDL

First, let’s look at the official statement.

To ensure transaction serializability, the server must not permit one session to perform a data definition language (DDL) statement on a table that is used in an uncompleted explicitly or implicitly started transaction in another session.

The server achieves this by acquiring metadata locks on tables used within a transaction and deferring release of those locks until the transaction ends.

A metadata lock on a table prevents changes to the table's structure.

This locking approach has the implication that a table that is being used by a transaction within one session cannot be used in DDL statements by other sessions until the transaction ends.

From the above description, we can see that

1. The original intention of MDL is to protect the structure of a table in a transaction from being modified.

2. The transactions mentioned here include two types, explicit transactions and AC-NL-RO (auto-commit non-locking read-only) transactions. There are two types of explicit transactions: 1. Operations with AutoCommit turned off, and 2. Operations started with begin or start transaction. AC-NL-RO can be understood as a select operation with AutoCommit turned on.

3. MDL is transaction-level and will be released only after the transaction ends. Before this, there was actually a similar protection mechanism, but it was at the statement level.

It should be noted that MDL is not only applicable to tables, but also to other objects, as shown in the following table, where "wait state" corresponds to the State in "show processlist".

In order to improve the concurrency of the database, MDL is divided into 11 types.

• MDL_INTENTION_EXCLUSIVE
• MDL_SHARED
• MDL_SHARED_HIGH_PRIO
• MDL_SHARED_READ
• MDL_SHARED_WRITE
• MDL_SHARED_WRITE_LOW_PRIO
• MDL_SHARED_UPGRADABLE
• MDL_SHARED_READ_ONLY
• MDL_SHARED_NO_WRITE
• MDL_SHARED_NO_READ_WRITE
• MDL_EXCLUSIVE

Commonly used ones are MDL_SHARED_READ, MDL_SHARED_WRITE and MDL_EXCLUSIVE, which are used for SELECT operations, DML operations and DDL operations respectively. For other types of corresponding operations, please refer to the source code sql/mdl.h.

For MDL_EXCLUSIVE, the official explanation is,

/*
An exclusive metadata lock.
A connection holding this lock can modify both table's metadata and data.
No other type of metadata lock can be granted while this lock is held.
To be used for CREATE/DROP/RENAME TABLE statements and for execution of
certain phases of other DDL statements.
*/

In short, MDL_EXCLUSIVE is an exclusive lock. During its holding period, no other types of MDLs are allowed to be granted, including SELECT and DML operations.

This is why when a DDL operation is blocked, other subsequent operations will also be blocked.

Supplementary information about MDL

1. The maximum waiting time of MDL is determined by the lock_wait_timeout parameter, and its default value is 31536000 (365 days). This value is not reasonable when using tools to perform DDL operations. In fact, pt-online-schema-change and gh-ost have made corresponding adjustments, with the former taking 60s and the latter taking 3s.

2. If a SQL statement is syntactically valid but an error occurs during execution, for example, the column name does not exist, the MDL lock will also be acquired and will not be released until the transaction ends.

Summarize

The above is the full content of this article. I hope that the content of this article will have certain reference learning value for your study or work. If you have any questions, you can leave a message to communicate. Thank you for your support for 123WORDPRESS.COM.