What is MetaData Lock?
MySQL uses metadata locking to manage concurrent access to database objects, and to ensure data consistency when performing modifications to the schema: DDL operations. Metadata locking applies not just to tables, but also to schemas and stored programs (procedures, functions, triggers, and scheduled events).
In this post I am going to cover metadata locks on tables and triggers, that are usually seen by DBAs during regular operations/maintenance.
Kindly refer to these 4 different connections to MySQL Instance:
The screenshot shows that the uncommitted transaction may cause metadata lock to ALTER operations. The ALTER will not proceed until the transaction is committed or rolled-back. What is worse, after the ALTER is issued, any queries to that table (even simple SELECT queries) will be blocked. If the ALTER operation is an …[Read more]