Write Skew Can Break Invariants Under Snapshot Isolation

Snapshot isolation gives each transaction a stable view of committed data and usually rejects concurrent updates to the same row. That combination removes many anomalies that appear under weaker isolation levels. It does not, however, make every application invariant serializable.

Write skew is the important edge case. Two transactions read overlapping state, make decisions from the same valid snapshot, then update different rows. Because their write sets do not collide, both can commit. The combined result can violate a rule that neither transaction violated in its own snapshot.

A cross-row rule creates the opening

Consider an on-call table with two doctors. The operational rule requires at least one doctor to remain on call:

doctor | on_call
-------+--------
A      | true
B      | true

Transaction T1 checks both rows, sees that B is available, and changes A to false. At the same time, T2 checks the same snapshot, sees that A is available, and changes B to false.

Their logical flow is:

T1: read A=true, B=true
T2: read A=true, B=true

T1: write A=false
T2: write B=false

T1: commit
T2: commit

No row receives two concurrent writes. A first-committer-wins check on individual rows therefore has no direct conflict to reject. After both commits, both values are false, and the cross-row rule has failed.

Each transaction made a locally valid decision. The anomaly appears only when their effects are combined.

Snapshot isolation protects versions, not arbitrary predicates

A typical snapshot-isolation implementation gives a transaction a consistent database snapshot taken from a logical point in time. Reads continue to see versions appropriate to that snapshot even if another transaction commits newer versions while the first transaction is still running.

For writes, the database detects conflicting concurrent updates according to its concurrency-control rules. Two transactions attempting to replace the same row cannot normally commit independently.

The on-call example avoids that collision. T1 writes row A and T2 writes row B. The business rule spans both rows, but the storage conflict is evaluated over separate write targets.

This distinction matters because an invariant such as “at least one matching row must remain” is a predicate over a set. It is not encoded automatically as a conflict on one physical record.

The anomaly is different from a lost update

A lost update occurs when concurrent work targets the same logical value and one update overwrites or obscures another. Write skew can happen even when every written row has exactly one writer.

That difference changes the remedy. Adding a version column to each doctor row can detect two writers racing on the same doctor, but it does not make T1 and T2 conflict when they intentionally modify different doctors.

The relevant question is not merely whether each row was updated safely. The transaction boundary must also protect the relationship among rows that establishes the invariant.

A serial execution would reject one decision

Run the same operations serially. If T1 commits first, T2 subsequently reads A as false. With the rule applied correctly, T2 must keep B on call. Reversing the order produces the symmetric result.

There is no serial ordering in which both transactions read the original two-true state and both validly switch their own row off while preserving the rule.

That is the key diagnostic property. The final state is possible under snapshot isolation but not under a serial execution that applies the same decision logic. Serializable isolation is designed to prevent such outcomes, though the mechanism varies by database.

Serializable isolation may abort instead of blocking

Serializable execution does not require every database to place broad locks around all reads. Some systems use predicate or range locking. Others track read-write dependencies and abort a transaction when the dependency graph indicates a serialization anomaly.

The application must therefore treat serialization failures as a normal concurrency outcome. A transaction can be correct and still be asked to retry because its observed snapshot can no longer be placed safely into a serial history.

Retries also need boundaries. The entire decision transaction must run again against a fresh view; retrying only the final UPDATE preserves the stale decision that caused the conflict.

Explicit locking can make the conflict concrete

When the invariant has a small, stable coordination point, explicit locking can turn a logical conflict into a physical one.

For example, an application can lock a parent record representing the on-call group before checking and changing member rows:

BEGIN;
SELECT id FROM on_call_group
WHERE id = 42
FOR UPDATE;

SELECT doctor, on_call
FROM on_call_member
WHERE group_id = 42;

-- validate the invariant and update one member
COMMIT;

Concurrent transactions for the same group then contend on the parent row. The lock serializes decisions for that group even though the eventual member updates target different rows.

This approach is straightforward when a natural coordination record exists. It can become a throughput bottleneck if unrelated work is forced through an unnecessarily broad lock.

Constraints help only when the invariant can be represented

Database constraints are valuable because they move correctness checks close to the data, but not every cross-row predicate fits a simple CHECK constraint. A row-level check generally cannot assert an aggregate property over arbitrary peer rows.

Sometimes the schema can be reshaped so the invariant becomes a unique key, foreign key, exclusion rule, or update to one guarded aggregate row. In those cases the database gains a concrete object on which concurrent transactions can conflict.

The schema change must preserve the actual business rule. A synthetic counter, for example, introduces its own maintenance requirements. If the counter can drift from the member rows, it merely moves the correctness problem.

Tests need concurrent schedules, not only sequential cases

Sequential unit tests can verify the decision rule while missing write skew entirely. A useful concurrency test holds two transactions open, lets both read the initial valid state, then allows their writes and commits to race in a controlled order.

The assertion belongs on the invariant after both transactions finish. Depending on the chosen protection, the expected result may be one serialization failure, one lock wait followed by a changed decision, or another database-specific conflict.

The test should also cover retry behavior. If a serialization failure is retried, the retry must repeat every read that contributed to the decision.

Isolation level is part of the data model

Choosing an isolation level is not only a performance setting. It defines which concurrent histories the application must tolerate.

Snapshot isolation is strong enough for many workloads and can provide excellent read behavior. Its write-conflict checks still operate on concrete writes, so a rule spanning several independently writable records deserves separate analysis.

For each critical invariant, identify the rows or predicates that establish it, then identify the concurrent transactions that can change those facts. If two valid snapshots can lead to disjoint writes whose combination breaks the rule, the design needs a stronger serialization mechanism or an explicit coordination point.

The safest boundary is the one that makes the business invariant and the concurrency conflict refer to the same piece of state.