Shulou(Shulou.com)06/01 Report--

What is MDL?

MDL,Meta Data lock, metadata lock, is commonly referred to as dictionary lock. Dictionary locks correspond to data locks. Dictionary lock is to protect the data object is changed, generally some DDL will change the dictionary object, such as two things, thing 1 first query the table, and then thing 2 attempts to alter, it first needs to wait for the end of transaction 1 (commit or rollback), the state at this time is Waiting for table metadata lock, and then the dictionary lock can be obtained. Any subsequent operations on TableA (including reads) cannot be performed and will be queued for Waiting for table metadata lock during the Opening tables phase. If such a lock waiting queue appears in the core table of the online business, it will cause disastrous consequences. A data lock protects the data in a table. For example, when two things update a row at the same time, the transaction that gets the row lock first executes first, and the latter can only wait.

What happened:

Scenario 1:

Through show processlist, you can see that there are operations (including reads) in progress on the TableA, and when the alter table statement cannot acquire the metadata exclusive lock, it will wait. Waiting for table metadata lock appears

Scenario 2:

You don't see any operations on the TableA through show processlist, but there are actually uncommitted transactions, which you can see in information_schema.innodb_trx. The lock on TableA is not released until the transaction is completed, and alter table also cannot acquire the exclusive lock of metadata.

Scenario 3:

You don't see any operations on TableA through show processlist, and there are no transactions in progress in information_schema.innodb_trx. This is probably because in an explicit transaction, a failed operation is performed on TableA (such as querying a field that does not exist), and the transaction does not start, but the lock acquired by the failed statement is still valid. Failed statements can be found in the performance_schema.events_statements_ current table.

Attachment: http://down.51cto.com/data/2368700

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.