What is MySQL Metadata Lock in DDL?

When executing Data Definition Language (DDL) statements in MySQL, the database system employs a crucial concurrency control mechanism known as the Metadata Lock (MDL). This article explains the core function of MDL during DDL operations, how it prevents data inconsistency, and how it manages concurrent access to database schemas to ensure transactional integrity.

The Core Function of Metadata Locks (MDL)

The primary purpose of the MySQL Metadata Lock (MDL) is to isolate table schema changes from active data operations. It ensures that while a session is reading or writing to a table, no other session can modify the structure of that table.

Before MDL was fully implemented (prior to MySQL 5.5), a transaction could begin reading a table, and a concurrent DDL statement (such as DROP TABLE or ALTER TABLE) could execute mid-transaction. This resulted in broken queries, replication desynchronization, and corrupted transactions. MDL prevents this by locking the “metadata” (the table’s definition) rather than the actual data rows.

How MDL Works During DDL Execution

MDL operates using a system of shared and exclusive locks:

• Shared Locks for Data Operations (DML): When a query (such as SELECT, INSERT, UPDATE, or DELETE) executes, MySQL automatically acquires a Shared Metadata Lock on the target table. Multiple sessions can hold shared locks on the same table simultaneously, allowing concurrent read/write operations to proceed without blocking one another.
• Exclusive Locks for Schema Changes (DDL): When a DDL statement (such as ALTER TABLE, DROP TABLE, or RENAME TABLE) is executed, MySQL requests an Exclusive Metadata Lock.

An exclusive lock cannot be granted if any shared locks are currently active. Consequently, the DDL statement must wait in a queue until all active transactions holding shared locks on that table are completed (either committed or rolled back). Once the exclusive lock is granted, all subsequent queries attempting to access the table are blocked until the DDL operation completes and releases the lock.

Why Metadata Locks are Critical

• Ensures Transactional Integrity: MDL guarantees that the structure of a table remains constant for the entire duration of a transaction, preventing “dirty reads” of schema definitions.
• Guarantees Replication Safety: It ensures that statements are written to the binary log in the correct chronological order, preventing replication desynchronization between primary and replica servers.
• Protects Long-running Queries: It prevents active analytical queries or backups from failing due to sudden, mid-execution schema modifications.

The Risk of MDL Blocking

While MDL is essential for database safety, it can introduce performance bottlenecks. Because MySQL prioritizes exclusive lock requests, a pending DDL statement waiting for a long-running query to finish will block all subsequent incoming queries trying to access that table. If left unchecked, this queueing behavior can quickly exhaust the database connection pool and cause application-wide downtime.