MySQL metadata lock

Presumably played MySQL to waiting for table metadata lock is certainly not unfamiliar, generally is the alter operation was blocked, resulting in the show processlist when we see the state of the thread is waiting for metadata lock. This article will make a small summary of MySQL metadata lock, hope to help you.

The MDL was introduced to MySQL in 5.5 before it had a mechanism similar to protecting metadata, except that the MDL concept was not explicitly presented. however, before 5.5 versions (for example, 5.1) and 5.5, there is a significant difference between the protection of metadata, 5.1 is the statement level for metadata protection, and 5.5 for metadata protection is the transaction level. The so-called statement level, that is, when the statement executes, the table structure can be updated by other sessions, regardless of whether the transaction is committed or rolled back, and the transaction level releases the MDL after the transaction has finished.

after the introduction of MDL, the main solution to the 2 problems, one is the transaction isolation problem, such as under the Repeatable isolation level, session a in 2 queries, session B changes the table structure, two query results will be inconsistent, can not meet the requirements of repeatable read; the other is the problem of data replication, For example, when session a executes multiple update statements, and another session B makes a table structure change and commits it first, it causes the slave to redo the alter when redo, and then redo the update when the error occurs.

     5.5, under what circumstances will encounter MDL Lock, I combined with the actual situation 3 will appear MDL scene, to analyze the MDL lock timing. The following. The tests are based on the MySQL 5.5 version and do not take into account the online DDL, the next Boven details the 5.6 online DDL.