Mysql metadata lock

Probably those who have played mysql will certainly be familiar with Waiting for table metadata lock. Generally, they are blocked during the alter operation, resulting in the show processlist, the thread status is waiting for metadata lock. This article will make a small Summary of mysql metadata lock and hope to help you.

MDL was introduced to mysql only in mysql 5.5. A mechanism similar to metadata protection was also provided before, but the MDL concept was not explicitly proposed. However, a major difference between versions earlier than 5.5 (such as 5.1) and 5.5 in protecting metadata is that 5.1 protects metadata at the statement level, 5.5 metadata protection is at the transaction level. The so-called statement level, that is, after the statement is executed, the table structure can be updated by other sessions regardless of whether the transaction is committed or rolled back. The transaction level is released after the transaction ends.

After MDL is introduced, it mainly solves two problems. One is transaction isolation. For example, at the repeatable isolation level, session A is in the two query periods, when session B modifies the table structure, the results of the two queries will be inconsistent and cannot meet the requirements of repeatable reading. The other is data replication, for example, when session A executes multiple update statements, another session B changes the table structure and submits the statement first. This will cause slave to redo the alter statement first during redo, duplicate errors occur when you redo the update operation.

5.5 and later, under what circumstances will the MDL lock be encountered? I will give three scenarios based on the actual situation to analyze the MDL locking time. The tests below are based on mysql 5.5 and do not take online ddl into account. The next blog will detail the online ddl of mysql 5.6.

1. Large query or mysqldump causes alter to wait for MDL

Time Point	Session	Session B	Session C
1	Select count (*) from t;
2		Alter table t add column c3 int; blocking
3			Show processlist; B: Waiting for table metadata lock
4	A: The execution is complete.
5			Show processlist; B: copy to tmp table
6		B: continue execution
7	Select count (*) from t;
8			Show processlist; A: Sending data B: copy to tmp table
9	A: The execution is complete.

Table 1

From table 1, we can see that session A executes select and B before alter. Before session A completes execution, session B cannot obtain the MDL lock. After session A is executed at point 4, session B gets the MDL lock and changes to the copy to tmp table status. At point 7, session A executes the query again, at this time, both A and B are executed normally, which means that for the MDL lock, select will block alter, and alter will not block select.

2. There are uncommitted transactions in the table, causing alter to wait for MDL

Time Point	Session	Session B	Session C
1	Set autocommit = 0; Update t set c2 = '000000' where c1 = 4;
2		Alter table t drop column c3;
3			Show processlist; B: Waiting for table metadata lock
4	A: Submit the transaction. Commit
5			Show processlist; B: copy to tmp table
6		B: continue execution
7	Update t set c2 = '000000' where c1 = 4; blocking
8			Show processlist; A: Waiting for table metadata lock B: copy to tmp table
9		B execution completed
10	Executed by

Table 2

From table 2, we can see that after session A executes the update statement for the first time, it is not submitted. As A result, session B needs to wait for the MDL lock when executing the alter statement. point in time 4, session A submits the transaction, in this case, session B acquires the MDL lock and starts execution. session A initiates the update operation again at point 7, and session A is blocked. This indicates that update will block alter for the MDL lock, similarly, alter also blocks update.

PS: Time Point 3 because show processlist only shows that alter is blocked, but it is unclear who is blocked, you can find the active transaction by checking information_schema.innodb_trx.

3. This is a special case of 1st cases. There is a query failure statement, such as a column that does not exist in the query. If the statement fails to return, but the transaction is not committed, alter will still be blocked.

Time Point	Session	Session B	Session C
1	Start transaction; Select c99 from t; Unknown column 'c99' in 'field list'
2		Alter table t drop column c3;
3			Show processlist; B: copy to tmp table
4			Show processlist; B: Waiting for table metadata lock
5	A: Submit the transaction. Commit
6		Execution completed

Table 3

Note that time 1 indicates that session A must enable A transaction. Otherwise, the query will end with an implicit rollback and cannot reproduce the preceding scenario. After session B executes the alter statement, it does not immediately block it, but immediately starts copying to tmp table. After the process is completed, the MDL lock waits. How can this be explained? The alter operation is mainly divided into three steps: creating a temporary new table, inserting data from the old table, and performing a temporary new TABLE rename to the old table. In this case, the MDL lock is required only in the last step, so it will not be blocked during the copy process. Because no query is in progress and the query does not enter the innodb layer (failed to return), show processlist and information_schema.innodb_trx have no information for reference.

There is a small doubt here. For the first and second cases, alter immediately blocks at the beginning, and the third case is block after copy ends. Experiment shows that opening tables blocks the first and second cases when autocommit = 0 and show profile commands. What is the relationship between opening table and autocommit? the specific reason for this problem has not yet been clarified. If you are interested, you can go to the debug source code to check whether the problem exists.

 

Root @ chuck 11:57:41> show profile for query 4;
+ ---------------------- + ----------- +
| Status | Duration |
+ ---------------------- + ----------- +
| Startling | 0.000050 |
| Check permissions | 0.000004 |
| Check permissions | 0.000005 |
| Init | 1, 0.000007 |
| Opening tables | 19.068828 |
| System lock | 0.000011 |
| Setup | 0.000034 |
| Creating table | 0.005047 |
| After create| 0.000056 |
| Copy to tmp table | 89.574539 |
| Rename result table | 1.101672 |
| End| 0.000040 |
| Query end | 0.000004 |
| Closing tables | 0.000009 |
| Freeing items | 0.000021 |
| Logging slow query | 0.000002 |
| Logging slow query | 0.000090 |
| Cleaningup | 0.000004 |
+ ---------------------- + ----------- +

 

Refer:

Http://www.mysqlperformanceblog.com/2013/02/01/implications-of-metadata-locking-changes-in-mysql-5-5/

Http://ctripmysqldba.iteye.com/blog/1938150