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

How to understand the mysql Meta Lock mechanism, in view of this problem, this article introduces the corresponding analysis and answers in detail, hoping to help more partners who want to solve this problem to find a more simple and feasible way.

Reason

Recently, in the process of launching, there is a phenomenon of inconsistency between master and slave data. It is found that the problem is that slave waits for Waiting for table metadata lock when performing ddl operations.

It is found that the query user has several processes on the connection, and the bytes kill off these threads. Slave basically executes synchronization instantly.

Because it takes a long time to wait for the online operation of stopping the application. But it has no impact on the business.

Found time, did not start the application. It also causes the problem of avalanche from the library.

To solve the problem. And the requirement of online downtime is more strict in the future. Short downtime or memory read-write separation or performs the following analysis during ddl

Why is there a MDL lock?

In MySQL5.1 and previous versions, if there was an uncommitted transaction trx, when the DROP/RENAME/ALTER TABLE RENAME operation was performed, it would not be blocked by other transactions. This can lead to the following problems (MySQL bug#989)

Master:

Uncommitted transactions, but SQL has been completed (binlog is also ready), table schema changes, will not be detected during commit.

Slave:

In binlog, it is recorded in transaction commit order, and DDL commits implicitly, so the DDL is executed first in the slave database, followed by the transaction trx. Because the table used by trx has changed, trx will fail.

The greater the DML pressure of the main library during DDL, the more likely it is to trigger this problem.

A simple example:

Session1,set autocommit=0, execute a DML on table b

Root@xxx 11:48:28 > set autocommit = 0

Query OK, 0 rows affected (0.00 sec)

Root@xxx 11:48:35 > insert into b values (NULL,4)

Query OK, 1 row affected (0.00 sec)

Session2, execute rename table a to tmp_b

Root@xxx 11:48:23 > rename table b to tmp_b

Query OK, 0 rows affected (0.01 sec)

Session1:commit

Root@xxx 11:49:00 > show binlog events

+-+

| | Log_name | Pos | Event_type | Server_id | End_log_pos | Info | |

+-+

| | mysql-bin.000001 | 4 | Format_desc | 12 | 106 | Server ver: 5.1.48-log, Binlog ver: 4 |

| | mysql-bin.000001 | 106 | Query | 12 | 191 | use `xxx`; rename table b to tmp_b |

| | mysql-bin.000001 | 191 | Query | 12 | 258 | BEGIN |

| | mysql-bin.000001 | 258 | Table_map | 12 | 298 | table_id: 195 (xxx.b) |

| | mysql-bin.000001 | 298 | Write_rows | 12 | 336 | table_id: 195 flags: STMT_END_F | |

| | mysql-bin.000001 | 336 | Xid | 12 | 363 | COMMIT / * xid=737 * / | |

+-+

Obviously, when such Binlog is synchronized to the slave library, it will inevitably lead to replication interruption.

Bug can be bypassed by the following steps in 5.1:

> set autocommit = 0

Lock tables T1 write

> drop table T1 / alter table T1 rename to T2

DDL such as rename table T1 to T2 does not apply to the above methods.

The MDL (meta data lock) lock was introduced to solve this problem in 5.5. as for 5.1, the official has explicitly replied that it will not FIX, which is too sad.

Type of MDL

Metadata lock is also a kind of lock. Each metadata lock defines the object locked, the holding time of the lock, and the type of lock.

2.1 divided by object / scope

Attribute meaning scope / object GLOBAL global lock scope COMMIT submit protection lock scope SCHEMA library lock object TABLE table lock object FUNCTION function lock object PROCEDURE stored procedure lock object TRIGGER trigger lock object EVENT event lock object

MDL is classified by locked objects, which can be divided into global,commit,schema, table and function,procedure,trigger,event. When these objects are locked and waiting, we can see the following waiting information in show processlist.

Waiting for global read lock

Waiting for commit lock

Waiting for schema metadata lock

Waiting for table metadata lock

Waiting for stored function metadata lock

Waiting for stored procedure metadata lock

Waiting for trigger metadata lock

Waiting for event metadata lock

2.2 according to the holding time of the lock

The attribute meaning MDL_STATEMENT is obtained at the beginning of the statement execution and released at the end of the statement execution. MDL_TRANSACTION involves getting MDL for all tables in a transaction until the transaction commit or rollback (final cleanup in the thread) is released.

MDL_EXPLICIT needs to be explicitly released by MDL_context::release_lock ().

Statement or transaction ends, and still holds, such as

Lock table, flush.. With lock statement and so on.

2.3 according to the object of operation

Attribute meaning case MDL_INTENTION_EXCLUSIVE (IX) intended exclusive locks are used for locking global and commit. Truncate table t1

Insert into T1 values (3 recordings abcde'); will be added as GLOBAL,MDL_STATEMENT,MDL_INTENTION_EXCLUSIVE (SCHEMA,MDL_TRANSACTION,MDL_INTENTION_EXCLUSIVE)

MDL_SHARED (S) only accesses metadata such as table structure, not data. Set golbal_read_only = on lock

(GLOBAL,MDL_EXPLICIT,MDL_SHARED) MDL_SHARED_HIGH_PRIO (SH) is used to access information_scheam tables and does not involve data. Select * from information_schema.tables

Show create table xx; desc xxx; will be added as the following lock:

(TABLE,MDL_TRANSACTION,MDL_SHARED_HIGH_PRIO) MDL_SHARED_READ (SR) access table structure and read table data select * from T1; lock table T1 read

The following locks will be added:

(TABLE,MDL_TRANSACTION,MDL_SHARE_READ)

MDL_SHARED_WRITE (SW) accesses the table structure and writes the table data insert/update/delete/select.. For update

The following locks will be added:

(TABLE,MDL_TRANSACTION,MDL_SHARE_WRITE) MDL_SHARED_UPGRADABLE (SU) is a new metadata lock introduced by mysql5.6.

This lock is added in alter table/create index/drop index; it can be said that it was introduced for online ddl. The feature is to allow DML and prevent DDL.

(TABLE,MDL_TRANSACTION,MDL_SHARED_UPGRADABLE) MDL_SHARED_NO_WRITE (SNW) upgrades locks, accesses table structures and reads and writes table data, and forbids other transactions from writing. Alter table T1 modify c bigint; (non-onlineddl)

(TABLE,MDL_TRANSACTION,MDL_SHARED_NO_WRITE) MDL_SHARED_NO_READ_WRITE (SNRW) upgrades locks, accesses table structures and reads and writes table data, and forbids other transactions from reading and writing. Lock table T1 write; lock

(TABLE,MDL_TRANSACTION,MDL_SHARED_NO_READ_WRITEMDL_EXCLUSIVE (X) prevents other threads from reading and writing metadata CREATE/DROP/RENAME TABLE, and other online DDL also holds X locks during the rename phase

(TABLE,MDL_TRANSACTION,MDL_EXCLUSIVE)

About global object

The main function is to prevent the execution of set golbal_read_only = on or flush tables with read lock during DDL and write operations.

About commit object Lock

The main role is to prevent write transactions that are already in execution from committing after the flush tables with read lock is executed.

Insert/update/delete is COMMIT,MDL_EXPLICIT,MDL_INTENTION_EXCLUSIVE locked when it is submitted.

2.4 compatibility Matrix of MDL Lock

Third, the locking process of several typical statements.

1.select statement operation MDL lock process

1) Opening tables phase, add shared lock

A) add MDL_INTENTION_EXCLUSIVE lock

B) add MDL_SHARED_READ lock

2) transaction commit phase, release the MDL lock

A) release the MDL_INTENTION_EXCLUSIVE lock

B) release the MDL_SHARED_READ lock

2. DML statement operation MDL lock process

1) Opening tables phase, add shared lock

A) add MDL_INTENTION_EXCLUSIVE lock

B) add MDL_SHARED_WRITE lock

2) transaction commit phase, release the MDL lock

A) release the MDL_INTENTION_EXCLUSIVE lock

B) release the MDL_SHARED_WRITE lock

3. Alter operation MDL lock process

1) Opening tables phase, add shared lock

A) add MDL_INTENTION_EXCLUSIVE lock

B) add MDL_SHARED_UPGRADABLE lock, upgrade to MDL_SHARED_NO_WRITE lock

2) Operation data, copy data, the process is as follows:

A) create a temporary table tmp, and redefine tmp to the modified table structure

B) read data from the original table and insert it into the tmp table

3) upgrade the MDL_SHARED_NO_WRITE read lock to the MDL_ excluse lock

A) delete the original table and rename tmp to the original table name

4) release the MDL lock in the transaction commit phase

A) release the MDL_INTENTION_EXCLUSIVE lock

B) release the MDL_ Excluse lock

IV. Analysis of typical problems

Usually we focus on MDL locks, and in most cases there is an exception in the online DB. So after the exception, how can we tell that it is caused by the MDL lock. There are two main ways to monitor MDL locks, one is to determine whether a transaction is in the "Waiting for table metadata lock" state through the show processlist command, and the other is to analyze the time spent of a particular statement in each stage through the profile of mysql.

Throw out a few questions:

Will select and alter block each other?

Will dml and alter block each other?

Will select and DML block each other?

Combined with the locking process of several statements in section 3, it is easy to get the answers to these three questions. The sentence will be blocked in a specific link, and we can use profile to verify whether our answer is correct.

The first problem is that when executing a select statement, as long as the select statement is not executed to the rename stage before the select statement acquires the alter lock, then the select acquires the MDL_SHARED_READ lock successfully, and the subsequent alter executes to the rename phase, and the request for the MDL_EXCLUSIVE lock will be blocked. The rename phase holds MDL_EXCLUSIVE locks, but because this process takes a very short time (mostly in the copy data phase) and is the last phase of alter, there is little sense that alter will block select statements. Because the MDL lock is not released until the transaction is committed, if there is a large query online, or if there is an uncommitted transaction, the DDL will get stuck. It should be noted here that after ddl is stuck, if another select query or DML comes in, it will be blocked, and the threadrunning will soar.

The second problem is that alter will upgrade the lock to MDL_SHARED_NO_WRITE,rename phase and then to MDL_EXCLUSIVE in the opening phase. Because MDL_SHARED_NO_WRITE and MDL_SHARED_WRITE are mutually exclusive, executing alter or DML statements first will cause the statement to block in the opening tables phase.

The third problem is that obviously, because MDL_SHARED_WRITE is compatible with MDL_SHARED_READ, they don't wait because of MDL.

On the improvement of MDL Lock

The MDL subsystem has been more thoroughly optimized in MySQL 5.7. It mainly starts from the following points:

First, although the MDL HASH is partitioned, because the table name + library name is partitioned as the key value, if the query or DML are concentrated on the same table, it will hash to the same partition, causing obvious lock competition on the MDL HASH.

In view of this, the HASH of LOCK-FREE is introduced to store the MDL_lock,LF_HASH lock-free algorithm based on the paper "Split-Ordered Lists: Lock-Free Extensible Hash Tables", and the implementation is still complicated. Note: in fact, LF_HASH has been used in Performance Schema for a long time, which is a relatively mature code module.

Naturally, it was abolished directly because of the introduction of the LF_HASH,MDL HASH partitioning feature.

Corresponding to WL#7305, PATCH (Rev:7249)

Second, from the widely used actual scenarios, DML/SELECT is more common than high-level MDL lock types such as DDL, so it can reduce the MDL overhead of DML and SELECT operations.

In order to realize the fast locking of DML/SELECT, a locking method similar to LOCK-WORD is used, which is called FAST-PATH. If FAST-PATH locking fails, SLOW-PATH is used for locking.

Each MDL lock object (MDL_lock) maintains a state value of type long long to indicate the current locked state, and the variable name is MDL_lock::m_fast_path_state to give a simple example: (initially, the MDL_lock::m_fast_path_ state value on the sbtest1 table is 0)

Session 1: BEGIN

Session 1: SELECT * FROM sbtest1 WHERE id = 1; / / m_fast_path_state = 1048576, MDL ticket does not add MDL_lock::m_granted queue

Session 2: BEGIN

Session 2: SELECT * FROM sbtest1 WHERE id = 2; / / m_fast_path_state=1048576+1048576=2097152, ditto, go FAST PATH

Session 3: ALTER TABLE sbtest1 ENGINE = INNODB; / / the MDL_SHARED_UPGRADABLE type lock added by the DDL request is regarded as unobtrusive lock, which can be considered to be a higher-level lock than the MDL lock of the above SQL and is incompatible, so it is forced to leave slow path. And slow path is a write lock that needs to add MDL_lock::m_rwlock. M_fast_path_state = m_fast_path_state | MDL_lock::HAS_SLOW_PATH | MDL_lock::HAS_OBTRUSIVE

Note: DDL will also acquire library-level exclusive MDL locks or table-level shared upgradeable locks, but for ease of expression, it is ignored here and only the same MDL_lock lock object involved is considered.

Session 4: SELECT * FROM sbtest1 WHERE id = 3; / / check m_fast_path_state & HAS_OBTRUSIVE and walk slow path if DDL hasn't finished running.

As can be seen from the above description, the MDL subsystem explicitly distinguishes the lock types (OBTRUSIVE or UNOBTRUSIVE), which are stored in the array matrix m_unobtrusive_lock_increment. So for MDL lock types of compatible types, such as DML/SELECT, locking operations have almost no read-write lock or MUTEX overhead.

Corresponding to WL#7304, WL#7306, PATCH (Rev:7067,Rev:7129) (Rev:7586)

Third, due to the introduction of MDL locks, earlier versions of THR_LOCK used to control concurrency at the Server and engine level already have some redundancy for Innodb, so the Innodb table can completely ignore this part of the overhead.

However, in the existing logic, Innodb still relies on THR_LOCK to implement LOCK TABLE tbname READ, so a new MDL lock type is added to replace this implementation.

In fact, most of the changes to the code are to deal with the new MDL type, and the changes to Innodb are only a few lines of code.

Corresponding WL#6671,PATCH (Rev:8232)

Fourth, the user lock in the Server layer (acquired through the GET_LOCK function) is reimplemented using MDL.

Users can acquire multiple user locks at the same time through GET_LOCK (). At the same time, due to the use of MDL, deadlock detection can be realized with the help of MDL subsystem.

Note that due to this change, the naming of the user lock must be less than 64 bytes, which is due to the limitation of the MDL subsystem.

Corresponding to WL#1159, PATCH (Rev:8356)

This is the end of the answer to the question on how to understand the mysql Meta Lock mechanism. I hope the above content can be of some help to you. If you still have a lot of doubts to be solved, you can follow the industry information channel for more related knowledge.

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.