Write Skew Anomaly
SEV-2, Significant ImpactIsolated propagation · concurrency · Affects 0 scenario(s)
Severity Classification
Classified as CRITICAL based on failure mode severity.
Propagation Chain
Origin component
Write Skew Anomaly begins at the source component. Trigger: Two transactions concurrently check a multi-row or aggregate invariant, both find it satisfied, and each writes its own row.
Immediate (T+0) · Signal: Log Errors
Blast Radius
The damage is scoped to the specific business invariant that was violated, but it is persistent, silent, incorrect state rather than an outage. Depending on the domain it ranges from one double-booked room to systemic inventory oversell or financial double-spend. Because both transactions committed cleanly, there is no error in the logs; the corruption surfaces later, when a downstream process or a human notices the invariant is broken, often hours or days after the fact.
Contributing Factors
This operational trigger enables Write Skew Anomaly: Two transactions concurrently check a multi-row or aggregate invariant, both find it satisfied, and each writes its own row
This operational trigger enables Write Skew Anomaly: Reservation, booking, or quota flows that check availability and then write on a different row without materializing the conflict
This operational trigger enables Write Skew Anomaly: An invariant about the absence of a row (uniqueness, no-overlap) enforced in application code rather than by a database constraint
Remediation Plan
Query the affected table to find where the invariant is currently violated (for example, shifts whose on-call count is zero, or overlapping bookings)
Effort: Minutes to hours (on-call response)
Repair the state explicitly inside a SERIALIZABLE transaction so the correction itself cannot skew
Effort: Minutes to hours (on-call response)
Audit the surrounding time window for other violations of the same invariant, since one skew often implies more
Effort: Minutes to hours (on-call response)
If the rule can be expressed as a constraint, the database enforces it at every isolation level and write skew cannot occur, because the second write is rejected rather than checked. PostgreSQL UNIQUE handles uniqueness; an EXCLUDE USING gist exclusion constraint over a range type enforces no-overlap (no two bookings for the same room whose time ranges intersect) directly, which is exactly the phantom case FOR UPDATE cannot cover. Not every invariant fits a constraint (an aggregate like "at least one on call" does not), but this is the first option to evaluate because it removes the anomaly rather than coordinating around it.
Effort: 1 day to 1 week
When the invariant is carried by rows that exist, lock them: SELECT ... FOR UPDATE on the constraint-relevant rows before the check turns the pattern into a write-write conflict the database will serialize. When the invariant is about rows that do not yet exist (a phantom), introduce a row to lock: a single guard or parent row per resource (the shift row, the room row) taken FOR UPDATE, so concurrent claimants serialize on it. This is materializing the conflict, and it is targeted and cheap, but it requires finding every write-skew-prone path and is easy to miss one in a large codebase.
Effort: 1 day to 1 week
Configure alerts for: log errors, alert, error rate spike. Set thresholds to fire at 70% of critical level to allow response before full failure.
Effort: 1–3 days
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE lets PostgreSQL SSI detect the dangerous read-write dependency structure and abort one transaction with SQLSTATE 40001; the application retries the whole transaction. This protects paths you did not individually identify, which is its advantage over materializing conflicts. The caveats are load bearing: every transaction touching that data must also be SERIALIZABLE or the guarantee leaks, the retry must wrap every such path or that path is silently unprotected, and the retry logic must be idempotent because a transaction can run more than once. Expect a low retry rate normally and higher under hotspots.
Effort: 1–4 sprints
This post-mortem framework is derived from structured architecture knowledge. It provides an evidence-grounded starting point, not a substitute for a live incident review conducted by the team closest to the system. Adjust remediation priorities based on actual runtime observations.