Understanding MySQL Metadata Locks (MDL) and Their Impact on DDL/DML Operations

MySQL introduced Metadata Locks (MDL) in version 5.5 to protect table metadata and ensure consistency between DDL and DML operations. When a transaction holds an MDL lock, other sessions cannot modify the table structure, and DDL statements must wait for the lock to be released.

In MySQL 5.7 the performance_schema.metadata_locks table records MDL information, but it is disabled by default. It can be enabled with:

mysql> update performance_schema.setup_instruments set enabled='YES', TIMED='YES' where name='wait/lock/metadata/sql/mdl';

Several experimental scenarios illustrate MDL behavior:

Scenario 1: A simple SELECT SLEEP(10) FROM t holds a SHARED_READ lock, visible in metadata_locks .

Scenario 2: A BEGIN followed by SELECT keeps the SHARED_READ lock until the transaction ends.

Scenario 3: An uncommitted INSERT acquires a SHARED_WRITE lock.

Scenario 4: While the INSERT from Scenario 3 is pending, another session attempts ALTER TABLE and hangs, showing INTENTION_EXCLUSIVE , SHARED_UPGRADABLE , and EXCLUSIVE lock types.

Scenario 5: A subsequent SELECT also hangs, still holding a SHARED_READ lock.

Scenario 6: Committing or rolling back the first transaction releases the MDL locks, allowing the DDL and subsequent SELECT to proceed.

The article lists common MDL lock modes and the SQL statements that trigger them, such as MDL_SHARED_READ for SELECT and MDL_EXCLUSIVE for ALTER TABLE.

Key observations:

Even plain SELECT statements acquire a SHARED_READ MDL lock.

Uncommitted transactions block DDL operations.

If a DDL statement is waiting on an MDL lock, all other sessions (including simple SELECTs) will also wait.

MDL locks are server‑level table locks applicable to all storage engines. Their blocking rules mirror those of row‑level locks: read‑vs‑write and write‑vs‑write block each other, while read‑vs‑read do not.

To diagnose MDL contention, query performance_schema.metadata_locks , threads , and events_statements_history . An example query returns the waiting and blocking sessions and even generates a KILL command to release the blocker.

Finally, the article advises proper transaction management—keeping transactions short and ensuring they are explicitly committed or rolled back—to avoid long‑lasting MDL waits, which by default can persist for up to a year (controlled by lock_wait_timeout ).