The on-call rule is simple: a doctor can leave only if someone else stays. Two transactions each check the count, each see two doctors, and each update a different row. Both commits succeed. Nobody is on call.
Nothing in that outcome requires a dirty read or two writers racing on the same row. It is a mismatch between the invariant the application cares about and the conflicts the database is checking.
Patrick Koss’s Database Internals Visualized includes this example alongside MVCC and snapshot-retention walkthroughs. This lab turns those scenarios into an event-driven workbench: choose the transaction, interleave its operations, and inspect committed versions separately from private writes.
Keep the visibility rules explicit #
Begin T1 and T2 before either commits. Each transaction records the current commit timestamp as its snapshot. Reading the on-call count selects the version of each row whose visibility interval contains that timestamp.
A version is visible when
Stage an off-call update in each transaction. The shared version chains do not change yet. The updates remain private until commit. This separation prevents an animation from accidentally suggesting that other transactions can see uncommitted writes.
Under snapshot isolation, the commit check rejects writes when the same row has been changed since the transaction’s snapshot. T1 writes Alice, while T2 writes Bob. Neither write set intersects the other. Both therefore commit in the overlapping schedule, even though the resulting state violates the application rule.
The conflict runs through the reads #
T1’s decision depends on the old Bob row. T2’s decision depends on the old Alice row. Each transaction changes something the other relied on remaining true.
In dependency-graph terms, this schedule creates a cycle of read-write anti-dependencies. Reasoning only about the two update statements misses it because the writes are disjoint.
This is why a row-level check constraint cannot express every cross-row invariant. The transaction’s read predicate matters. Locking only “my doctor row” does not protect the count of available doctors.
The compact example is intentionally narrow: two named rows and a count. A booking-range or inventory-reservation workload can involve predicates and rows that do not yet exist. Those require stronger reasoning than validating two known keys.
Serializable is a contract; SSI is one implementation #
Switch to Serializable: read-set validation and run the overlapping schedule again. At commit, a writing transaction validates every row version it read. If another committed transaction has changed one, the validation fails and the transaction aborts.
That is conservative optimistic concurrency control. It gives this small fixed-key model a serializable commit order without pretending to implement PostgreSQL’s Serializable Snapshot Isolation. SSI tracks dependencies and dangerous structures; it is not simply “abort whenever any read row changed.”
The distinction matters operationally. A stronger but more conservative validator can reject schedules that a more sophisticated implementation would permit. A demo should expose the mechanism it actually runs, not borrow the name of a more capable database feature.
After an abort, retry the entire transaction. Begin a new snapshot and repeat the decision-making read. Reusing the old count and retrying only the update would preserve the stale premise that caused the problem.
Materialize the conflict when the invariant is narrow #
The third strategy keeps snapshot isolation but adds a write to a shared guard row. Both transactions now update the same key, so one loses the write-write conflict check.
This is a useful design technique when the invariant has a clear scope: a roster, a booking resource or an allocation bucket. It makes the serialization point visible in the data model.
It is also a throughput decision. A single global guard row serializes unrelated work. Partitioning the guard too aggressively can stop protecting an invariant that spans partitions. Every path that changes the protected state must participate; an administrative script that bypasses the guard can defeat the design.
The lab stages a guard write, not a lock acquisition. A pessimistic locking implementation would wait, and its read timing would need to be specified. These are related techniques with different scheduling behavior.
A long snapshot has a storage cost #
Begin the long-running Reader. Then manually run T1 through commit. The current Alice row is now OFF, but the reader’s earlier snapshot must still see the old ON version.
Collect obsolete versions. The reader pins the reclamation horizon, so the old Alice version remains. Commit the reader and collect again; the version can now be removed if no other active snapshot needs it.
The lab uses a simple global snapshot horizon. Real engines may have additional retention constraints from replication, backup, undo management or transaction metadata. The general lesson survives: a transaction that “only reads” can still impose a substantial write-side storage and maintenance cost.
That connection is easy to miss when isolation levels and vacuum behavior are taught as unrelated topics.
Turn the invariant into an operational test #
For a service, I would test the actual invariant under deliberately overlapping transactions, not merely assert that both update endpoints return success. Record serialization failures, retries, transaction duration and retry exhaustion. Keep non-idempotent external side effects out of a transaction body that might be repeated, or arrange an explicit idempotency/outbox strategy.
A higher isolation level changes which histories may commit. It does not eliminate application errors, guarantee progress under arbitrary contention, or make retries optional.
Reference: Database Internals Visualized, revision 08b0251b, particularly its doctor/write-skew and MVCC examples. The local engine is an original extension with freely interleavable operations, private write sets, commit-time validation and snapshot-aware reclamation. It is not a simulator of any one commercial database.