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.
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.
| Choice | What it buys | What it costs |
|---|---|---|
| Trade-off 1 | SERIALIZABLE 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;
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.
| Choice | What it buys | What it costs |
|---|---|---|
| Trade-off 1 | Parent-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
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.
| Choice | What it buys | What it costs |
|---|---|---|
| Trade-off 1 | InnoDB 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;
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.
| Choice | What it buys | What it costs |
|---|---|---|
| Trade-off 1 | Denormalizing `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;
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.
| Choice | What it buys | What it costs |
|---|---|---|
| Trade-off 1 | GiST 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.
Sql Isolation Levels And Phantom Reads — Normal → Load / Fault → Recovery
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.
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.
Putting SQL Isolation Levels: Preventing Phantom Reads Together
- Set SET LOCAL lock_timeout = '2000ms'.
- Execute SELECT quota_limit FROM projects WHERE id = %s FOR UPDATE.
- Sum current allocations and insert only if within quota.
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.
Make the trade-off yourself.
Practice Check
Test your understanding
When is ACID Transaction Fundamentals the right default for a production team?
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.
