MySQL metadata lock (ii)

The last article, "MySQL metadata lock (i)," describes the reasons why the introduction of MDL,MDL action and the MDL lock lead to blocking several typical scenarios, the end of the article left a little doubt. This article will introduce the MDL in more detail, focusing mainly on the principle and implementation of MDL. In general, the business database system implements the lock, generally divides the lock into a read lock (shared lock) and a write lock (exclusive lock), in order to further improve concurrency, will also add the intent to share the lock and intent exclusive lock. But MySQL's MDL is more complex, but the goal is to improve concurrency. MDL consists of 9 types, detailed reference table 1. In fact, there are two major categories, just a further subdivision of the shared lock.

I. The type of the MDL lock

Lock name	Lock type	Description	Applicable statements
Mdl_intention_exclusive	Shared locks	Intent lock, lock a range	Any statement will get the MDL intent lock, Then get a stronger level of MDL lock.
Mdl_shared		Shared locks, indicating access to table structures only
Mdl_shared_high_prio		Shared locks, access to table structure only	Show CREATE TABLE, etc. Access to INFORMATION_SCHEMA statements only
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		Upgradeable locks, access to table structure and read and write table data	The ALTER statement intermediate procedure uses the
Mdl_shared_no_write		You can upgrade locks, Access table structures and read and write table data, and prohibit other transactions from being written.	The ALTER statement intermediate procedure uses the
Mdl_shared_no_read_write		Can upgrade locks, Access table structure and read and write table data, and prohibit other transactions to read and write.	LOCK TABLES ... WRITE
Mdl_exclusive	Write lock	Prohibit other transactions from reading and writing.	DDL statements such as Create/drop/rename table.

Table 1

Second, MDL compatibility matrix

 	ix		sh	SR	SW	su	SNW	SNRW	x
ix	1		1	1	1	1	1	1	1
s	1		1	1	1	1	1	1	0
sh	1		1	1	1	1	1	1	0
SR	1		1	1	1	1	1	0	0
SW	1		1	1	1	1	0	0	0
su	1		1	1	1	1	0	0	0
SNW	1		1	1	0	0	0	0	0
SNRW	1		1	0	0	0	0	0	0
X	1	0	0	0	0	0	0	0	0

Description: Horizontal represents the lock that other transactions already hold, portrait of the Lock the transaction wants to add

Three, several typical statements of the addition (release) lock process

1.select statement Operation MDL Lock Process

1) Opening tables stage, plus shared lock

A) plus mdl_intention_exclusive lock

b) plus Mdl_shared_read lock

2) Transaction commit phase, release MDL lock

A) Release mdl_intention_exclusive lock

b) Release Mdl_shared_read lock

2. DML statement Operation MDL Lock Process

1) Opening tables stage, plus shared lock

A) plus mdl_intention_exclusive lock

b) plus Mdl_shared_write lock

2) Transaction commit phase, release MDL lock

A) Release mdl_intention_exclusive lock

b) Release Mdl_shared_write lock

3. alter operation MDL Lock Process

1) Opening tables stage, plus shared lock

A) plus 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 temp table tmp, redefine TMP as modified table structure

b) Read data from the original table 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 the TMP to the original table name

4) Transaction commit phase, release MDL lock

A) Release mdl_intention_exclusive lock

b) Release mdl_exclusive lock

Four, the typical problem analysis.

In general, we focus on MDL locks, most of which are online anomalies. So, after the exception, how do we judge the MDL Lock caused it? There are two main ways to monitor MDL locks, one is to determine if a transaction is in the "waiting for table metadata lock" state through the show Processlist command, and also through the MySQL profile, Analyze time-consuming times for specific statements at each stage.

Throw a few questions:

1. Whether the select and alter will block each other
2. Whether DML and alter will block each other
3. Whether select and DML will block each other

With the locking process of the third section, we can easily get answers to these three questions. The statement is blocked at a specific point and can be verified by profile to verify that our answer is correct.

First, when the SELECT statement is executed, if ALTER does not execute to the rename phase before the SELECT statement acquires the Mdl_shared_read lock, then the select acquires the Mdl_shared_read lock successfully, Subsequent alter execution to the rename stage, when requested mdl_exclusive lock, will be blocked. The rename stage holds the Mdl_exclusive lock, but because the process is very short (the big head is in the copy data phase) and is the last phase of the alter, the basic sense is that the alter will block the SELECT statement. Because the MDL Lock is released after the transaction has been committed, the DDL is stuck if there is a large query on the line, or if there is an uncommitted transaction. It should be noted here that the DDL is stuck, if there is a select query or DML come in, will be blocked, the threadrunning high situation.

The second issue, ALTER will upgrade the lock to the mdl_shared_no_write,rename phase in the opening phase and then upgrade to Mdl_exclusive, due to Mdl_shared_no_write and mdl_shared_ Write mutex, so executing an alter or executing a DML statement first will cause the statement to block at the opening tables stage. In conjunction with the first and second questions, you can answer the question "MySQL metadata lock (i)".

The third problem, obviously, is that because Mdl_shared_write is compatible with Mdl_shared_read, they do not cause a wait situation because of MDL. Specific examples and profile analysis can be found in MySQL metadata lock (i).

MySQL metadata lock (ii)