Understanding InnoDB Row Locks: Rules, Types, and Real-World Examples

InnoDB uses three kinds of row locks: Record Lock (locks a single row), Gap Lock (locks a range between rows, left‑open right‑open), and Next‑key Lock (locks a range, left‑open right‑closed). Understanding when each lock is added is essential for writing correct SQL and avoiding deadlocks.

Implicit and Explicit Locks

DML statements ( UPDATE, DELETE, INSERT) automatically acquire write locks on the affected rows.

By default, a plain SELECT acquires no lock, except under the SERIALIZABLE isolation level where it gets a row‑level read lock.

Explicit locks can be requested with: SELECT ... FOR UPDATE – adds a row‑level write lock. SELECT ... LOCK IN SHARE MODE – adds a row‑level read lock.

Core Locking Rules

Only the objects accessed during the search are locked.

The basic locking unit is the Next‑key Lock .

Example Table Schema

Consider a table user with columns: id – primary key (unique index) a – ordinary (non‑unique) index b – regular column without an index

Case 1: Unique Index Equality Query

When querying a unique index with WHERE id = ?:

If the row exists, the Next‑key Lock degrades to a Record Lock , locking only that row (e.g., id = 25).

If the row does not exist, the Next‑key Lock degrades to a Gap Lock , locking the surrounding range (e.g., (20, 25)).

Illustrations:

Case 2: Unique Index Range Query

For a range condition like WHERE id >= 20 AND id < 22 on a unique index:

The search starts at id = 20, acquiring a Next‑key Lock (15,20], which then degrades to a Record Lock on id = 20.

The right‑hand side stops at the first non‑matching row ( id = 25), so the lock on the right side becomes a Gap Lock covering (20, 25).

Resulting locks: a Record Lock on id = 20 and a Gap Lock on (20, 25).

Case 3: Non‑Unique Index Equality Query

When using a non‑unique index (e.g., column a) with WHERE a = 16:

If the row exists, InnoDB adds a Next‑key Lock on the range (8,16] and an additional Gap Lock (16,32), resulting in two locks.

If the row does not exist, the Next‑key Lock degrades directly to a Gap Lock covering the range (8,32).

Illustrations:

Case 4: Non‑Unique Index Range Query

For a range condition on a non‑unique index, such as WHERE a >= 16 AND a < 18:

The left side starts at a = 16, acquiring a Next‑key Lock (8,16]. Even though the row exists, it does not degrade to a Record Lock.

The right side continues until the first non‑matching row ( a = 32), adding another Next‑key Lock (16,32]. No degradation to Gap Lock occurs.

The final lock range on index a is the union of both ranges: (8,32].