The metadata lock (MDL) mainly manages the synchronization and mutual exclusion of different threads operating the same data object to ensure the consistency of metadata. The MDL has been introduced since MySQL 5.5. It is a table at the table level and prevents any data from being read or written, resulting in SQL statements being blocked
Procedure
1.Connect to a MySQL instance. For details, see Introduction to Instance Connection Mode. You can connect the instance through an Intranet or public network.
2.Execute the following SQL in the SQL window to view all thread statuses in the database.
3.show full processlist
4.If there is a large number of Waiting for table metadata locks in the State column, it indicates a block. In the corresponding Info column, you can view the operation of the corresponding table and find the session ID that operates on the table.
5.Execute the following command in the SQL window to unlock the MDL.
6.kill id
How do I avoid MDL blocking?
MDL blocking, once happens, will have a great impact on the service, because all subsequent accesses to the table will be blocked, resulting in a connection backlog of connections. Therefore, avoid the occurrence of MDL blocking during daily use. Here are some optimization recommendations for your reference:
• Open the metadata_locks table to record the MDL.
• Set the lock_wait_timeout parameter to a small value so that the blocked end can actively stop.
• Standardize the use of transactions and timely commit transactions. Do not use large transactions.
Perform DDL operations and backup operations during off-peak hours.