High-level design
Part 2 of 3 · SlotWiseSlotWise - SQLAlchemy Transaction Fix & Tests
Partial unique index on active reservations, a transactional reserve that maps IntegrityError to 409, optional SELECT FOR UPDATE on the slot row, and a regression test that spawns concurrent reservers.
- 1Gist
- 2Maps
- 3Q&A
- 4Sandbox
Voice readout needs Web Speech Synthesis in this browser.
Question ladder
L1
What is unique?
Answer
slot_id, and only for rows whose status is active.
L2
What is not unique?
Answer
Cancelled rows, and two users are not part of the key.
L3
What does with_for_update do?
Answer
Locks the slot row for this transaction. It is optional next to the index.
L4
What becomes 409?
Answer
IntegrityError after rollback, raised as SLOT_TAKEN.
L5
What becomes a missing slot?
Answer
session.get returns None and the code raises SLOT_NOT_FOUND.
L6
What does the thread test count?
Answer
One 201 and nineteen 409s for twenty workers on one slot.
L7
What breaks if commit is outside the request?
Answer
The FOR UPDATE lock ends before the insert it was meant to protect.
Failure modes
Commit outside the request
The row lock is released. The insert races again.
Bare Exception handler
A missing table or a bug becomes a fake conflict.
Unique index without the status predicate
A cancelled reservation blocks the slot forever.
Misconceptions
session.add is the commit.
The unique index is checked at commit or flush. The race lives until then.
FOR UPDATE replaces the index.
Any code path that inserts without taking the lock still needs the constraint.
20 threads in a unit test need a mock lock.
They need a real database that enforces the index.
Interviewer traps
Catch Exception and return 500.
Map IntegrityError to 409 and let the rest surface.
Forget rollback.
The session stays in a failed transaction and the next call is nonsense.
Design scenario
Same prompt for every reader.
Requirements
Partial unique index. One transaction. IntegrityError to 409. A regression test with threads.
Traffic / scale
Twenty clients, one slot, a local Postgres.
Latency
The loser fails at the constraint. It does not spin.
Consistency
One active reservation per slot after commit.
Availability
Conflicts are 409. A missing slot is not a 500.
Failure assumptions
- Two sessions flush an active row for the same slot.
- A cancelled row already exists for that slot.
Constraints
- Do not commit outside the request scope.
- Do not catch Exception around the insert.
Prompt
Implement reserve so one slot has one active row under 20 concurrent callers.
API
Which status codes does the thread test expect?
Data
What is the predicate on the unique index?
Architecture
When is the slot row locked relative to the insert?
What rejects the second insert
Prefer
Partial unique index, then 409
The loser is an integrity failure, not a second 201.
- The predicate is status = active.
- Rollback happens before the error leaves the function.
- FOR UPDATE is optional on the slot row.
Alternative
Check in Python, then insert
That is the incident. Two sessions both see zero rows.
- Autocommit makes the window wider.
- A unique key that includes user_id does not close it.
- The thread test fails with two 201s.
Run twenty concurrent posts for one slot against the fixed schema. Expect one 201 and nineteen 409s. Two 201s means the partial index is missing or the commit is outside the transaction.
Overview
Concrete fix sketch: partial unique index on active reservations, transactional reserve with IntegrityError mapping to 409, optional SELECT FOR UPDATE on the slot row, and a regression test that spawns concurrent reserveners.
One transaction, one winner
Diagram 1. Optional row lock, then an insert. The partial unique index chooses the single winner.
- 1
Begin the transaction
The session owns the connection until commit or rollback. - 2
Optional FOR UPDATE
Lock the slot row if you want the critical section to be explicit. - 3
Missing slot
No row means SLOT_NOT_FOUND. Do not insert. - 4
Insert active
The new row is status active for that slot and user. - 5
Unique index
A second active row for the slot fails at commit. - 6
409 or 201
IntegrityError rolls back to SLOT_TAKEN. A clean commit is 201.
Decisions
- 1
1. Begin the transaction
- next2. Optional SELECT FOR UPDATE on the slot
- 2
2. Optional SELECT FOR UPDATE on the slot
- next3. Slot exists?
- ?
3. Slot exists?
- No4. Failure: SLOT_NOT_FOUND
- Yes5. INSERT active reservation
- 4
4. Failure: SLOT_NOT_FOUND
- 5
5. INSERT active reservation
- next6. Partial unique index ok?
- ?
6. Partial unique index ok?
- No7. Rollback and 409 SLOT_TAKEN
- Yes8. Commit and 201
- 7
7. Rollback and 409 SLOT_TAKEN
- 8
8. Commit and 201
Lesson map
SlotWise - SQLAlchemy Transaction Fix & Tests
Partial unique index on active reservations, a transactional reserve that maps IntegrityError to 409, optional SELECT FOR UPDATE on the slot row, and a regression test that spawns concurrent reservers.
Architecture. Architecture
Select a node to see why it exists, or an edge to see the protocol, direction, effect, and consequence.
Mermaid export
flowchart TB a["1. Begin the transaction"] b["2. Optional SELECT FOR UPDATE on the slot"] c["3. Slot exists?"] d["4. Failure: SLOT_NOT_FOUND"] e["5. INSERT active reservation"] f["6. Partial unique index ok?"] g["7. Rollback and 409 SLOT_TAKEN"] h["8. Commit and 201"] a -->|continues| b b -->|continues| c c -->|No| d c -->|Yes| e e -->|continues| f f -->|No| g f -->|Yes| h
Schema sketch
CREATE TABLE slots (
id BIGSERIAL PRIMARY KEY,
starts_at TIMESTAMPTZ NOT NULL
);
CREATE TABLE reservations (
id BIGSERIAL PRIMARY KEY,
slot_id BIGINT NOT NULL REFERENCES slots(id),
user_id TEXT NOT NULL,
status TEXT NOT NULL CHECK (status IN ('active','cancelled')),
created_at TIMESTAMPTZ NOT NULL DEFAULT now()
);
CREATE UNIQUE INDEX uq_reservations_active_slot
ON reservations (slot_id)
WHERE status = 'active';Service sketch (FastAPI + SQLAlchemy)
"""Illustrative reserve path. Not a full deployable app.
Idea: one transaction, rely on partial unique index, map conflict to 409.
"""
from sqlalchemy.exc import IntegrityError
def reserve(session, slot_id: int, user_id: str) -> dict:
slot = session.get(Slot, slot_id, with_for_update=True) # optional belt
if slot is None:
raise LookupError("SLOT_NOT_FOUND")
row = Reservation(slot_id=slot_id, user_id=user_id, status="active")
session.add(row)
try:
session.commit()
except IntegrityError:
session.rollback()
raise ValueError("SLOT_TAKEN")
return {"reservation_id": row.id, "slot_id": slot_id}Regression test idea
# Pseudocode: 20 threads hit same slot; exactly one success.
def test_single_winner(client, slot_id):
results = []
def worker():
results.append(client.post("/reservations", json={"slot_id": slot_id, "user_id": str(uuid4())}).status_code)
threads = [threading.Thread(target=worker) for _ in range(20)]
for t in threads: t.start()
for t in threads: t.join()
assert results.count(201) == 1
assert results.count(409) == 19Concepts used, learn more
Read the underlying idea on its own study page. This lesson applies it. It does not replace those pages.
- Interview Debugging & Implementation — Reproduce, Trace, Fix, Ship
- MVCC, Snapshot Isolation & Write Skew
- Isolation Levels Deep Dive
- SSI vs Snapshot Isolation
- Distributed Locks — Correctness, Leases & Fencing Tokens
- Mutexes, Condition Variables, Deadlocks & Happens-Before
- When Locks Win — Contention, Fairness & Hybrid Designs
- Low-Level Design Under Time — Interfaces, State & Tradeoffs
- API Design — Naming, Paths, Routing & Contracts
Pitfalls
session.commit()outside the request scope so FOR UPDATE never held.- Catching broad
Exceptionand returning 500 on conflicts. - Forgetting cancelled rows when designing the unique index.
Interview Q&A
What does with_for_update change?
Answer
It locks the slot row until the transaction ends. The unique index still has to exist for any path that skips the lock.
Why rollback on IntegrityError?
Answer
Postgres aborts the transaction. The session cannot keep going until you roll it back.
Why not catch Exception?
Answer
A programming error should not look like SLOT_TAKEN.
What if a cancelled row exists?
Answer
The partial index ignores it. A new active row is allowed.
What does the 20-thread test prove?
Answer
Exactly one 201. The other nineteen responses are 409.
Which status codes are the contract?
Answer
201 created, 409 taken, and a not-found error for a missing slot. API design owns the wider vocabulary.
Related
The series pager also walks these pages.