Understanding MySQL Lock Mechanisms: Metadata Locks, InnoDB Locks, and Source Code Insights

The talk, originally delivered by Ctrip senior database engineer Jiang Yuxiang, introduces MySQL's unique two‑layer architecture that separates the server layer (handling networking, parsing, and optimization) from the storage‑engine layer (e.g., InnoDB, MEMORY), highlighting the flexibility and challenges this design brings to transaction processing.

It then explains MySQL metadata locks (MDL), which protect database objects such as schemas, tables, procedures, functions, and triggers at the server level. Various MDL types (global, table, schema, etc.) and their acquisition/release points (STATEMENT, TRANSACTION, EXPLICIT) are described, along with the relationship hierarchy among locks.

The article points to key source‑code files (e.g., MDL.h , MDL.cc ) that implement MDL handling, advising readers to set breakpoints in the low‑level lock‑management functions when debugging.

A concrete production incident is presented: a "flush tables with read lock" operation caused a global read lock that blocked many connections. By tracing the call stack and setting breakpoints in the relevant functions, the root cause was identified and resolved.

Next, the discussion shifts to InnoDB locks, covering table, row, and gap locks, as well as lock modes (IS, IX, S, X, auto‑increment). It reviews transaction isolation levels (READ UNCOMMITTED, READ COMMITTED, REPEATABLE READ, SERIALIZABLE) and explains how gap locks prevent phantom reads under REPEATABLE READ.

Practical debugging tips are offered, including how to locate lock‑related code in files such as lock0lock.cc , trx0trx.cc , and trx0rec.cc , and how Ctrip enhanced lock‑information output for better visualization and root‑cause analysis.

Finally, the speaker shares learning strategies for reading database source code: understand the functional concepts first, study relevant files in isolation, observe how others annotate code, and use the insights to improve operational reliability.