MySQL metadata lock meta data lock

What is MDL

Mdl,meta data lock, a metadata lock, is generally called a dictionary lock. The dictionary lock corresponds to a data lock. Dictionary lock is to protect the data object is changed, usually some DDL will change the Dictionary object, such as two things, things 1 query the table first, then the thing 2 to try to alter, which first need to wait for the end of things 1 (commit or rollback), at this time the state is waiting for table metadata Lock before you can get the dictionary lock. Any subsequent operation of TableA (including read) is not possible, and it will enter the queue of waiting for table metadata lock at the opening tables stage. If there is such a lock-waiting queue in the core table of the online business, it will have disastrous results. A data lock is a protection of data in a table, such as when two things update a row at the same time, the transaction that gets row lock first executes, the latter only waits.

What is happening:

Scenario One:

With show Processlist you can see that there is an ongoing operation (including read) on the TableA, at which time the ALTER TABLE statement cannot get to the metadata exclusive lock and waits. Waiting for table metadata lock appears

Scenario Two:

There is no action on TableA through show processlist, but there are actually uncommitted transactions that can be seen in Information_schema.innodb_trx. The lock on the TableA is not released until the transaction is complete, and ALTER TABLE also acquires an exclusive lock of metadata.

Scenario Three:

There is no action on TableA through show processlist, and there is no ongoing transaction in Information_schema.innodb_trx. This is most likely because in an explicit transaction, a failed operation was performed on TableA (such as querying a nonexistent field), and the transaction did not begin, but the lock acquired by the failed statement is still valid. Failed statements can be found from the Performance_schema.events_statements_current table.

This article is from the "MySQL Big Data Combat" blog, please be sure to keep this source http://277963679.blog.51cto.com/6966371/1976802

MySQL metadata lock meta data lock