Mysql metadata lock (3), mysqlmetadatalock

Mysql metadata lock (3), mysqlmetadatalock

Preface

The MDL lock is mainly used to protect the metadata of Mysql internal objects. The MDL mechanism ensures the concurrency of DDL, DML, and SELECT query operations. MySQL Meta Lock (1) and MySQL Meta Lock (2) have already talked about MDL knowledge. This article will supplement MDL, explains how to query congestion and mysqldump to obtain consistent backup.

1. MDL lock type

1. Divide by type

See MySQL Meta Lock (2)

2. Division by Object/range dimension

Attribute	Description	Range/Object
GLOBAL	Global lock	Range
COMMIT	Submit protection lock	Range
SCHEMA	Database lock	Object
TABLE	Table lock	Object
FUNCTION	Function lock	Object
PROCEDURE	Stored Procedure lock	Object
TRIGGER	Trigger lock	Object
EVENT	Event lock	Object

The MDL lock mainly consists of two dimensions: the DB object and the range. The MDL of the object is well understood to protect the metadata of the object. What about range-level locks? The essence of the lock is to protect shared resources, so both the scope and object can be understood as a resource. MYSQL stipulates that some operations must be subject to the COMMIT range lock or GLOBAL range lock. This synchronization mechanism ensures the orderly operation of each thread. The application scenarios of COMMIT and GLOBAL lock will be discussed in detail in the following cases.

3. By request/release lock duration

Attribute	Description
MDL _ STATEMENT	Statement level
MDL_TRANSACTION	Transaction Level
MDL_EXPLICIT	Release to be displayed

Another property of MDL is the holding time. If STATEMENT is used, MDL is released after a single STATEMENT is executed. TRANSACTION indicates that the MDL lock is released after the TRANSACTION ends. The first two are implicit locks, that is, the request lock and release lock are both internal behaviors of the system, and the user does not need to issue instructions, while MDL_EXPLICIT indicates that the MDL lock displays the request and release. For example, the command flush table with read lock will display the GLOBAL: MDL_EXPLICIT: SHARED and COMMIT: MDL_EXPLICIT: SHARED locks. You need to use the unlock tables command to display the release.

4. Example

Begin:

Update t3 set c1 = 1 where id = 1;

Commit;

Process	Execution statement	Execution content	Dictionary lock
1	Begin		Release MDL Release_transactional_locks
2	Update t3 set c1 = 1 where id = 1;	Request STATEMENT MDL	GLOBAL: STATMENT MDL_INTENTION_EXCLUSIVE
3		Request TRANSACTION MDL	TABLE: TRANSACTION MDL_SHARED_WRITE
6		Execute update
7		Release STATEMENT MDL	GLOBAL: STATMENT
8	Commit;	Request COMMIT MDL	COMMIT: MDL_EXPLICIT MDL_INTENTION_EXCLUSIVE
9		Submit execution
11		Release COMMIT MDL Release TRANSACTION MDL	COMMIT: MDL_EXPLICIT MDL_INTENTION_EXCLUSIVE
12			Release_transactional_locks TABLE: TRANSACTION

 

Ii. Principles of request/release locks

The lock compatibility matrix has been described in detail in metadata lock (2). After carefully reading the code, we found that the MDL lock Compatibility Matrix actually contains two parts: the active lock compatibility matrix, waiting for the lock compatibility matrix. When a request is locked, you must ensure that the values of the two matrices are compatible before the request can be successfully locked. Why design the wait lock Compatibility Matrix? I understand that DDL operations are prioritized. If a DDL operation is waiting for a query operation, other queries continue to enter, the DDL operation may never get the lock. In actual situations, DDL operations are often more important than queries or DML.

1. Request lock compatibility check:

1) check whether the request lock conflicts with an existing active lock. If yes, wait;

2) check whether the request lock conflicts with an existing wait lock. If yes, wait.

3) The request is locked successfully.

2. Release lock time:

1) release the STAMENT lock after the statement is executed.

2) When a transaction is committed, it requests the COMMIT type and releases the COMMIT type lock successively.

3) release the TRANSACTION lock after the TRANSACTION is committed.

Iii. MDL Application Scenario Analysis

MDL is an important lock at the Mysql level. The source and function implementation of many common problems depend on MDL. Below I will analyze several common problems and functions.

1. Why is the query blocked?

In our actual O & M process, a common scenario is to receive a high alarm from the mobile phone threadrunning and log on to the host. show processlist sees a large thread in the "Waiting for table metadata lock" status, of course, the query is also included. Next I will repeat this scenario with a simple example.

Time Point	Session		Session B	Session C
1	Begin Update t3 set c1 = 1 where id = 1;
2	Return
3			Alter table t3 add column c3 int;
4			Wait
5				Select * from t3
6				Wait
7	Show processlist Returned results
8	Init	Show processlist
	Waiting for table metadata lock	Alter table t3 add column c3 int
	Waiting for table metadata lock	Select * from t3

From table 2, we can see that the uncommitted transactions of session A block the DDL statements of session B, and the DDL statements block Session C. From the perspective of step 2, both session B and Session C are in the "Waiting for table metadata lock" status. From TABLE 1, we can see that the DML operation of session A will request the TABLE-TRANSACTION-MDL_SHARED_WRITE lock. Because no COMMIT is executed, it will always be held; the DDL operation of session B will request the TABLE-TRANSACTION-EXCLUSIVE lock. Because the two locks are mutually EXCLUSIVE, wait. The query operation of Session C will request the TABLE-TRANSACTION-MDL_SHARED_READ lock, although MDL_SHARED_READ does not conflict with the active lock MDL_SHARED_WRITE, it also waits because it conflicts with the wait lock EXCLUSIVE of session B. In this case, you must first check whether there is a blocked DDL. If there is a DDL, and then check whether there are large queries or uncommitted transactions, these two cases will cause the DDL to block, this affects common queries and DML operations.

2. Mysqldump and global lock

In the actual production environment, for Disaster Tolerance and load balancing, database services are generally composed of one master, one slave, and one slave instance. The master database provides read/write services externally, and the slave database provides read-only services, or simply for Disaster Tolerance. In this system, when creating a new instance through mysqldump, You need to obtain a consistent backup set and obtain the corresponding point (the basis for pulling the binlog of the master database ), copy a database instance in full + incremental mode. In Mysqldump, two key parameters -- master-data = 2 and -- single-transaction must be set to ensure consistent backup and obtain the corresponding position. We can trace the statements executed by Mysqldump through the trace function of mysqld. Suppose we want to back up the chuck database. The command is as follows:

 ./bin/mysqldump -uchuck -pchuck -P4006 –h127.0.0.1 --databases chuck mysql --master-data=2 --single-transaction --default-character-set=utf8 > chuck_dump.sql 2>chuck_dump.log

Enable the trace function of mysqld

--debug=d,query,general:O,/kkk/mysqld.trace

Through mysqld. trace, I sorted out the key statements and analyzed the results, as shown in. As you can see, mysqldump is mainly used to obtain incremental points through the two dictionary locks of the flush tables with read lock statement (global lock and commit lock ). The duration of the entire process is not long (if the statement is not blocked), the MDL lock is released immediately after the point is obtained. Steps 2nd to 6th are the time for holding the MDL lock. During this period, new transactions cannot be started, and existing transactions cannot be committed to ensure the correctness of the point. Setting the isolation level to RR is the key to obtaining consistent backup. The source of consistent backup is MVCC. For MVCC implementation, you can write an article later. Steps 8th to 9th are the backup process of a TABLE. The select statement obtains the MDL lock (TABLE: TRANSACTION: MDL_SHARED_READ). After execution, the MDL lock is released through the rollback statement. Therefore, DDL operations cannot be performed on the current table except the backup table. The DML of the current table and the DDL and DML of other tables are not affected. Therefore, during the Mysqldump backup process, it basically does not block other online statement execution.

Procedure	Key statements	Description	Function
1	FLUSH /*! 40101 LOCAL */TABLES	Close Open Table	Clear query Cache
2	FLUSH TABLES WITH READ LOCK	GLOBAL dictionary lock: GLOBAL: MDL_SHARED COMMIT: MDL_SHARED	Block new transactions and commit active transactions. Prepare for obtaining consistent points
3	SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ	Set the transaction isolation level to RR	Read snapshots
4	Start transaction /*! 40100 with consistent snapshot */	Start transaction
5	SHOW MASTER STATUS	Obtain the binlog point	Used to obtain the incremental point of the master database
6	UNLOCK TABLES	Release GLOBAL dictionary lock	Allow updates to other transactions
7	SAVEPOINT sp	Set save point	Subsequent tables can be created one by one Release MDL
8	SELECT /*! 40001 SQL _NO_CACHE */* FROM 't1'	Obtain table t1 data TABLE: TRANSACTION MDL_SHARED_READ	Consistent read
9	Rollback to savepoint sp	Release: TABLE: TRANSACTION MDL_SHARED_READ
10	SELECT /*! 40001 SQL _NO_CACHE */* FROM 't2'	Obtain table t2 data TABLE: TRANSACTION MDL_SHARED_READ	Consistent read
11	Rollback to savepoint sp	Release: TABLE: TRANSACTION MDL_SHARED_READ
12	......
13			Backup complete

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

References

Http://www.percona.com/blog/2010/04/24/how-fast-is-flush-tables-with-read-lock/

Http://blogread.cn/it/article/2338? F = hot1

Http://imysql.cn/2008_10_24_deep_into_mysqldump_options