Unlocking MySQL: Deep Dive into Metadata and InnoDB Lock Mechanisms

Jiang Yuxiang, a senior database engineer, shares an in‑depth tutorial on MySQL lock mechanisms, emphasizing the value of reading source code for both operations and development.

He first explains MySQL's unique two‑layer architecture, separating the server layer (handling networking, parsing, and optimization) from the storage‑engine layer (e.g., InnoDB, MEMORY), which provides flexibility but also introduces transaction‑related complexities.

The discussion then moves to metadata locks (MDL) that protect database objects such as schemas, tables, procedures, functions, and triggers at the server level, and to InnoDB's own lock implementation that handles row‑level concurrency.

He outlines the three lock‑release categories—STATEMENT, TRANSACTION, and EXPLICIT—and shows how MDL and InnoDB locks are acquired and released, illustrating the process with stack traces and breakpoint locations in files such as MDL.h and MDL.cc.

Real‑world examples demonstrate how a global read lock from FLUSH TABLES WITH READ LOCK can block other transactions, and how visualizing lock stacks helps pinpoint the root cause of deadlocks.

The talk also covers transaction isolation levels (dirty read, non‑repeatable read, phantom read) and how InnoDB implements gap locks to prevent phantom rows under the REPEATABLE READ level.

Finally, Jiang shares practical tips for studying MySQL source code: understand the database’s functional layers first, follow the flow from high‑level features to low‑level functions (e.g., in trx0trx.cc, trx0sys, trx0rec.cc), and use visualized lock information to improve operational debugging.