Articles / DATABASES

Write skew, snapshot isolation and what SERIALIZABLE actually catches

The anomaly that snapshot isolation allows, why it passes every test you write, and the three ways to fix it: serializable with retries, locking the right rows, or materializing the conflict.

DatabasesArchitecture
Table of contents
  1. Context
  2. Why the database allowed it
  3. Fix 1: use SERIALIZABLE and retry
  4. Fix 2: lock what you read
  5. Fix 3: materialize the conflict
  6. Other shapes of the same bug
  7. Constraints beat code
  8. The same code behaves differently across engines
  9. Finding it before production
  10. What it costs
  11. Conclusion
  12. References

Most developers can name the classic isolation anomalies: dirty reads, non-repeatable reads, phantoms. There is one more that matters in practice, because it lives in a level most people consider safe. It is called write skew.

Context

Take the standard example. A hospital requires that, for each shift, at least one doctor stays on call. Two doctors, Alice and Bob, are both on call tonight and both feel ill. Each opens the app and asks to go off call.

Each transaction checks the rule first:

sql
BEGIN ISOLATION LEVEL REPEATABLE READ;

SELECT count(*) FROM on_call WHERE shift_id = 42 AND active;
-- both transactions see 2, so both conclude it is safe

UPDATE on_call SET active = false
WHERE shift_id = 42 AND doctor_id = :me;

COMMIT;

Both transactions run in parallel. Each sees a snapshot where two doctors are on call. Each updates only its own row. Both commit.

The shift now has nobody on call.

Why the database allowed it

Snapshot isolation prevents two transactions from writing to the same row concurrently. If Alice and Bob had both updated the same row, one of them would have been aborted.

But here they updated different rows. There is no write-write conflict to detect. The problem is that each decision was based on a read that the other transaction invalidated, and snapshot isolation does not track that.

This is not a bug. In PostgreSQL, REPEATABLE READ is snapshot isolation. In SQL Server it is SNAPSHOT. Oracle calls its snapshot level SERIALIZABLE, which is a famously unhelpful name.

It also explains why the bug is so hard to catch. It needs two transactions overlapping in a window of milliseconds, so it never appears in a unit test and shows up in production under load.

Fix 1: use SERIALIZABLE and retry

PostgreSQL's SERIALIZABLE level is implemented with Serializable Snapshot Isolation. It runs transactions on snapshots like before, but it also tracks read-write dependencies between them, and when it detects a pattern that could not occur in any serial order, it aborts one of the transactions.

The aborted one gets SQLSTATE 40001. The contract is that the application must retry:

csharp
for (var attempt = 0; attempt < 5; attempt++)
{
    try
    {
        await using var tx = await connection.BeginTransactionAsync(IsolationLevel.Serializable, ct);

        var onCall = await connection.ExecuteScalarAsync<int>(
            "SELECT count(*) FROM on_call WHERE shift_id = @shift AND active", new { shift }, tx);

        if (onCall <= 1)
            throw new InvalidOperationException("At least one doctor must stay on call.");

        await connection.ExecuteAsync(
            "UPDATE on_call SET active = false WHERE shift_id = @shift AND doctor_id = @doctor",
            new { shift, doctor }, tx);

        await tx.CommitAsync(ct);
        return;
    }
    catch (PostgresException ex) when (ex.SqlState == PostgresErrorCodes.SerializationFailure)
    {
        await Task.Delay(Backoff(attempt), ct);
    }
}

Two cautions. The detection is conservative, so occasional unnecessary aborts are normal. And without the retry loop, SERIALIZABLE just turns a silent anomaly into visible errors.

Read-only reports can use SERIALIZABLE READ ONLY DEFERRABLE, which waits for a safe snapshot and then runs without any risk of being aborted.

Fix 2: lock what you read

If you would rather avoid retries, make the dependency explicit by locking the rows the decision is based on:

sql
SELECT doctor_id FROM on_call
WHERE shift_id = 42 AND active
FOR UPDATE;

Now the second transaction blocks until the first commits, and then sees the updated state.

This works when the rows already exist. It does not protect against phantoms, that is, rows another transaction is about to insert, because you cannot lock something that is not there yet.

Fix 3: materialize the conflict

The most robust option is often to make the conflict physical. Lock a row that represents the thing being protected, such as the shift itself:

sql
SELECT id FROM shifts WHERE id = 42 FOR UPDATE;

Every transaction touching that shift's on-call list takes the same lock first. For rules like "no overlapping bookings" or "balance must not go negative across accounts", a unique constraint, an exclusion constraint or a single guard row can encode the invariant directly in the schema.

Other shapes of the same bug

On-call doctors are a textbook story, but the pattern hides in very ordinary features:

  • Room booking. Check that no booking overlaps the requested time, then insert one. Two requests for the same slot both pass the check and both insert.
  • Claiming a name. Check that the username is free, then insert it.
  • A combined balance. A customer's rule is that the sum across two accounts must stay positive. Two withdrawals, one from each account, each checked against the sum, each writing a different row.
  • Capacity. At most ten participants per event, checked by counting rows and then adding one.

The structure is always the same: read a set of rows, decide, and write something that changes the property you just checked. If two transactions can do that at once on a snapshot, the rule is not protected.

Constraints beat code

Whenever the rule can be expressed as a constraint, let the database enforce it. A unique index settles the username case with no help from isolation levels. For overlapping ranges, PostgreSQL has an exclusion constraint:

sql
CREATE EXTENSION IF NOT EXISTS btree_gist;

CREATE TABLE bookings (
    room_id integer   NOT NULL,
    during  tstzrange NOT NULL,
    EXCLUDE USING gist (room_id WITH =, during WITH &&)
);

Now two concurrent inserts for overlapping periods cannot both succeed. One of them fails with an exclusion violation, whatever the isolation level, because the check happens inside the index at the moment of the write.

For rules that no constraint can state, such as "at least one doctor", the guard row from the previous section is the next best option.

The same code behaves differently across engines

Isolation level names are not portable, and this is where migrations bite.

  • PostgreSQL REPEATABLE READ is snapshot isolation, and SERIALIZABLE detects dangerous patterns and aborts.
  • SQL Server offers SNAPSHOT, which allows write skew, and its SERIALIZABLE works with range locks, so conflicts show up as blocking and deadlocks instead of serialization failures.
  • MySQL InnoDB defaults to REPEATABLE READ, but locking reads and writes see the latest committed data and take gap locks, so it behaves differently from the PostgreSQL level with the same name.
  • Oracle calls its snapshot level SERIALIZABLE, and it allows write skew.

When code moves between engines, or when you only test on one, the invariants you believed the isolation level provided may no longer hold.

Finding it before production

Write skew needs two transactions to interleave at exactly the wrong moment, so make them do it on purpose. A test with two connections and a barrier forces both to read before either writes:

csharp
[Fact]
public async Task Two_doctors_cannot_both_go_off_call()
{
    var barrier = new Barrier(2);

    async Task GoOffCall(int doctorId)
    {
        await using var connection = await _db.OpenAsync();
        await using var tx = await connection.BeginTransactionAsync(IsolationLevel.RepeatableRead);

        var onCall = await connection.ExecuteScalarAsync<int>(
            "SELECT count(*) FROM on_call WHERE shift_id = 42 AND active", transaction: tx);

        barrier.SignalAndWait();            // both have read before either writes

        if (onCall > 1)
            await connection.ExecuteAsync(
                "UPDATE on_call SET active = false WHERE shift_id = 42 AND doctor_id = @doctorId",
                new { doctorId }, tx);

        await tx.CommitAsync();
    }

    await Task.WhenAll(GoOffCall(1), GoOffCall(2));

    Assert.True(await CountActiveAsync(shiftId: 42) >= 1);   // fails here: the anomaly is reproduced
}

On RepeatableRead this test fails every time, which is the point: you now have a reproduction. Change the fix under test, whether it is serializable with a retry, a lock or a constraint, and watch it pass.

What it costs

SERIALIZABLE is not free. Detection is conservative, so some transactions abort that would have been fine, and the probability grows with the number of rows a transaction touches and with how long it stays open. Keep serializable transactions short, touch as few rows as you can, and declare reports as read-only. The cost is usually small compared with the cost of writing, and then debugging, a bespoke locking scheme.

Conclusion

Write skew is what happens when a transaction decides based on data it did not lock, and someone else changes that data without touching anything it wrote.

The question to ask about any invariant that spans more than one row is: which mechanism enforces this when two requests arrive at the same moment? If the honest answer is "the isolation level", check which level that is, and whether it really detects this.

References

  • Transaction Isolation in the PostgreSQL documentation.
  • Berenson et al., A Critique of ANSI SQL Isolation Levels (1995), where write skew was formally described.
  • Ports and Grittner, Serializable Snapshot Isolation in PostgreSQL (VLDB 2012).