Write Skew Breaks Invariants Under Snapshot Isolation

Snapshot isolation gives each transaction a stable view of committed data. That property removes many anomalies caused by values changing midway through a transaction. It does not, by itself, make every concurrent execution equivalent to some serial order.

Write skew is a compact example of the gap. Two transactions read the same valid state, make decisions from that state, then write different rows. Because their write sets do not overlap, both commits can succeed even though the combined result violates a rule that each transaction preserved in isolation.

The issue appears when correctness depends on a predicate or relationship spanning more data than either transaction writes.

A valid snapshot can lead to an invalid result

Consider an on-call table with two engineers. The operational rule requires at least one engineer to remain on call.

engineer | on_call
---------+--------
A        | true
B        | true

Two requests arrive concurrently. Transaction 1 handles A leaving the rotation. Transaction 2 handles B leaving it. Each transaction checks that another engineer is still available before changing its own row.

A simplified sequence is:

T1 reads A=true, B=true
T2 reads A=true, B=true

T1 writes A=false
T2 writes B=false

T1 commits
T2 commits

Each transaction observed a state that satisfied the invariant. Each changed only one row. The final state is:

A=false
B=false

The invariant is now false.

There is no direct write-write conflict. T1 writes row A; T2 writes row B. A concurrency-control mechanism that aborts only transactions competing to update the same row has no conflict to reject here.

Snapshot isolation protects versions, not arbitrary predicates

A common implementation of snapshot isolation uses multiversion concurrency control. A transaction reads from a snapshot associated with a logical point in database history. Concurrent commits do not replace the versions visible inside that snapshot.

At commit time, the database can reject a transaction if another concurrent transaction already changed a row in its write set. This prevents lost updates for overlapping writes under implementations with first-committer-wins behavior.

Write skew crosses a different boundary. The decision can depend on a predicate such as:

count(on_call = true) >= 1

Neither transaction writes that predicate as a single object. Each writes a distinct row whose value contributes to it. Row-level write-conflict detection therefore does not necessarily encode the dependency that matters to the application.

The same shape occurs with rules such as:

at least one active approver exists
room bookings must not overlap
allocated capacity must stay below a limit
exactly one record may hold a logical role

The schema can contain many rows while the business rule spans a set of them.

The anomaly requires a cycle of read-write dependencies

The on-call example can be expressed through dependencies between transactions.

T1 reads B=true, then T2 changes B to false. In the other direction, T2 reads A=true, then T1 changes A to false.

Conceptually:

T1 --read B before T2 write--> T2
T2 --read A before T1 write--> T1

Those opposing dependencies form a cycle. No serial execution produces the same observations and writes: if T1 ran fully before T2, then T2 would see A=false; if T2 ran first, T1 would see B=false.

This is the key distinction between a snapshot that is internally consistent and an execution that is serializable. Snapshot isolation can provide the former while admitting histories that fail the latter.

Locking the rows being changed may still be insufficient

An intuitive fix is to lock each row before updating it:

SELECT * FROM engineers
WHERE id = :engineer_id
FOR UPDATE;

If T1 locks A and T2 locks B, the transactions still do not contend. The protected objects remain disjoint while the invariant spans both rows.

A locking design must cover the data that determines the decision. For the small on-call set, a transaction could lock every row participating in the invariant before checking it:

SELECT id, on_call
FROM engineers
WHERE team_id = :team_id
FOR UPDATE;

Concurrent changes to the same team’s on-call set then serialize around those locks.

This can be effective, but the lock scope matters. If new rows can appear and alter the predicate, locking only currently returned rows may not protect against phantoms unless the database and isolation mode provide suitable predicate or range locking.

A guard row can turn a distributed predicate into one conflict point

Some invariants can be represented by a stable row that every relevant transaction must update or lock.

For example:

team_guard
----------
team_id = 42
revision = 17

Before changing on-call membership, each transaction locks the guard row for team 42. The application then checks the member rows and applies its change while holding that common lock.

T1 locks guard(42)
T2 waits for guard(42)
T1 checks invariant, writes A=false, commits
T2 acquires guard(42), sees current state, rejects B=false

The guard does not need to contain the full derived state. Its purpose is to create deliberate contention for operations that must be ordered together.

This technique trades concurrency for a simpler correctness boundary. It is suitable when the protected scope is naturally partitioned, such as one account, team, tenant, or inventory bucket. A single global guard would serialize unrelated work and can become a throughput bottleneck.

Serializable isolation can reject the dangerous execution

Serializable isolation aims to make committed transactions behave as if they ran in some serial order, even when the database executes them concurrently.

Implementations differ. Some use strict two-phase locking. Others track read-write dependencies and abort a transaction when a dangerous dependency structure develops. Some combine multiple mechanisms.

For the on-call case, a serializable execution cannot commit both transactions with the observations shown earlier. One transaction must effectively precede the other. The later transaction either observes the first change or is aborted and retried against newer state.

Applications must still handle serialization failures correctly. A database abort is part of the concurrency-control contract, not an exceptional corruption event. Retry logic should rerun the complete transaction so all decisions are recomputed from a fresh transactional view.

Constraints are strongest when the invariant fits the schema

A database constraint is often preferable to application-side checking when the rule can be expressed directly and atomically.

Unique constraints are a familiar case. If two transactions attempt to claim the same unique key, the database has a concrete object on which to enforce exclusivity. Foreign keys and check constraints cover other classes of rules.

Cross-row invariants are harder. A normal row-level CHECK constraint generally cannot assert an aggregate over arbitrary sibling rows. A schema redesign can sometimes make the invariant local: store a scarce slot as a unique row, represent capacity as claimable units, or move ownership into a row protected by a uniqueness rule.

Such designs convert an implicit predicate into state the database can conflict on directly. They can reduce reliance on broad locks or high isolation levels, but only when the representation matches the domain rule without introducing a second unsynchronized source of truth.

Retry logic does not repair a transaction that was allowed to commit

Retries help when concurrency control reports a conflict. They do not fix write skew if the chosen isolation level considers both transactions valid and commits them.

A loop such as:

begin
read current rows
check invariant
write one row
commit

can repeat perfectly and still admit the anomaly when no commit fails.

The corrective mechanism must first cause the unsafe concurrent history to block or abort. That may come from serializable isolation, a common lock, a guard row, a database constraint, or another concurrency primitive tied to the invariant. Retry policy belongs after that mechanism.

Retries also need bounded attempts and sensible backoff under contention. A hot invariant can otherwise turn serialization failures into a retry storm.

Tests need concurrent schedules, not only sequential cases

A sequential test can confirm the business rule while missing the concurrency defect entirely.

A useful regression test coordinates two independent database transactions so both read the initial state before either commits. It then releases both writes and checks the outcome.

For a protected implementation, the accepted outcomes should preserve the invariant:

T1 commits, T2 rejects or aborts
or
T2 commits, T1 rejects or aborts

A test that merely starts two goroutines or threads without synchronization may rarely hit the critical interleaving. Barriers or latches make the schedule reproducible enough to exercise the race deliberately.

Production telemetry should also distinguish serialization aborts, lock waits, deadlocks, and application-level invariant rejections. A rising abort rate can indicate that a formerly well-partitioned invariant has become a contention hotspot.

Isolation level is part of the data model

A transaction can contain correct local logic and still produce an invalid global state when its isolation guarantees do not cover the dependencies behind that logic.

Snapshot isolation is valuable because stable snapshots and write-conflict checks remove important classes of concurrency bugs. Its boundary matters just as much as its strengths. When a decision reads a set of rows and concurrent transactions can change different members of that set, write skew deserves explicit consideration.

The durable fix is to make the invariant visible to concurrency control: encode it as a constraint where possible, introduce a shared conflict point where practical, lock the full decision scope when appropriate, or use serializable isolation and handle its aborts. Correctness then rests on a mechanism that represents the actual dependency rather than on the timing of concurrent requests.