How Does MySQL InnoDB Lock Rows? Deep Dive into MVCC, 2PL, and Deadlocks

Background

Locking in MySQL/InnoDB is a challenging topic that many developers encounter when troubleshooting deadlocks or answering lock‑related questions. This article presents a systematic approach to determine which locks a given SQL statement will acquire, the associated risks, and how to analyze real‑world deadlock cases.

MVCC: Snapshot Read vs. Current Read

InnoDB uses Multi‑Version Concurrency Control (MVCC) so that reads generally do not acquire locks. A snapshot read returns a visible version of a row without locking, while a current read locks the latest version of the row to prevent concurrent modifications.

Typical snapshot reads are simple SELECT statements (with some exceptions). Current reads include SELECT ... FOR UPDATE, SELECT ... LOCK IN SHARE MODE, INSERT, UPDATE, and DELETE, which acquire shared (S) or exclusive (X) locks.

Two‑Phase Locking (2PL)

InnoDB follows the two‑phase locking protocol: a lock‑acquisition phase followed by a lock‑release phase, which never overlap. The diagram below illustrates the separation of these phases.

Isolation Levels

MySQL supports four isolation levels: Read Uncommitted, Read Committed (RC), Repeatable Read (RR), and Serializable. This article focuses on how each level influences lock behavior for the same SQL statements.

Simple SQL Lock Analysis

Consider two statements:

SELECT * FROM t1 WHERE id = 10;

DELETE FROM t1 WHERE id = 10;

Answering which locks they acquire depends on several prerequisites:

Is id a primary key?

What is the current isolation level?

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

If indexed, is the index unique?

What execution plan does MySQL choose (index scan vs. full table scan)?

Combination Scenarios

The article enumerates nine combinations of index type and isolation level, analyzing lock acquisition for each. Below are the key conclusions.

Combination 1: Primary Key + RC

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

Combination 2: Unique Secondary Index + RC

The delete locks the matching entry in the unique index and the corresponding row in the clustered (primary) index.

Combination 3: Non‑Unique Index + RC

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

Combination 4: No Index + RC

A full‑table scan is performed; initially every row is locked, but MySQL may release locks for rows that do not satisfy the condition (semi‑consistent read).

Combinations 5‑6 (RR with Primary Key or Unique Index)

Lock behavior mirrors RC cases; no GAP locks are needed because the key is unique.

Combination 7: Non‑Unique Index + RR

In addition to X locks on matching rows, GAP locks are placed on the gaps surrounding the key range to prevent phantom reads.

Combination 8: No Index + RR

All rows in the clustered index receive X locks, and every gap receives a GAP lock, effectively locking the whole table.

Combination 9: Serializable

Reads become current reads and acquire S locks; the delete behavior is the same as RR.

Complex SQL Example

A more elaborate query that mixes range conditions, index filters, and table filters is analyzed. The lock strategy includes:

GAP locks on the index‑key range.

Conditional X locks on rows that fail the index filter (depending on MySQL version and ICP support).

X locks on rows that fail the table filter.

Deadlock Principles and Analysis

Understanding lock acquisition order is essential for preventing deadlocks. Two illustrative deadlock cases are shown:

Deadlocks arise when sessions acquire locks in different orders. By applying the lock‑analysis method described earlier, one can predict and avoid such conflicts.

Summary

Mastering MySQL/InnoDB locking requires knowledge of storage formats, concurrency control protocols, two‑phase locking, isolation levels, execution plans, and practical details like Index Condition Pushdown and semi‑consistent reads. With this foundation, developers can write lock‑safe SQL and diagnose live deadlocks effectively.