Module 17: Transactions & Concurrency Control · Lesson 3 of 7
Isolation levels (Lesson 02) largely describe what a transaction sees. Locking describes how the engine keeps writers from stepping on each other, and how a read can deliberately opt into blocking a concurrent write when isolation-level guarantees alone aren’t enough. This lesson covers InnoDB’s row-level locking model directly.
SELECT ... FOR UPDATE and SELECT ... FOR SHARE correctly to
close a read-then-write raceinformation_schema / performance_schemaA transaction that reads a value, computes a new value based on it, then writes the new value back — “read-then-write” — is not safe under concurrency unless something prevents another transaction from doing the same read-modify-write in between your read and your write. Isolation level alone (Lesson 02) does not close this gap for a generic read-then-write pattern; explicit locking is the mechanism that does.
Consider decrementing inventory when an order is placed:
SELECT quantity FROM inventory WHERE product_id = 55, check it’s
> 0 in application code, then UPDATE inventory SET quantity = quantity - 1 .... Two concurrent orders can both read quantity = 1,
both decide “in stock,” and both decrement — selling the same unit of
inventory twice. SELECT ... FOR UPDATE on the read closes this
exact race by taking an exclusive lock at read time, forcing the
second transaction to wait until the first commits (and sees the now-updated quantity) before it can proceed. (The inventory table is illustrative and not part of 00_Schema; the companion `.sq[…]
| Lock Type | Taken By | Blocks Other… |
|---|---|---|
| Shared (S) | Plain locking read: SELECT ... FOR SHARE |
…exclusive locks on the same row (other reads with S are fine) |
| Exclusive (X) | UPDATE, DELETE, INSERT, SELECT ... FOR UPDATE |
…both shared AND exclusive locks on the same row |
-- Exclusive lock, held until COMMIT/ROLLBACK:
START TRANSACTION;
SELECT quantity FROM inventory WHERE product_id = 55 FOR UPDATE;
-- any other session's UPDATE, DELETE, or SELECT ... FOR UPDATE
-- against product_id = 55 now BLOCKS until this transaction ends
UPDATE inventory SET quantity = quantity - 1 WHERE product_id = 55;
COMMIT; -- lock released here
-- Shared lock — lets other readers proceed, blocks writers:
START TRANSACTION;
SELECT quantity FROM inventory WHERE product_id = 55 FOR SHARE;
-- another SELECT ... FOR SHARE can proceed concurrently;
-- an UPDATE from another session blocks until this COMMITs
COMMIT;
InnoDB locks more than the exact rows a query touches when the query involves a range, an equality lookup on a non-unique index, or a lookup on a value that doesn’t exist yet — it also locks the gap between index records, to prevent a phantom insert into that gap (Lesson 02, §6 footnote).
START TRANSACTION;
SELECT * FROM employes WHERE hire_date BETWEEN '2023-01-01' AND '2023-12-31'
FOR UPDATE;
-- This takes next-key locks covering the matched rows AND the gaps
-- between them within that date range, under REPEATABLE READ —
-- another session's INSERT of a NEW employee hired '2023-06-15'
-- BLOCKS, even though no such row existed for this query to have
-- locked directly. This is what makes the range phantom-safe for
-- this locking read.
COMMIT;
Setup this example depends on. The range a locking read locks depends
on the index the optimizer uses and on the isolation level. With an index
on hire_date, InnoDB takes next-key locks on the scanned index entries
and the gap after the last one; without one it scans the clustered index
and can lock far more rows and gaps. The companion .sql creates a
temporary index, confirms the plan with EXPLAIN, and drops it afterward
(see Module 15 — Indexes for how the optimizer
chooses indexes). This requires REPEATABLE READ: under READ COMMITTED
InnoDB generally does not take gap locks for searches and index scans, so
the concurrent insert would not block.
A next-key lock is InnoDB’s combination of a record lock (on the
row itself) plus a gap lock (on the space before it in index order).
This is InnoDB-specific machinery, not a general SQL-standard concept
— it exists because InnoDB delivers REPEATABLE READ’s phantom
protection for locking reads through locking rather than pure MVCC.
SELECT @@innodb_lock_wait_timeout; -- default: 50 seconds
-- A session blocked longer than this on a row lock gets:
-- ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
A lock-wait-timeout error is not a deadlock (Lesson 04) — it’s one transaction simply waiting too long for another to release a lock voluntarily. The waiting transaction’s statement fails; earlier statements in that same transaction remain pending, uncommitted.
SELECT ... FOR UPDATE is the correct tool for read-then-write
races when you need a locking read. In InnoDB a typical plain
(non-locking) consistent SELECT reads from a snapshot and generally does not acquire row-level locks; however, behavior can differ under SERIALIZABLE or engine-specific settings that cause reads to take locks. Distinguish ordinary consistent snapshot reads from locking reads (SELECT ... FOR UPDATE, SELECT ... FOR SHARE) and from locking statements (UPDATE, DELETE), which do acquire locks and participate in lock contention.UPDATE inventory SET quantity = quantity - 1 WHERE product_id = 55
AND quantity > 0 — over a separate locking read plus a write. A
single, properly-constructed SQL statement can make that specific
check-and-update atomic at the storage-engine level and often avoids a
separate read-then-write race. However, that atomicity applies only to
the expressed check-and-update; it does not automatically enforce
broader multi-row or multi-statement business invariants, which still
require careful transactional design or explicit locking.emp_id first).
Inconsistent ordering across concurrent transactions is the single
most common cause of deadlocks (Lesson 04).COMMIT/ROLLBACK, not until the statement
that took them finishes — a long transaction holding a FOR UPDATE
lock blocks others for its entire remaining duration, not just for
the moment of that one statement.MySQL: Row-level locking is InnoDB-specific; MyISAM uses table-level locking and has no row-level locks, so FOR UPDATE gives no row-level protection there. Gap locks and next-key
locks are also InnoDB-specific concepts.
PostgreSQL: Also supports SELECT ... FOR UPDATE / FOR SHARE
with very similar semantics, plus FOR NO KEY UPDATE and FOR KEY
SHARE for finer-grained control not present in MySQL. Postgres does
not use gap locks the way InnoDB does — it achieves phantom
protection differently (via its own MVCC snapshot mechanics and, at
SERIALIZABLE, predicate locking).
SQL Server: Supports table hints like WITH (UPDLOCK),
WITH (HOLDLOCK), WITH (ROWLOCK) for comparable fine-grained
locking control, syntactically distinct from MySQL/Postgres’s
FOR UPDATE clause.
SELECT then a separate UPDATE for a
read-then-write, believing the isolation level alone protects it —
it doesn’t; see Lesson 02, §9 (Common Mistake #2).FOR UPDATE lock across a slow external call —
identical anti-pattern to Lesson 01/06’s “don’t hold a transaction
open across a network call,” but worse here because the lock is
explicit and exclusive, actively blocking others the entire time.FOR UPDATE locks only the exact rows matched without
accounting for gap/next-key locking on range or non-unique-index
predicates, then being surprised an unrelated insert blocks.SELECT ... FOR
UPDATE on a primary key value with no matching row): InnoDB still
takes a gap lock on the space where that value would sort, to
prevent a concurrent insert of exactly that value — relevant for
patterns like “check if a unique code is taken, then insert it.”WHERE clause can lock more than expected.SELECT * FROM performance_schema.data_lock_waits;
SELECT * FROM performance_schema.data_locks;
SELECT trx_id, trx_started, trx_mysql_thread_id, trx_query
FROM information_schema.INNODB_TRX
ORDER BY trx_started;
Lock contention rises with concurrent write volume against the same
rows/ranges. FOR UPDATE on a hot row (e.g., a shared counter) under
high concurrency serializes every transaction that touches it,
regardless of how brief each individual transaction is — the
contention is structural to the access pattern, not something a
faster query fixes. Illustrative only: this is exactly where patterns
like sharding a counter across multiple rows, or using an atomic
single-statement UPDATE ... SET x = x + 1 instead of a
lock-then-read-then-write sequence, become relevant — measure real
contention before redesigning around it.
Oracle: Uses SELECT ... FOR UPDATE with very similar semantics
to MySQL/Postgres; Oracle’s multiversion read consistency means
non-locking reads never block on writer locks, similar in spirit to
InnoDB’s MVCC reads.
SQLite: Locking model is coarser-grained (database- or table-level
depending on journal mode), not row-level — FOR UPDATE syntax is
not meaningfully applicable the way it is in MySQL/Postgres/Oracle.
DuckDB: Not designed around fine-grained row-level locking for high-concurrency OLTP write patterns; this lesson’s locking-race scenarios are not DuckDB’s primary use case.
Shared locks let concurrent readers coexist while blocking writers;
exclusive locks block everyone. SELECT ... FOR UPDATE is how you
convert a read into one that participates in this locking model,
closing read-then-write races that isolation level alone doesn’t
address. InnoDB additionally uses gap and next-key locks to make
locking reads phantom-safe on ranges and non-unique indexes. Locks are
held for the full transaction duration — transaction length (Lesson
01) and lock duration are the same lever.
SELECT ... FOR UPDATE, once as a single
atomic UPDATE statement. Explain which is preferable and why.SELECT ... FOR UPDATE on
emp_id = 12 blocking a concurrent UPDATE on the same row from
a second session until the first session commits.SELECT ... FOR UPDATE WHERE dept_id = 4 (a
non-unique index) takes, and why a concurrent INSERT of a new
employee into dept_id = 4 can block even though that row didn’t
exist when the FOR UPDATE ran.(Some challenges are scaffolded in this lesson’s .sql file. The cross-lesson problem set, with full solutions, is in PRACTICE_PROBLEMS.md and SOLUTIONS.sql.)
Previous: Lesson 02 — Isolation Levels & Read Phenomena · Next: Lesson 04 — Deadlocks