Transactional Anomalies and Multi-Row Invariants Under Snapshot Isolation
Why default database isolation levels fail to protect multi-record business invariants, and how write skew occurs without row-level write conflicts.
Most relational database installations—including default configurations of PostgreSQL, Oracle, and Microsoft SQL Server—operate under Read Committed or Snapshot Isolation. Engineering teams routinely assume that wrapping database statements inside a transaction block guarantees that cross-record constraints cannot be violated. In distributed or high-concurrency environments, this assumption is false.
The most pervasive concurrency bug in production systems is Write Skew. Write skew arises when two concurrent transactions read overlapping datasets, verify a joint invariant, and subsequently write to disjoint records that collectively break that invariant.
Consider an on-call physician scheduling system with an invariant: at least one doctor must be active at all times. Doctor A and Doctor B concurrently submit requests to clock out. Under Snapshot Isolation, both transactions query the active count, both observe count = 2, and both proceed with their individual updates. Because Doctor A updates row A and Doctor B updates row B, their write sets do not intersect. Neither transaction blocks or aborts, yet the hospital is left with zero doctors on duty.
To eliminate write skew, engineering teams must either elevate the transaction isolation level to Serializable (which uses Serializable Snapshot Isolation to track read-write dependencies in memory), enforce explicit row locks using SELECT FOR UPDATE on an anchor record, or introduce explicit conflict materialization tables.