In‑Depth Analysis of MySQL/InnoDB Locking Mechanisms

Background : The article starts by stating that MySQL/InnoDB locking is a difficult topic and many developers ask about deadlocks, prompting a deep dive into how any SQL statement acquires locks.

MVCC : MySQL InnoDB implements Multi‑Version Concurrency Control (MVCC). MVCC allows reads without locks, improving concurrency for read‑heavy OLTP workloads.

Snapshot read vs. current read : A snapshot read (e.g., a simple SELECT * FROM table WHERE ?) reads a visible version without acquiring a lock, while a current read (e.g., SELECT * FROM table WHERE ? LOCK IN SHARE MODE or SELECT * FROM table WHERE ? FOR UPDATE, INSERT, UPDATE, DELETE) reads the latest version and locks the rows (S‑lock for shared reads, X‑lock for writes).

Cluster index : InnoDB stores the full row in the primary‑key (cluster) index, so accessing a row always involves the primary‑key index. The article assumes the reader is already familiar with this concept.

Two‑Phase Locking (2PL) : Lock acquisition and release are divided into two non‑overlapping phases. The article includes a diagram illustrating the lock phase and unlock phase.

Transaction isolation levels : The four standard levels (Read Uncommitted, Read Committed, Repeatable Read, Serializable) are introduced, with a focus on how InnoDB implements them, especially the default REPEATABLE READ.

Lock‑behaviour matrix : Nine combinations of index type (primary key, unique secondary, non‑unique secondary, no index) and isolation level (RC, RR, Serializable) are examined. For each combination the article explains which locks are taken (row‑level X‑locks, GAP/next‑key locks, full‑table X‑locks) and why, highlighting differences such as GAP locks appearing only for non‑unique indexes under REPEATABLE READ.

Complex SQL example : A more elaborate query that uses an index range (pubtime > 1 AND puptime < 20) plus an index filter (userid = 'hdc') and a table filter (comment IS NOT NULL) is analyzed. The summary shows that the index range acquires GAP locks, the index filter may or may not acquire row X‑locks depending on ICP support, and the table filter always forces X‑locks on the clustered index rows.

Deadlock analysis : Two deadlock scenarios are presented – one with two sessions each holding two locks, and another with a single statement per session. The root cause is identified as inconsistent lock acquisition order across sessions, and the article explains how to use the previously described lock rules to predict and avoid such deadlocks.

Conclusion : Mastering MySQL/InnoDB locking requires understanding storage formats, concurrency control protocols, 2PL, isolation levels, execution plans, index condition push‑down, semi‑consistent reads, and deadlock detection. With this knowledge, developers can write lock‑safe SQL and troubleshoot production deadlocks.