Locks - Row Lock vs Gap Lock

InnoDB uses row locks to protect individual rows and gap locks to prevent phantom reads by locking "gaps" between index entries. Under REPEATABLE READ, range scans and non-unique conditions can acquire gap locks (and next-key locks). This article explains when each is used and how to avoid unnecessary blocking.

Overview

• Row lock: Locks a single row (by primary key or unique index). Other transactions cannot modify that row until the lock is released.
• Gap lock: Locks the interval between index values; no row need exist in the gap. Used to prevent inserts that would become visible in a repeatable read (phantom read prevention).
• Next-key lock: In InnoDB REPEATABLE READ, a "next-key lock" = gap lock on the gap before an index record + row lock on the record. So a range lock is implemented as next-key locks on the range.

Example

Example 1: Row lock only (unique index equality)

SQLCopy
-- Session A
START TRANSACTION;
SELECT * FROM orders WHERE id = 5 FOR UPDATE;   -- row lock on id=5 only

-- Session B: OK (different row)
UPDATE orders SET status = 'done' WHERE id = 6;

-- Session B: blocked (same row)
UPDATE orders SET status = 'done' WHERE id = 5;

• WHERE id = 5 on a unique index locks only that row; no gap lock.

Example 2: Gap lock (range or non-unique)

SQLCopy
-- Session A
START TRANSACTION;
SELECT * FROM orders WHERE user_id = 100 FOR UPDATE;
-- If there is no unique index on user_id, InnoDB may lock the index range
-- and the gap: no one can INSERT a new row with user_id = 100 until A commits.

-- Session B: blocked
INSERT INTO orders (user_id, ...) VALUES (100, ...);

• With a non-unique condition or range, InnoDB uses gap locks so that a new row matching the same condition cannot be inserted (no phantom read).

Example 3: When gap locks are not taken

• Unique index + equality (e.g. WHERE id = 5): only row lock.
• READ COMMITTED: InnoDB often does not use gap locks for range scans (only row locks), so phantoms can occur but blocking is reduced.

Core Mechanism / Behavior

• Lock mode: FOR UPDATE acquires exclusive row (and possibly next-key) locks; LOCK IN SHARE MODE acquires shared locks. Gap locks are always exclusive and block inserts into the gap.
• Index used: Locks are taken on the index used for the query. If the query uses a secondary index, the clustered index row is also locked (and possibly gaps on the secondary index).
• Gap lock scope: The gap before the first matching record, the gaps between matches, and the gap after the last match (up to "supremum"). Inserts that fall in these gaps are blocked.

Lock type	What it blocks	Typical use
Row lock	Update/delete of that row	Unique equality
Gap lock	Insert into the gap	Range / non-unique under RR
Next-key	Row + gap before	Default range lock in RR

Key Rules

• Prefer unique index equality when you only need to lock one row; that avoids gap locks and reduces blocking.
• Under REPEATABLE READ, avoid broad range scans or non-unique conditions in locking reads if you want fewer gap locks; or use READ COMMITTED if phantoms are acceptable.
• Use EXPLAIN and lock monitoring (e.g. performance_schema.data_locks) to see which rows and gaps are locked when debugging blocking.

What's Next

See Transactions - Isolation & Common Anomalies for phantom read and isolation levels. See MVCC Explained for how reads see snapshots without holding row locks.