The On-Call Doctor Anomaly (Write Skew under Snapshot Isolation)
The On-Call Doctor Anomaly (Write Skew under Snapshot Isolation)
Your hospital staff scheduling platform enforces a strict safety invariant: at all times, at least one on-call doctor must remain active in the hospital. The application runs on PostgreSQL with default Read Committed / Repeatable Read (Snapshot Isolation) semantics. Exactly two doctors (Alice and Bob) are currently on call. Both Alice and Bob feel ill at the exact same moment and open the app to take sick leave. In transaction T1, Alice's app checks: SELECT count(*) FROM doctors WHERE on_call = true; (returns 2). Because the count is >= 2, T1 executes UPDATE doctors SET on_call = false WHERE id = 'alice';. Simultaneously in transaction T2, Bob's app runs the exact same check against its snapshot: SELECT count(*) FROM doctors WHERE on_call = true; (returns 2), and executes UPDATE doctors SET on_call = false WHERE id = 'bob';. Because T1 and T2 updated completely disjoint rows (Alice's row vs Bob's row), neither transaction experienced a row lock conflict. Both transactions commit cleanly. The hospital now has zero doctors on call, violating the safety invariant and risking patient lives. You are tasked with analyzing this classic Write Skew anomaly and redesigning transaction handling to guarantee invariant enforcement under high concurrency.
The On-Call Doctor Anomaly (Write Skew under Snapshot Isolation)
Your hospital staff scheduling platform enforces a strict safety invariant: at all times, at least one on-call doctor must remain active in the hospital. The application runs on PostgreSQL with default Read Committed / Repeatable Read (Snapshot Isolation) semantics. Exactly two doctors (Alice and Bob) are currently on call. Both Alice and Bob feel ill at the exact same moment and open the app to take sick leave. In transaction T1, Alice's app checks: SELECT count(*) FROM doctors WHERE on_call = true; (returns 2). Because the count is >= 2, T1 executes UPDATE doctors SET on_call = false WHERE id = 'alice';. Simultaneously in transaction T2, Bob's app runs the exact same check against its snapshot: SELECT count(*) FROM doctors WHERE on_call = true; (returns 2), and executes UPDATE doctors SET on_call = false WHERE id = 'bob';. Because T1 and T2 updated completely disjoint rows (Alice's row vs Bob's row), neither transaction experienced a row lock conflict. Both transactions commit cleanly. The hospital now has zero doctors on call, violating the safety invariant and risking patient lives. You are tasked with analyzing this classic Write Skew anomaly and redesigning transaction handling to guarantee invariant enforcement under high concurrency.
Provide 1–2 precise sentences for each architectural dimension. Each box guides you on what staff-level interviewers evaluate.
Define SLA targets, hard consistency constraints, and conditions the system must never violate.
Quantify throughput (QPS/RPS), read:write ratios, and peak burst multipliers.
Step-by-step path: client ingress → API gateway → queues → background workers → persistence.
Database engine, table schema, partition keys (PK/SK), and durability strategy.
What resource hits saturation first under 10x traffic? (CPU, disk IOPS, connection pools, network).
Worker crashes, network partitions, split-brain, poison pill DLQ, retries, and idempotency.
What did you sacrifice in exchange and why? (e.g. eventual consistency vs latency, cost vs redundancy).