How MySQL InnoDB Locks Work: From MVCC Basics to Deadlock Analysis

MySQL/InnoDB locking analysis is a challenging topic that many colleagues ask about, and I often receive private messages on Weibo requesting help with deadlocks. This article presents a systematic approach to determine what locks any given SQL statement acquires, the associated risks, and how to analyze real‑world deadlock scenarios.

MVCC: Snapshot Read vs Current Read

InnoDB implements Multi‑Version Concurrency Control (MVCC), which allows reads without locking and eliminates read‑write conflicts in OLTP workloads. Snapshot reads return a visible version of a row (possibly historic) without acquiring a lock, while current reads fetch the latest version and lock the row to prevent concurrent modifications.

Snapshot read: simple SELECT statements (with exceptions discussed later) do not lock rows.

Current read: SELECT ... LOCK IN SHARE MODE , SELECT ... FOR UPDATE , INSERT , UPDATE , and DELETE acquire locks; the first acquires a shared (S) lock, the others acquire exclusive (X) locks.

Insert, update, and delete operations are considered current reads because they involve a read phase that locks the target rows before modification.

Cluster Index (聚簇索引)

InnoDB stores the full row in the primary‑key (clustered) index. The primary key therefore serves as the physical location of the record. This article assumes the reader is already familiar with clustered indexes.

2PL: Two‑Phase Locking

Traditional RDBMS use Two‑Phase Locking (2PL), which separates the lock acquisition phase from the lock release phase, ensuring they do not overlap.

Isolation Level

MySQL defines four isolation levels: Read Uncommitted, Read Committed, Repeatable Read, and Serializable. The article focuses on how InnoDB implements these levels rather than the theoretical definitions.

Read Uncommitted – ignored.

Read Committed – current reads lock rows; phantom reads may occur.

Repeatable Read – locks rows and the gaps (GAP locks) between them to prevent phantom reads.

Serializable – all reads become current reads, eliminating MVCC.

A Simple SQL Locking Example

After the background, we examine two representative statements:

SQL1: SELECT * FROM t1 WHERE id = 10; SQL2: DELETE FROM t1 WHERE id = 10; Answering which locks these statements acquire depends on several prerequisites:

Is id the primary key?

What is the current isolation level?

If not a primary key, does id have an index?

If indexed, is it a unique index?

What execution plan does the optimizer choose (index scan vs full table scan)?

We combine these conditions into nine scenarios (RC, RR, and Serializable isolation levels) and analyze the lock behavior for each.

Combination 1: Primary Key + RC

Result: Only the row with id = 10 receives an X lock.

Combination 2: Unique Secondary Index + RC

Result: An X lock on the unique index entry and an X lock on the corresponding clustered‑index row.

Combination 3: Non‑Unique Secondary Index + RC

Result: All rows matching the condition are locked in both the secondary index and the clustered index.

Combination 4: No Index + RC

Result: A full‑table scan locks every row in the clustered index, even those that do not satisfy the predicate (MySQL later releases locks on non‑matching rows).

Combination 5 & 6: Primary Key or Unique Index + RR

Locking behavior is identical to the RC cases: only the matching row(s) receive X locks.

Combination 7: Non‑Unique Index + RR

Result: In addition to the X locks on matching rows, GAP locks are placed on the gaps surrounding the matching range to prevent phantom inserts.

Combination 8: No Index + RR

Result: All rows and all gaps in the clustered index are locked, effectively blocking any concurrent DML.

Combination 9: Serializable

For SELECT statements, Serializable forces current reads with read locks, eliminating MVCC snapshot reads.

A Complex SQL Example

Consider a query that uses the idx_t1_pu index with the following conditions:

Index key: pubtime > 1 AND puptime < 20 Index filter: userid = 'hdc' Table filter:

comment IS NOT NULL

In Repeatable Read, the index‑key range receives GAP locks; rows that fail the index filter may or may not receive X locks depending on whether the server supports Index Condition Pushdown (ICP). Table‑filter conditions always cause X locks on the corresponding clustered rows.

Deadlock Principles and Analysis

Understanding InnoDB locking enables two practical outcomes: writing deadlock‑free SQL and diagnosing live deadlocks. Two example deadlocks are shown: one classic case where two sessions each hold a lock and request the other's lock, and another where a single‑statement per session deadlock occurs due to opposite lock acquisition order on different indexes.

Deadlocks arise when sessions acquire locks in different orders; analyzing each statement's lock order reveals the root cause.

Summary

Mastering MySQL/InnoDB locking requires knowledge of storage structures, concurrency control protocols, isolation levels, execution plans, implementation details such as ICP and semi‑consistent reads, and deadlock analysis techniques.