Understanding MySQL MDL Locks: Types, Structures, and Debugging Techniques

The article introduces MySQL's Metadata Lock (MDL) subsystem, explaining why MDL locks are critical for preventing metadata contention and how they differ from InnoDB row locks. It outlines the various lock types (e.g., MDL_INTENTION_EXCLUSIVE, MDL_SHARED, MDL_EXCLUSIVE) and their typical usage scenarios such as DML, DDL, and FLUSH operations.

Key data structures are described in detail, including MDL_request , MDL_key , MDL_ticket , MDL_lock , and MDL_context . The article shows the fields that store lock type, duration, namespace, and fast‑path status, and explains how these structures interact during lock acquisition and release.

To aid debugging, a custom function my_print_ticket(const MDL_ticket* p_ticket) is added as a friend of MDL_ticket . The function logs thread ID, database name, object name, namespace, fast‑path flag, lock type, and duration, mirroring the information available in performance_schema.metadata_locks .

Examples demonstrate where to insert the print calls: during lock acquisition ( MDL_context::acquire_lock ), lock upgrade ( MDL_context::upgrade_shared_lock ), lock downgrade ( MDL_ticket::downgrade_lock ), and lock release. Sample log output shows the printed fields for each stage.

The article then presents practical tests for each MDL lock type, showing the generated log entries for operations such as DELETE , ALTER TABLE , SELECT ... FOR UPDATE , and LOCK TABLES . Compatibility matrices (illustrated with images in the original source) are discussed to explain which lock types block others, highlighting the impact of scope locks (GLOBAL, COMMIT) versus object locks (TABLE).

Finally, the author emphasizes the importance of understanding MDL locks for diagnosing deadlocks and performance issues, and recommends further reading on MySQL replication and metadata locking.