Skip to main content
← PROBLEMS / ARCHITECTURE
An end-to-end domain learning journey

SQL Isolation Levels:
Preventing Phantom Reads.

A cloud resource-quota and shift-scheduling service running on PostgreSQL 16 (default READ COMMITTED) enforces a rule: 'A team may have at most 3 active on-call engineers per shift, and total allocated GPU quota per project cannot exceed 16 GPUs.' Under concurrent API requests, two transactions both query SELECT SUM(gpus) FROM allocations WHERE project_id = $1, both read 12 GPUs, and both INSERT a new 4-GPU allocation—resulting in 20 GPUs allocated. Worse, re-running the SELECT inside the same transaction suddenly shows the newly committed row (a phantom read). Design the exact isolation, locking, and retry strategy to prevent both phantom reads and multi-row invariant violations.
Start the journey5 stages
Loading diagram…
SQL Isolation Levels: Preventing Phantom Reads architecture sketch diagram
FIRST PRINCIPLES. PRODUCTION SCALE.
The idea to keep with you

A cloud resource-quota and shift-scheduling service running on PostgreSQL 16 (default READ COMMITTED) enforces a rule: 'A team may have at most 3 active on-call engineers per shift, and total allocated GPU quota per project cannot exceed 16 GPUs.' Under concurrent API requests, two transactions both query `SELECT SUM(gpus) FROM allocations WHERE project_id = $1`, both read 12 GPUs, and both `INSERT` a new 4-GPU allocation—resulting in 20 GPUs allocated. Worse, re-running the `SELECT` inside the same transaction suddenly shows the newly committed row (a phantom read). Design the exact isolation, locking, and retry strategy to prevent both phantom reads and multi-row invariant violations.

From patterns to decisions.

A cloud resource-quota and shift-scheduling service running on PostgreSQL 16 (default READ COMMITTED) enforces a rule: 'A team may have at most 3 active on-call engineers per shift, and total allocated GPU quota per project cannot exceed 16 GPUs.' Under concurrent API requests, two transactions both query `SELECT SUM(gpus) FROM allocations WHERE project_id = $1`, both read 12 GPUs, and both `INSERT` a new 4-GPU allocation—resulting in 20 GPUs allocated. Worse, re-running the `SELECT` inside the same transaction suddenly shows the newly committed row (a phantom read). Design the exact isolation, locking, and retry strategy to prevent both phantom reads and multi-row invariant violations.

01
ARCHITECTURE · STAGE 01L4 · FUNDAMENTALS

READ COMMITTED allows phantom reads.

READ COMMITTED allows phantom reads and over-allocation; PostgreSQL REPEATABLE READ prevents phantom reads on re-SELECT but still allows write skew (over-allocation); SERIALIZABLE detects the rw-antidependency and aborts one transaction with 40001.

ChoiceWhat it buysWhat it costs
Trade-off 1SERIALIZABLE requires zero schema changes but shifts contention handling to application-level 40001 retries.

Step 1

Trace Tx A and Tx B snapshots under READ COMMITTED.

Step 2

Trace Tx A and Tx B snapshots under PostgreSQL REPEATABLE READ (Snapshot Isolation).

Step 3

Trace SSI rw-antidependency detection under SERIALIZABLE.

Alternative 1

Use PostgreSQL EXCLUDE USING gist constraints for non-overlapping time ranges.

Failure Mode 1

Without an index on project_id, SSI escalates to relation-level SIREAD locks and aborts unrelated projects.

-- Under PostgreSQL SERIALIZABLE, SSI tracks predicate reads:
BEGIN ISOLATION LEVEL SERIALIZABLE;
SELECT COALESCE(SUM(gpus), 0) FROM allocations WHERE project_id = 42;
-- If concurrent tx also read and inserted for project_id = 42, COMMIT raises SQLSTATE 40001
INSERT INTO allocations (project_id, gpus) VALUES (42, 4);
COMMIT;
02
ARCHITECTURE · STAGE 02L5 · APPLIED DESIGN

Lock the parent projects row.

Lock the parent projects row with FOR UPDATE and lock_timeout=2000ms for single-project quotas, and wrap SERIALIZABLE blocks in a bounded 40001 retry loop.

ChoiceWhat it buysWhat it costs
Trade-off 1Parent-row locking eliminates 40001 aborts completely for project-scoped writes, at the cost of holding one row lock per active transaction.

Step 1

Set SET LOCAL lock_timeout = '2000ms'.

Step 2

Execute SELECT quota_limit FROM projects WHERE id = %s FOR UPDATE.

Step 3

Sum current allocations and insert only if within quota.

Alternative 1

Use pg_advisory_xact_lock(project_id) if no parent projects table exists.

Failure Mode 1

Locking multiple parent rows in inconsistent order across endpoints causes deadlocks (40P01).

import psycopg
from psycopg import errors

def allocate_gpus_locked(conn: psycopg.Connection, project_id: int, requested: int) -> bool:
    with conn.transaction():
        conn.execute("SET LOCAL lock_timeout = '2000ms'")
        row = conn.execute(
            "SELECT quota_limit FROM projects WHERE id = %s FOR UPDATE",
            (project_id,),
        ).fetchone()
        if not row:
            raise ValueError("Project not found")
        quota_limit = row[0]
        used = conn.execute(
            "SELECT COALESCE(SUM(gpus), 0) FROM allocations WHERE project_id = %s",
            (project_id,),
        ).fetchone()[0]
        if used + requested > quota_limit:
            return False
        conn.execute(
            "INSERT INTO allocations (project_id, gpus) VALUES (%s, %s)",
            (project_id, requested),
        )
        return True
03
ARCHITECTURE · STAGE 03L6 · SCALE & TRADE-OFFS

MySQL InnoDB REPEATABLE READ uses.

MySQL InnoDB REPEATABLE READ uses MVCC for plain SELECTs (still allowing write skew) but uses Next-Key Locks (record + gap lock) for SELECT ... FOR UPDATE, blocking concurrent phantom INSERTs into the gap.

ChoiceWhat it buysWhat it costs
Trade-off 1InnoDB gap locks prevent phantoms in REPEATABLE READ, but frequently cause gap-lock deadlocks (Error 1213) under high concurrency.

Step 1

Compare plain SELECT (snapshot read) vs SELECT ... FOR UPDATE (locking read with gap lock) in InnoDB.

Step 2

Note that PostgreSQL has no gap locks—it uses SSI (SERIALIZABLE) or parent/advisory locks instead.

Alternative 1

Use a parent-row lock in both MySQL and PostgreSQL for portable, deadlock-free serialization.

Failure Mode 1

Running plain SELECT without FOR UPDATE in MySQL REPEATABLE READ still suffers from write skew.

-- MySQL 8.0 InnoDB: Next-Key Lock blocks phantom INSERTs into project_id = 42
START TRANSACTION;
SELECT SUM(gpus) FROM allocations WHERE project_id = 42 FOR UPDATE;
INSERT INTO allocations (project_id, gpus) VALUES (42, 4);
COMMIT;
04
ARCHITECTURE · STAGE 04L6 · SCALE & TRADE-OFFS

High contention on optimistic SSI.

High contention on optimistic SSI causes retry storms (livelock). Switching that hot path to pessimistic parent-row FOR UPDATE (or maintaining a materialized used_gpus column on projects) converts retries into short FIFO waits.

ChoiceWhat it buysWhat it costs
Trade-off 1Denormalizing `used_gpus` onto `projects` achieves peak throughput and zero 40001 retries, but requires keeping it in sync on DELETE/release.

Step 1

Maintain a materialized used_gpus integer on projects updated atomically.

Execute single-statement conditional UPDATE

UPDATE projects SET used_gpus = used_gpus + $1 WHERE id = $2 AND used_gpus + $1 <= quota_limit.

Alternative 1

Use parent-row FOR UPDATE with a short lock_timeout.

Failure Mode 1

Infinite or un-jittered 40001 retry loops amplify DB CPU usage by 5x during spikes.

-- Single-statement atomic update eliminates multi-row read/write skew entirely
UPDATE projects
SET used_gpus = used_gpus + 4
WHERE id = 42 AND used_gpus + 4 <= quota_limit
RETURNING used_gpus;
05
ARCHITECTURE · STAGE 05L7 · STAFF / PRINCIPAL

PostgreSQL's EXCLUDE USING gist constraint.

PostgreSQL's EXCLUDE USING gist constraint enforces non-overlapping ranges atomically at the index level even under READ COMMITTED.

ChoiceWhat it buysWhat it costs
Trade-off 1GiST exclusion constraints guarantee zero overlaps at the storage engine level, with slightly higher index write cost than B-tree.

Step 1

Enable btree_gist extension.

Step 2

Add EXCLUDE USING gist (team_id WITH =, tstzrange(start_ts, end_ts) WITH &&).

Alternative 1

Use SERIALIZABLE isolation with a range query check.

Failure Mode 1

Forgetting to map SQLSTATE 23P01 (exclusion_violation) to HTTP 409 Conflict.

CREATE EXTENSION IF NOT EXISTS btree_gist;
ALTER TABLE oncall_shifts
ADD CONSTRAINT no_overlapping_shifts
EXCLUDE USING gist (team_id WITH =, tstzrange(start_ts, end_ts) WITH &&);

Theory & Tips and Tricks

Practical heuristics & interview traps

  • 01Interview anchor: Match isolation to the invariant: use PostgreSQL SERIALIZABLE (with retry on SQLSTATE 40001) or explicit parent-row SELECT ... FOR UPDATE for multi-row business invariants, and understand how PostgreSQL SSI differs from InnoDB Next-Key Locking.
  • 02Trade-off tip: InnoDB gap locks prevent phantoms in REPEATABLE READ, but frequently cause gap-lock deadlocks (Error 1213) under high concurrency.
  • 03Watch out for: Infinite or un-jittered 40001 retry loops amplify DB CPU usage by 5x during spikes.
Behavioral Progression · Sql Isolation Levels And Phantom Reads

Sql Isolation Levels And Phantom Reads — Normal → Load / Fault → Recovery

Send Request
1. Normal Request Ingress
Traffic: 1x baseline · Latency (p99): 4 ms
Process State
2. Core Execution & State Lookup
Traffic: 1x baseline · Latency (p99): 9 ms
Inject 10x Spike
3. Scale Spike / Failure Injection
Traffic: 10x burst · Latency (p99): 185 ms

Match isolation to the invariant: use PostgreSQL SERIALIZABLE (with retry on SQLSTATE 40001) or explicit parent-row SELECT ... FOR UPDATE for multi-row business invariants, and understand how PostgreSQL SSI differs from InnoDB Next-Key Locking.

Synthesis / One connected design

Putting SQL Isolation Levels: Preventing Phantom Reads Together

Match isolation to the invariant: use PostgreSQL SERIALIZABLE (with retry on SQLSTATE 40001) or explicit parent-row SELECT ... FOR UPDATE for multi-row business invariants, and understand how PostgreSQL SSI differs from InnoDB Next-Key Locking.

A candidate architecture, not a universal answer

Putting SQL Isolation Levels: Preventing Phantom Reads Together

Step 1
Step 2
Step 3
  1. Set SET LOCAL lock_timeout = '2000ms'.
  2. Execute SELECT quota_limit FROM projects WHERE id = %s FOR UPDATE.
  3. Sum current allocations and insert only if within quota.
High contention on optimistic SSI causes retry storms (livelock). Switching that hot path to pessimistic parent-row FOR UPDATE (or maintaining a materialized used_gpus column on projects) converts retries into short FIFO waits.
Interview Readiness Check

High contention on optimistic SSI causes retry storms (livelock). Switching that hot path to pessimistic parent-row FOR UPDATE (or maintaining a materialized used_gpus column on projects) converts retries into short FIFO waits.

Walk through bottlenecks, back-of-the-envelope math, and failure recovery.

Practice / Explain the consequences

Make the trade-off yourself.

Practice Check

Test your understanding

Question 1 of 3

When is ACID Transaction Fundamentals the right default for a production team?

Key takeaway

SQL Isolation Levels: Preventing Phantom Reads

Match isolation to the invariant: use PostgreSQL SERIALIZABLE (with retry on SQLSTATE 40001) or explicit parent-row SELECT ... FOR UPDATE for multi-row business invariants, and understand how PostgreSQL SSI differs from InnoDB Next-Key Locking.