MySQL metadata Lock

What is meta data

Data that describes the data in the database are metadata, such as the library name, indication, column name, version name, and most of the content shown in the show statement is metadata, as well as the contents of the table that records database objects in Information_shema

Why MySQL wants to set metadata lock

In order to ensure that database objects can be accessed concurrently and to ensure consistency of data, the application of metadata lock, such as session1 is scanning the T table data, this session holds a metadata lock on the T table, then session2 to try to drop the T table, When trying to get the T-table metadata Lock is blocked, if there is no MDL design, then before the Session1 execution, session2 the table drop, then the optimizer in the scan to the table half of the time suddenly thrown out of the table does not exist exception

How the MDL Works

Operation of the Transaction table T and non-transactional table NT in the same transaction, if the transaction does not end, the other session cannot perform DDL operations on table T and NT

If a statement in a transaction passes a syntax check but throws an exception at execution time, but this transaction does not end, the DDL operation on the table to which it is involved is also blocked, and the test is shown in example two

Test MDL

Example One

# Session1 -: -: -[Test] (;) >begin; Query OK,0Rows Affected (0.00sec) -: -: -[Test] (;) >Select* fromT1 limit1;+----+------+------+------+| ID | col1 | col2 | COL3 |+----+------+------+------+|2|3|5| NULL |+----+------+------+------+1Rowinch Set(0.00sec) #session2 -: -: -[Test] (;) > ALTER TABLE t1 add COL4int; #session3|4834501| Root | localhost | Test | Query | -| Waiting forTable metadataLock| ALTER TABLE T1 add COL4int|

Example Two

Session1 -: -: $[Test](;)> begin; Query OK,0Rows Affected (0.00sec) -: -: +[Test](;)> Insert  intoT1 (ID)Values(2); ERROR1062(23000): Duplicate Entry'2'  for Key 'PRIMARY'Session2 -: -: +[Test](;)> Alter TableT1DropCol4;session3| 4834501 |Root|localhost|Test|Query|       A |Waiting for TableMetadata Lock| Alter TableT1DropCol4|

MDL Wait Timeout Long default value is one year

 -: -: +[(None)](;)> Select @ @lock_wait_timeout;+---------------------+| @ @lock_wait_timeout |+---------------------+|            31536000 |+---------------------+1Rowinch Set(0.00Sec

Suggestions

Try to avoid the DDL operation of large table when the operation is frequent; The MDL timeout can be set according to the business scenario; it is necessary to do MDL monitoring alarm

Official Document: Https://dev.mysql.com/doc/refman/5.6/en/metadata-locking.html

MySQL metadata Lock