Write skew: the race that snapshot isolation lets through

The idea

Snapshot isolation gives each transaction a consistent view of the database as of its start. It prevents dirty reads and non-repeatable reads, and it aborts one side when two transactions write the same row. In PostgreSQL this is what REPEATABLE READ means, and Oracle's SERIALIZABLE level is also snapshot isolation.

Write skew is the anomaly it does not catch. Two transactions read an overlapping set of rows, each checks an invariant against its snapshot, and each writes to a different row. Neither write conflicts with the other, so both commit. Each transaction was correct on its own. Together they produce a state that neither would have allowed.

The problem it names is broader than isolation levels: any rule that spans multiple rows is only as safe as the mechanism that serializes the decisions about it.

An example

A workspace must always keep at least one admin. It has two admins, A and B, and both click "leave admin role" at the same moment.

• Transaction 1 runs SELECT count(*) FROM members WHERE workspace_id = 7 AND role = 'admin' and sees 2.
• Transaction 2 runs the same query against its own snapshot and also sees 2.
• Transaction 1 updates A's row to member. The check passed, so this is allowed.
• Transaction 2 updates B's row to member. Its check also passed.
• Both commit. They touched different rows, so no write conflict was detected.

The workspace now has zero admins. Every log line looks correct, and the bug is close to impossible to reproduce in a test that runs requests sequentially.

Where it breaks

The common fixes each have limits.

SELECT ... FOR UPDATE on the rows you read works when those rows exist. It does nothing when the invariant is about absence, such as "no overlapping booking for this room". You cannot lock a row that is not there, which is the phantom form of the same problem.

True SERIALIZABLE, which PostgreSQL implements with serializable snapshot isolation, catches write skew by aborting one transaction with SQLSTATE 40001. That moves the burden to the caller: the whole transaction must be retried, and under heavy contention on a hot invariant the abort rate can climb until throughput suffers.

Retries are also only safe if the transaction has no side effects outside the database. A transaction that sent an email or called a payment API before committing cannot simply be replayed.

None of this helps when the invariant spans two services with separate databases. Isolation levels end at the database boundary.

In practice

I would start by writing down every invariant that spans more than one row, because write skew only threatens those, and most teams have never listed them.

Then pick the cheapest mechanism that actually enforces each one:

• Prefer a declarative constraint when one exists. A unique index, a check constraint, or a PostgreSQL exclusion constraint on a range type removes the race entirely for cases like overlapping bookings.
• When the rule needs code, lock a single parent row, such as the workspace, with FOR UPDATE before reading children. This materializes the conflict on a row that always exists.
• Use SERIALIZABLE for complex rules, and wrap transactions in a retry loop that handles 40001 with a bounded number of attempts and backoff.
• Keep external side effects out of the transaction. Record the intent in an outbox table and act on it after commit.
• Test with real concurrency: two connections, a barrier between the read and the write, and an assertion on the invariant afterwards.

If your ORM opens transactions implicitly, find out which isolation level it uses. PostgreSQL defaults to READ COMMITTED, which is weaker still.

Any invariant that spans more than one row needs a named enforcement mechanism, a constraint, a lock on a row that always exists, or serializable isolation with retries, because snapshot isolation alone will let two correct transactions break it.