Master MySQL InnoDB Locks: How SELECT…FOR UPDATE Behaves

In a previous article we introduced InnoDB's common lock mechanisms; here we focus on how the SELECT ... FOR UPDATE statement influences lock scope.

Common Lock Types

InnoDB's default transaction isolation level is Repeatable Read (RR), which this discussion assumes.

1. Record Locks lock a single row, enabling concurrent access to different rows.

Record locks usually appear when querying by primary key or unique index.

<code>select * from table where id=5 for update;</code>

This locks the index record with id=5 , preventing other transactions from inserting, updating, or deleting that row.

<code>select * from table where id=5;</code>

The same query without FOR UPDATE performs a snapshot read and does not acquire a lock.

2. Gap Locks lock a range between index records (or before the first/after the last record) to prevent phantom reads.

<code># Table structure
users (Id PK, Name, Company);
# Sample rows
5, Gates, Microsoft
7, Bezos, Amazon
11, Jobs, Apple
14, Elison, Oracle</code>

Executing:

<code>select * from users where id between 7 and 13 for update;</code>

locks the interval (7,13] so no other transaction can insert a row with id=8 , avoiding phantom data. If the isolation level is lowered to Read Committed, gap locks are disabled.

3. Next‑Key Locks combine record and gap locks for a range, using a left‑open, right‑closed interval to prevent phantom reads.

<code># Left‑open, right‑closed example
(-∞,1]
(1,7]
(7,+∞)</code>

In RR mode, InnoDB automatically uses next‑key locks for range queries.

<code>CREATE TABLE users (Id INT PRIMARY KEY, Name VARCHAR(255) NOT NULL, Company VARCHAR(255) NOT NULL);
INSERT INTO users (id, name, company) VALUES (1,'Alice','ali');
INSERT INTO users (id, name, company) VALUES (2,'Brand','tencent');
START TRANSACTION;
SELECT * FROM users WHERE id > 1 FOR UPDATE;
-- Another transaction trying to insert will block until the first commits
START TRANSACTION;
INSERT INTO users (id, name, age) VALUES (4,'David',30);
COMMIT;
COMMIT;</code>

Different SELECT Lock Rules

3.1 Primary‑Key Lookup

If the row exists, a record lock is taken; if it does not exist, a gap lock on the surrounding interval is taken.

<code># Existing record (id=5)
SELECT * FROM userinfo WHERE id=5 FOR UPDATE;
# Non‑existent record (id=6)
SELECT * FROM userinfo WHERE id=6 FOR UPDATE;</code>

3.2 Unique‑Index Lookup

Behaves the same as primary‑key lookup because the index uniquely identifies a value.

3.3 Ordinary Index Lookup

Always acquires a gap lock, regardless of whether the exact value exists.

<code># Existing record (usercode=24)
SELECT * FROM userinfo WHERE usercode=24 FOR UPDATE;
# Non‑existent record (usercode=25)
SELECT * FROM userinfo WHERE usercode=25 FOR UPDATE;</code>

3.4 Range Scan on an Index

A range condition (e.g., id > 4 ) creates gap locks covering the matching intervals.

<code>SELECT * FROM userinfo WHERE id > 4 FOR UPDATE;</code>

3.5 Full Table Scan (No Index)

Results in a table lock, blocking all modifications during the scan.

Summary

Transaction isolation level is Repeatable Read (RR).

Primary‑key or unique‑index queries lock rows when the value exists; otherwise they trigger gap locks.

Ordinary index queries always acquire gap locks.

Range queries on indexed columns generate gap locks.

Full‑table scans without an index produce a table lock.