Shulou(Shulou.com)05/31 Report--

This article shows you how to change the MySQL table structure Metadata Lock, the content is concise and easy to understand, can definitely brighten your eyes, through the detailed introduction of this article, I hope you can get something.

People who have played mysql must be no stranger to Waiting for table metadata lock. Generally, the operation of alter is blocked, which leads us to see that the state of the thread is waiting for metadata lock when we are in show processlist. This article will give a detailed introduction to the Metadata Lock of structural changes in the MySQL table.

When performing DDL operations online, relative to the system load it may bring, we are most worried about the blocking problem caused by MDL.

Once the DDL operation is blocked because the MDL cannot be obtained, other subsequent operations on the table will be blocked. Typically, if you block for a little longer, we will see a surge in Threads_running and an CPU alarm.

Mysql > show processlist +-+- -+ | Id | User | Host | db | Command | Time | State | Info | +-+ -+-+ | 4 | event_scheduler | localhost | NULL | Daemon | 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 put the DDL operation into the business trough, in fact, there are two aspects of consideration, 1. Avoid having a great impact on the system load. two。 Reduce the probability that DDL is blocked.

The background of MDL introduction

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

The problem of non-repeatable reading under the RR transaction isolation level

Demo environment, MySQL 5.5.0, as shown below.

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: 0session1 > select * from T1 emptiness 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)

As you can see, although it is the RR isolation level, the second query has no result when the transaction is turned on.

Master-slave replication problem

Including master-slave data inconsistency, master-slave replication interruption and so on.

For example, the master and slave data below are inconsistent.

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; Query OK, 1 row affected (0.00 sec) session2 > truncate table T1 boot 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 of Congku.

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

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

# at 7140180 714 19:32:14 server id 1 end_log_pos 7261 Query thread_id=31 exec_time=0 error_code=0SET timestamp 1531567934ash / at 726118714 19:32:30 server id 1 end_log_pos 7333 Query thread_id=32 exec_time=0 error_code=0SET timestamp 1531567950 # at 7333 "180714 19:32:30 server id 1 end_log_pos 7417 Query thread_id=32 exec_time=0 error_code=0SET timestamp 1531567950 balance" at 7417 "180714 19:32:30 server id 1 end_log_pos 7444 Xid = 422COMIT 19:32:34 server id 1 end_log_pos 7516 Query thread_id=31 exec_time=0 error_code=0SET timestamp 1531567954 # at 7516 "180714 19:32:24 server id 1 end_log_pos 7611 Query thread_id=31 exec_time=0 error_code=0SET timescale 1531567944 ash insert into T1 values / *! /; # at 7611" 180714 19:32:34 server id 1 end_log_pos 7638 Xid = 421COMMIT

If session 2 is performing a drop table operation, it will also cause a master-slave interruption.

Interestingly, if session 2 is performing an 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)

The basic concept of MDL

First of all, 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.

As can be seen from the above description

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

two。 There are two types of transactions mentioned here, explicit transactions and AC-NL-RO (auto-commit non-locking read-only) transactions. There are two types of explicit transactions: 1. Close the operation under AutoCommit, 2. An operation that begins with begin or start transaction. AC-NL-RO can be understood as the select operation enabled by AutoCommit.

3. MDL is transaction-level and will not be released until the transaction ends. Before that, there was a similar protection mechanism, but at the statement level.

It is important to note that MDL applies not only 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 subdivided 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 are MDL_SHARED_READ,MDL_SHARE D_WRITE and MDL_EXCLUSIVE, which are used for SELECT operation, DML operation and DDL operation respectively. For other types of corresponding operations, please see 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, and other types of MDL are not allowed to be granted during its holding, which naturally includes SELECT and DML operations.

This is why when DDL operations are blocked, other subsequent operations are blocked as well.

About the supplement of MDL

1. The maximum wait 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 for DDL operations. In fact, pt-online-schema-change and gh-ost adjust it accordingly, the former 60s and the latter 3s.

two。 If a SQL is syntactically valid, but the execution times is wrong, for example, the column name does not exist, it also acquires the MDL lock and is not released until the end of the transaction.

The above is how the structure of the MySQL table changes Metadata Lock. Have you learned any knowledge or skills? If you want to learn more skills or enrich your knowledge reserve, you are welcome to follow the industry information channel.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.