Skip to content

PATTERN Cited by 2 sources

On-demand Serializable read

Pattern

Run the application's default transactions at a low isolation level (READ COMMITTED or REPEATABLE READ / SnapshotRead) for the contention-free throughput — and upgrade specific reads to Serializable-equivalent behaviour on demand by acquiring explicit row locks. The lever is a locking read: SELECT … FOR UPDATE (exclusive) or SELECT … LOCK IN SHARE MODE / SELECT … FOR SHARE (shared).

Why this is the load-bearing lever

Serializable is "generally considered to be impractical, even for a non-distributed database" — it holds locks longer than necessary and produces frequent deadlocks. But a pure-READ COMMITTED application is unsafe in any case where a read-then-write depends on the read staying stable. The apparent dilemma — "either coarse-grained safety at unacceptable cost, or fine-grained speed with correctness holes" — is resolved by this pattern: the application pays the locking cost only on the specific rows where correctness requires it, not globally. (Source: sources/2026-04-21-planetscale-pitfalls-of-isolation-levels-in-distributed-databases.)

Canonical framing, verbatim from Sugu Sougoumarane's PlanetScale pedagogy post: "The ReadCommitted setting also allows you to upgrade your read by issuing a read or write lock, effectively providing you with the ability to perform on-demand Serializable reads. … This approach gives you the best of both worlds for application transactions that intend to modify data."

Mechanism

Two lever types:

  • SELECT … FOR UPDATE — acquires an exclusive (concepts/shared-lock-vs-exclusive-lock|X) lock on each returned row. Blocks concurrent writers and readers wanting any kind of lock.
  • SELECT … LOCK IN SHARE MODE (MySQL, legacy syntax) / SELECT … FOR SHARE (MySQL 8.0+, SQL-standard) — acquires a shared (S) lock. Blocks concurrent writers; other readers can acquire their own S locks.

Both statements promote a plain MVCC-snapshot read into a transactional primitive. They only have effect inside an explicit transaction (i.e. after BEGIN / START TRANSACTION); on an autocommit statement the lock is released immediately after the SELECT returns.

Shared lock vs exclusive lock — the counter-intuitive rule

Sougoumarane canonicalises a subtle correctness point: for the racing read-then-write case, prefer FOR UPDATE (exclusive) over LOCK IN SHARE MODE (shared).

-- BAD shape (shared lock + write upgrade) — deadlock-prone
BEGIN;
SELECT balance FROM user WHERE id = ? LOCK IN SHARE MODE;
-- two concurrent withdrawals both hold an S lock here…
UPDATE user SET balance = balance - amount WHERE id = ?;
-- … and both try to upgrade to X → deadlock
COMMIT;

-- GOOD shape (exclusive lock upfront) — serialises cleanly
BEGIN;
SELECT balance FROM user WHERE id = ? FOR UPDATE;
-- second transaction waits for first to commit, then reads the fresh value
UPDATE user SET balance = balance - amount WHERE id = ?;
COMMIT;

Canonical quote: "This lock prevents another transaction from obtaining any kind of lock on this row. This approach of pessimistic locking sounds worse at first, but will allow two racing transactions to successfully complete without encountering a deadlock. The second transaction will wait for the first transaction to complete, at which point it will read and lock the row as of the new value."

The stronger lock is the safer choice in this shape.

When to use it

  • Read-then-write on a critical row (account balance, inventory count, reservation counter). Use FOR UPDATE.
  • Consistent multi-row read for reporting where the set must not change mid-read. Use FOR SHARE (cheaper than FOR UPDATE if only readers contend).
  • Upgrading a SnapshotRead transaction. Sougoumarane: "MySQL allows you to 'select… lock in share mode' (read lock). This mode upgrades the read to that of a Serializable transaction." Same mechanism applies under Postgres SI via SELECT … FOR UPDATE.

When not to use it

  • Reference-data reads where concurrent writes are fine to observe (exchange-rate example from Sougoumarane's post — the application doesn't need the rate to stay stable after the read). Plain SELECT under READ COMMITTED or SnapshotRead is the right choice.
  • Analytical / reporting scans across many rows that would hold locks for a long time and starve writers. Use a dedicated read replica or analytical store instead.
  • In MySQL's REPEATABLE READ without a usable index. Per Brian Morrison II (Source: [[sources/2026-04-21-planetscale- mysql-isolation-levels-and-how-they-work]]), a locking read without an index on the WHERE clause locks every scanned row — not just the matching ones — plus gap locks on the missing rows. Missing indexes inflate lock scope, not just query latency. Always verify with EXPLAIN that a locking read has an efficient access plan.

Distributed-database caveat

On a sharded database, FOR UPDATE across multiple shards collides with the [[concepts/distributed-isolation-coupling- cost|distributed isolation coupling cost]]: the lock must either be coordinated via a centralised manager or via concepts/atomic-distributed-transaction|2PC. Sougoumarane's prescription (in the same post) is "avoid distributed transactions by keeping related rows within the same shard." Within a single shard, on-demand Serializable reads remain the canonical upgrade lever.

Relationship to the three-part prescription

This pattern is the tactical mechanism that makes the broader patterns/lowest-isolation-plus-explicit-locks prescription implementable. That prescription is the strategic discipline — "use as few isolation guarantees as you can, upgrade where needed, co-locate data to avoid cross-shard transactions" — and on-demand Serializable reads are the per-transaction tool the application uses to implement the upgrade step.

Seen in

Last updated · 550 distilled / 1,221 read