Write Skew Breaks Cross-Row Invariants Under Snapshot Isolation

Snapshot isolation gives each transaction a stable database view and commonly prevents concurrent transactions from committing conflicting writes to the same row. That is a strong concurrency property, but it does not make every application invariant serializable.

Write skew appears when two transactions read overlapping state, make decisions from the same valid snapshot, then write different records. Since their write sets do not collide, both commits may succeed. The combined state can violate a rule that each transaction checked before writing.

A valid snapshot can lead to an invalid result

Consider an on-call roster with two operators. The rule is simple: at least one operator must remain on call.

initial state

operator A = on
operator B = on

Two transactions begin from that state. Transaction T1 intends to take A off call. T2 intends to take B off call. Each transaction checks that another operator remains available.

T1 snapshot: A=on, B=on
T2 snapshot: A=on, B=on

T1 sees B=on -> writes A=off
T2 sees A=on -> writes B=off

The writes target different rows. A snapshot-isolation implementation that detects write-write conflicts can therefore allow both transactions to commit.

final state

operator A = off
operator B = off

Neither transaction observed an invalid snapshot. The invalid state emerges from the pair of commits.

Row conflict detection is narrower than an invariant

A uniqueness rule backed by a database constraint has a concrete enforcement point. A cross-row predicate such as “at least one operator remains on call” may not map to a single row that every transaction modifies.

The application can run a query before the update:

SELECT count(*)
FROM operators
WHERE on_call = true;

A result of 2 does not reserve that fact for the rest of the transaction. Another concurrent transaction can make a compatible decision from its own snapshot.

The race is therefore not a missing application check. Both transactions can execute the check correctly. The issue is that the isolation level does not necessarily order the predicate read against the other transaction’s disjoint write.

A common write can create a conflict

One mitigation is to make transactions that protect the same invariant contend on a shared record. For example, an on-call group row can act as the serialization point.

group row: primary-on-call

T1 locks group row -> checks roster -> updates A -> commit
T2 waits          -> checks new roster -> decides

The shared row turns a logical conflict into a physical conflict the database can coordinate. This approach is often practical when the invariant has a clear scope, such as one team, account, inventory pool, or scheduling bucket.

The serialization record should match that scope. A single global lock row can preserve correctness but create unnecessary contention between independent groups.

Serializable isolation can reject the dangerous execution

Serializable isolation aims to make committed transactions equivalent to some serial order. Database implementations reach that property through different mechanisms, including locking and detection of dependency patterns.

For the roster case, a serializable database may block one transaction or abort one with a serialization failure. The application must then handle the failure according to the database contract, often by retrying the complete transaction from a fresh state.

A retry is not merely a repeat of the final UPDATE. The decision depended on earlier reads, so the full decision unit needs a new transaction and a new view.

begin
read invariant state
decide
write
commit

If commit reports a serialization failure, that whole unit is the retry boundary.

Explicit locks need the correct lock target

SELECT ... FOR UPDATE can be useful when the rows that determine the decision are known and lockable. It is not a universal predicate lock.

Suppose a rule depends on the absence of a matching row. There may be no existing row to lock. Range locking, predicate locking, advisory locking, or a dedicated serialization record may be needed, depending on the database and access pattern.

Even with existing rows, every code path that can change the protected state must follow the same coordination protocol. A lock in one endpoint does not preserve an invariant if another writer bypasses it.

Constraints are preferable when the rule fits them

Database constraints place validation at the state transition boundary. Unique constraints, foreign keys, exclusion constraints, and suitable CHECK constraints can remove classes of application races when they can express the rule directly.

Not every cross-row invariant fits a declarative constraint. When it does, the constraint is generally easier to audit than a convention spread across multiple application call sites.

When it does not, the design needs an explicit concurrency mechanism whose scope matches the invariant.

Testing requires overlapping transactions

A sequential test cannot expose write skew. Both operations need to hold snapshots that overlap in time.

A useful concurrency test coordinates two database connections:

T1 begin
T2 begin

T1 read predicate
T2 read predicate

T1 write row A
T2 write row B

T1 commit
T2 commit

The expected result depends on the chosen protection. Under a vulnerable execution, both commits succeed and the invariant fails. With a serialization point or suitable serializable behavior, one transaction waits, observes changed state, or fails at commit.

Tests should run against the actual database engine and isolation configuration used in production. Transaction semantics differ across engines, and a test double that executes transactions sequentially cannot establish the relevant behavior.

Isolation level is part of application correctness

Transaction code often looks locally sound because every read, condition, and update is correct in isolation. Concurrency adds executions that are not visible in a single request trace.

For invariants spanning multiple records, correctness depends on the relation between reads and writes across transactions. Snapshot isolation can provide a consistent view while still permitting write skew. The remedy is to give the invariant an enforcement point: a database constraint, a shared serialization record, explicit locking with suitable scope, or serializable isolation with correct retry handling.