Mysql metadata lock (2), mysqlmetadatalock

Mysql metadata lock (2), mysqlmetadatalock

The previous article mysql metadata lock (1) introduced some typical scenarios of MDL, MDL, and blocking caused by MDL locks. At last, the article left a small question. This article will introduce MDL in more detail, focusing on the Principle and Implementation of MDL. In general, commercial database systems implement locks. Generally, locks are divided into read locks (shared locks) and write locks (exclusive locks). In order to further improve the concurrency, they also add intention shared locks and intention exclusive locks. However, the MDL of mysql is complicated, but the goal is to improve the concurrency. MDL contains nine types. For more information, see table 1. There are actually two main categories, but the shared locks are further subdivided.

1. MDL lock type

Lock name	Lock type	Description	Applicable statement
MDL_INTENTION_EXCLUSIVE	Shared lock	Intention lock, locking a range	Any statement will obtain the MDL intention lock, Then obtain a stronger MDL lock.
MDL_SHARED		Shared lock, indicating only accessing the table structure
MDL_SHARED_HIGH_PRIO		Share lock, only Access Table Structure	Show create table, etc. Only access the INFORMATION_SCHEMA statement
MDL_SHARED_READ		Access Table Structure and read Table Data	Select statement Lock table... READ
MDL_SHARED_WRITE		Access Table Structure and write table data	SELECT... FOR UPDATE DML statements
MDL_SHARED_UPGRADABLE		Locks can be upgraded to access the table structure and read/write table data.	The intermediate process of the Alter statement uses
MDL_SHARED_NO_WRITE		You can upgrade the lock, access the table structure, read and write table data, and prohibit other transaction writes.	The intermediate process of the Alter statement uses
MDL_SHARED_NO_READ_WRITE		You can upgrade the lock, access the table structure, read and write table data, and prohibit other transactions from reading and writing.	Lock tables... WRITE
MDL_EXCLUSIVE	Write lock	Prohibit other transactions from reading and writing.	CREATE/DROP/rename table and other DDL statements.

Table 1

Ii. Compatibility Matrix of MDL

	IX	S	SH	SR	SW	SU	SNW	SNRW	X
IX	1	1	1	1	1	1	1	1	1
S	1	1	1	1	1	1	1	1	0
SH	1	1	1	1	1	1	1	1	0
SR	1	1	1	1	1	1	1	0	0
SW	1	1	1	1	1	1	0	0	0
SU	1	1	1	1	1	1	0	0	0
SNW	1	1	1	1	0	0	0	0	0
SNRW	1	1	1	0	0	0	0	0	0
X	1	0	0	0	0	0	0	0	0

Description: it indicates the locks held by other transactions horizontally, and vertical indicates the locks to be applied by the transaction.

 

Iii. Add (release) Lock process for several typical statements

1. select statement MDL Lock operation process

1) Add a shared lock in the Opening tables phase

A) apply the MDL_INTENTION_EXCLUSIVE lock.

B) Apply the MDL_SHARED_READ lock.

2) release the MDL lock in the transaction commit phase

A) release the MDL_INTENTION_EXCLUSIVE lock.

B) release the MDL_SHARED_READ lock.

2. MDL Lock operation procedure using DML statements

1) Add a shared lock in the Opening tables phase

A) apply the MDL_INTENTION_EXCLUSIVE lock.

B) Apply the MDL_SHARED_WRITE lock.

2) release the MDL lock in the transaction commit phase

A) release the MDL_INTENTION_EXCLUSIVE lock.

B) release the MDL_SHARED_WRITE lock.

3. alter MDL Lock operation process

1) Add a shared lock in the Opening tables phase

A) apply the MDL_INTENTION_EXCLUSIVE lock.

B) Apply the MDL_SHARED_UPGRADABLE lock and upgrade it to the MDL_SHARED_NO_WRITE lock.

2) operation data and copy data. The process is as follows:

A) create a temporary table tmp and redefine tmp as 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_EXCLUSIVE 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_EXCLUSIVE lock.

 

4. Typical problem analysis.

In general, we pay attention to the MDL lock. In most cases, online exceptions occur. Then, how can we determine whether the MDL lock causes an exception. There are two main methods to monitor the MDL lock. One is to use the show processlist command to determine whether a transaction is in the "Waiting for table metadata lock" state, and the other is to use the mysql profile, analyze the time consumed by a specific statement in each stage.

Several issues are thrown out:

Combining the locking process of several statements in Section 3, we can easily get the answers to these three questions. The statement is blocked at a specific stage. You can use profile to verify whether our answer is correct.

First, when the select statement is executed, as long as the select statement is not executed to the rename stage before the MDL_SHARED_READ lock is obtained, the select statement successfully acquires the MDL_SHARED_READ lock, later, when an alter statement is executed in the rename stage and the MDL_EXCLUSIVE lock is requested, it will be blocked. The rename phase will hold the MDL_EXCLUSIVE lock, but because this process takes a very short time (the majority are in the copy data phase) and is the last stage of alter, as a result, we basically do not feel that alter will block the select statement. Because the MDL lock is released only after the transaction is committed, if there is a large query online or a transaction that has not been committed exists, the ddl is stuck. It should be noted that after the ddl is stuck, if a select query or DML comes in again, it will be blocked and threadrunning will rise.

The second problem is that alter will upgrade the lock to MDL_SHARED_NO_WRITE in the opening phase, and then upgrade the lock to MDL_EXCLUSIVE In the rename phase. Because MDL_SHARED_NO_WRITE and MDL_SHARED_WRITE are mutually exclusive, execute, the statement is blocked in the opening tables stage. In combination with the first and second questions, you can answer the questions in mysql metadata lock (1.

The third problem is that MDL_SHARED_WRITE is compatible with MDL_SHARED_READ, so they will not wait because of MDL. For specific examples and profile analysis, see mysql metadata lock (1).