Skip to content

CONCEPT Cited by 1 source

Locking read (MySQL)

A locking read in MySQL is a SELECT statement that acquires row-level locks on the rows it returns, promoting it from a plain non-locking read to a transactional primitive that blocks concurrent writers (and optionally readers). The two clauses that produce locking reads are SELECT … FOR SHARE (acquires a shared lock on each row) and SELECT … FOR UPDATE (acquires an exclusive lock).

Why locking reads exist

Under MySQL isolation levels weaker than SERIALIZABLE, plain SELECT statements don't acquire row-level locks — they rely on MVCC snapshots to give a consistent view. This is great for read-heavy workloads, but it means a SELECT followed by an UPDATE in the same transaction has a race window: another transaction can modify the row in between, and the UPDATE sees the stale value in its effect computation.

Canonical example from PlanetScale's Brian Morrison II (Source: sources/2026-04-21-planetscale-mysql-isolation-levels-and-how-they-work):

-- Without a locking read, this is racy:
START TRANSACTION;
SELECT cost FROM products WHERE id = 20;  -- reads 1000
-- Another transaction commits `UPDATE products SET cost = 1200 WHERE id = 20;`
UPDATE products SET cost = cost - 200 WHERE id = 20;
-- Applied to the fresh 1200, not the 1000 we read: final cost = 1000, not 800.
COMMIT;

The fix: convert the read into a locking read so the row is exclusively held between the SELECT and the UPDATE:

START TRANSACTION;
SELECT cost FROM products WHERE id = 20 FOR UPDATE;
UPDATE products SET cost = 800 WHERE id = 20;
COMMIT;

Verbatim framing from the post: "This approach would prevent another transaction from reading the data before it was updated." Between the SELECT FOR UPDATE and the COMMIT, no other transaction can SELECT FOR UPDATE, UPDATE, or DELETE the row.

FOR SHARE semantics

SELECT … FOR SHARE acquires a shared lock on each returned row:

  • Other readers can acquire FOR SHARE too (shared locks are reader-compatible) — multiple readers coordinate.
  • Other writers (UPDATE / DELETE / SELECT FOR UPDATE) are blocked until the shared lock holder commits.
  • The shared lock is held until transaction commit or rollback.

Use case: a transaction wants to read rows and know they won't change while the transaction is in flight, but doesn't itself intend to modify them and is willing to coexist with other readers with the same need.

Legacy syntax: SELECT … LOCK IN SHARE MODE (pre-MySQL 8).

FOR UPDATE semantics

SELECT … FOR UPDATE acquires an exclusive lock on each returned row:

  • All other readers attempting FOR SHARE or FOR UPDATE are blocked.
  • All other writers (UPDATE / DELETE) are blocked.
  • Non-locking plain SELECTs still succeed under MVCC (at READ COMMITTED / REPEATABLE READ) — they see the snapshot, not the lock.
  • The exclusive lock is held until transaction commit or rollback.

Use case: read-then-modify workflows where the transaction will definitely write the row (or needs to block concurrent writes to maintain correctness even if it doesn't end up writing). The canonical pattern: SELECT … FOR UPDATE, decide based on read values, UPDATE, COMMIT.

Gap-lock interaction under REPEATABLE READ

Under MySQL's default REPEATABLE READ isolation level, locking reads with range predicates take next-key locks (record + gap before the record), blocking not just modifications to existing rows but also inserts into the gap:

START TRANSACTION;
SELECT * FROM products WHERE release_year = 1999 FOR UPDATE;
-- All existing rows with release_year = 1999 are X-locked.
-- Plus: the gap in the release_year index where new 1999 rows
--       would go is locked. INSERTs of new 1999 rows block.
UPDATE products SET cost = 800 WHERE release_year = 1999;
COMMIT;

This is how InnoDB makes REPEATABLE READ locking reads phantom-read-free.

Under READ COMMITTED, gap locks are largely disabled for performance — locking reads still work but only protect existing matching rows, not the insert-space around them.

Index coverage and lock scope (the subtle part)

If a locking read under REPEATABLE READ uses a suitable index matching the WHERE predicate, InnoDB locks only the matching rows (plus their gaps for the next-key lock variant).

If the locking read has no usable index, InnoDB performs a table scan and:

  • Locks every row it scans, whether or not the row matches the WHERE predicate.
  • Takes gap locks to prevent inserts that would alter the predicate's result if re-run.

Canonical datum from the article: "If an index can be used based on the where condition, MySQL will only lock the necessary rows that match the query. If an index is NOT used and the table is scanned, MySQL will lock all of the rows it reads regardless if they match the where, as well as perform gap locking to prevent inserts that may alter the data if the query is run multiple times."

Operational implication: missing indexes on columns used in FOR UPDATE / FOR SHARE predicates cause lock scope to balloon to effectively-the-whole-table. This is one of the sharpest reasons to index every column used in locking-read WHERE clauses — the downside isn't latency, it's concurrency collapse.

Common use cases

Pessimistic concurrency control for read-modify-write: The classic "load the row, check state, update state" flow where state transitions must be atomic relative to concurrent transactions. SELECT FOR UPDATE forces serialisation on the row.

Enforcing uniqueness as check- then-insert: Lock the parent / sentinel row, re-read the unique-key index, insert if absent. See concepts/check-then-act-race for the race class without locking reads.

Queue-like patterns (SELECT … FOR UPDATE SKIP LOCKED): Grab one of N pending rows without blocking behind other workers — see concepts/select-for-update-skip-locked.

Transactional read-your-own-writes without relying on MVCC snapshot timing: FOR SHARE can read current committed state plus prevent concurrent modifications for the transaction's lifetime, without upgrading to a full exclusive lock.

Distributed-coordination primitives (Vitess Consistent Lookup Vindex): Vitess's Consistent Lookup Vindex issues SELECT … FOR UPDATE on both the user- table row and the lookup-table row across two MySQL connections per DML, using the row locks as the cross-shard coordination primitive for lookup-vindex consistency. See patterns/ordered-commit-without-2pc.

Anti-patterns

Locking rows without a tight transaction scope: SELECT … FOR UPDATE outside a transaction block does nothing useful — the lock is released when the implicit single-statement transaction commits. Always wrap in START TRANSACTION / COMMIT.

Locking rows via wide predicates without indexes: leads to whole-table locks under REPEATABLE READ — see index coverage above.

Holding locking reads across application think-time: don't issue FOR UPDATE and then wait for user input. The row stays locked for the entire wait, serialising other transactions.

Using FOR UPDATE when FOR SHARE would do: if the transaction only needs the row not to change but doesn't need exclusive access for writing, FOR SHARE is reader-compatible and produces less contention.

Seen in

  • sources/2026-04-21-planetscale-mysql-isolation-levels-and-how-they-work — Brian Morrison II's PlanetScale pedagogical post canonicalises FOR SHARE (shared lock) and FOR UPDATE (exclusive lock) as the two developer-facing clauses for upgrading a plain SELECT into a locking read. Worked example: fixing the dirty-read example via SELECT * FROM products WHERE id = 20 FOR UPDATE; before the UPDATE. Canonical index-scope datum: without a usable index, a locking read under REPEATABLE READ locks every row scanned plus gap-locks to prevent phantom-affecting inserts.
Last updated · 378 distilled / 1,213 read