The Anomaly That Slips Past Repeatable Read
Most engineers assume Repeatable Read (Snapshot Isolation) is safe enough for complex business logic. It is not. There is a class of anomaly that Snapshot Isolation cannot prevent: Write Skew.
Write Skew occurs when two transactions each read an overlapping set of data, make decisions based on what they read, and then each write to different rows — so no individual row has a conflict, but the combined result violates a business constraint.
The On-Call Doctor Scenario
Business rule: At least one doctor must be on call at all times.
Initial state: Alice = ON_CALL, Bob = ON_CALL
Time Transaction A (Alice goes off call) Transaction B (Bob goes off call)
---- ---------------------------------------- ----------------------------------
T1 BEGIN BEGIN
T2 SELECT count(*) FROM on_call SELECT count(*) FROM on_call
WHERE status = 'ON_CALL' WHERE status = 'ON_CALL'
-- Returns: 2 (Alice + Bob) -- Returns: 2 (snapshot: Alice + Bob)
T3 -- "Count > 1, safe to leave" -- "Count > 1, safe to leave"
T4 UPDATE doctors SET status = 'OFF' UPDATE doctors SET status = 'OFF'
WHERE name = 'Alice' WHERE name = 'Bob'
T5 COMMIT COMMIT
-- No conflict detected! -- No conflict detected!
Final state: Alice = OFF_CALL, Bob = OFF_CALL ← ZERO doctors on call!
Under Snapshot Isolation, each transaction read a consistent snapshot showing 2 doctors. Each updated a different row. The database saw no write-write conflict on any single row, so both commits succeeded. The business rule was silently violated.
Why This Is Dangerous
Write Skew is insidious because:
1. No error is raised. Both transactions commit successfully.
2. Each transaction's logic was individually correct.
3. Standard row-level locking doesn't help because the transactions write to different rows.
4. It only manifests under concurrent load — it's invisible in testing with a single user.
Real-world examples beyond on-call scheduling:
- Double-spending: Two payment transactions both check "balance > amount" and both pass, but together they overdraw the account.
- Seat booking: Two users both check "seat is available" and both book the same seat.
- Username uniqueness: Two registrations both check "username not taken" and both insert the same username.
The Fix: Serializable Isolation
Only Serializable isolation (specifically, Serializable Snapshot Isolation / SSI in PostgreSQL 9.1+) can automatically detect and prevent write skew. SSI tracks read/write dependencies between transactions and aborts one if it detects a dangerous cycle.
Enabling it is a one-line change — wrapping the same read-then-write logic in BEGIN ISOLATION LEVEL SERIALIZABLE instead of the default. If PostgreSQL detects the kind of read/write dependency cycle that causes write skew, it aborts one of the transactions with ERROR: could not serialize access due to read/write dependencies among transactions rather than letting both commit silently. The application must be prepared to catch that error and retry the transaction.
"What's the difference between a lost update and write skew?" is a classic senior-level question. A lost update is when two transactions read-modify-write the same row and one overwrites the other's change (e.g., both increment a counter). Write skew is when they read overlapping data but write to different rows, violating a constraint that spans those rows. Lost updates can be prevented with Repeatable Read or SELECT FOR UPDATE. Write skew requires Serializable isolation or explicit application-level locking.