Snapshot Isolation and Write Skew: What Your Database Actually Promises

Posted on

Every relational database ships an isolation level selector, and most applications pick whatever the default is and move on. That decision usually holds up until the day an invariant quietly breaks: two transactions each check a condition, each decide it is safe to proceed, and both commit successfully. No constraint fires, no error is raised, no lock waits. The data is just wrong. This is the signature of write skew, and it survives every isolation level below Serializable.

The confusion is understandable. The SQL standard defines four isolation levels, but the mapping between what the standard promises and what your database actually implements is looser than the naming suggests. PostgreSQL’s Repeatable Read, for example, is not the standard’s Repeatable Read at all — it is snapshot isolation, a different algorithm with a different anomaly profile. If you do not know which anomaly your level still permits, you cannot tell whether that critical transaction is actually safe. This post walks through what each level really guarantees, shows write skew with a concrete example, and lays out the practical options for handling it.

The four levels, translated

The standard defines isolation levels in terms of prohibited phenomena: dirty reads (reading uncommitted data), nonrepeatable reads (re-reading a row and finding it changed), and phantom reads (re-running a query and finding the result set changed). PostgreSQL implements only three distinct behaviors internally: Read Uncommitted behaves exactly like Read Committed there, since uncommitted data is never observable under its MVCC architecture.

Here is what the levels actually do in PostgreSQL:

  • Read Committed (the default): every statement sees a fresh snapshot of committed data. Two SELECTs in the same transaction can legitimately see different results.
  • Repeatable Read: the whole transaction sees one consistent snapshot, taken at the first query. Dirty reads, nonrepeatable reads, and even phantom reads are prevented.
  • Serializable: behaves like Repeatable Read, plus the database actively monitors for schedules that could not have resulted from any serial execution, and aborts one of the offending transactions.

PostgreSQL’s Repeatable Read is stronger than the standard requires — its snapshot-based implementation prevents phantoms too — which is why the docs describe it as snapshot isolation. Other databases make different choices: MySQL’s InnoDB defaults to Repeatable Read with next-key locking to suppress phantoms, while PostgreSQL, Oracle, and SQL Server all default to Read Committed. The names are shared; the guarantees are not portable.

The anomaly snapshot isolation does not prevent

Snapshot isolation guarantees a consistent view of the database as of your transaction’s start. What it does not guarantee is that the writes you make are still compatible with reality by the time you commit. The classic failure pattern:

  • Two transactions read an overlapping set of rows.
  • Each takes an action based on what the other rows contain.
  • They write to different rows, so there is no write-write conflict to detect.
  • Both commit, and a system-level invariant is now violated.

The standard on-call scheduling example makes this concrete. The rule: at least one doctor must be on call at all times.

CREATE TABLE doctors (
    name    text PRIMARY KEY,
    on_call boolean NOT NULL
);

INSERT INTO doctors VALUES
    ('alice', true),
    ('bob',   true);

Now Alice and Bob both request shift changes at the same time, each in a REPEATABLE READ transaction:

-- Alice's transaction                       -- Bob's transaction
BEGIN ISOLATION LEVEL REPEATABLE READ;       BEGIN ISOLATION LEVEL REPEATABLE READ;

SELECT count(*) FROM doctors                 SELECT count(*) FROM doctors
WHERE on_call;                               WHERE on_call;
-- returns 2: safe to leave                  -- returns 2: safe to leave

UPDATE doctors SET on_call = false           UPDATE doctors SET on_call = false
WHERE name = 'alice';                        WHERE name = 'bob';

COMMIT;                                      COMMIT;

Both transactions see the snapshot where two doctors are on call, each concludes it is safe to step down, and neither touches the other’s row. There is no row-level conflict, so both commit. The result: zero doctors on call, and no error anywhere in the logs. The invariant “at least one on call” lived in application logic, spanning rows — and nothing in snapshot isolation’s job description covers invariants that span rows.

Notice how this differs from anomalies that do get caught. Had both transactions updated the same row, the second would block and then fail with a serialization error under Repeatable Read. A UNIQUE constraint defends the same way — duplicate inserts genuinely conflict at write time — but “at least one row satisfies this condition” cannot be expressed as a simple constraint.

The pattern shows up everywhere once you look for it: a batch job that checks a flag before deleting rows another job is also managing; a booking system where two users each cancel a meeting because the attendee list still looks fine to both. Any decision made from data your own transaction does not write is a candidate.

The Read Committed trap: lost updates

Before jumping to Serializable, it is worth flagging a related bug that bites at the default level. Under Read Committed, a read-modify-write sequence like this is not atomic:

BEGIN;
SELECT balance FROM accounts WHERE id = 42;   -- application reads 100
-- application computes 100 - 30 = 70
UPDATE accounts SET balance = 70
WHERE id = 42;
COMMIT;

If two transactions interleave here, the second overwrites the first’s result and the money vanishes. The safe form keeps the arithmetic inside the UPDATE, so PostgreSQL re-evaluates the row’s current value after any concurrent commit:

UPDATE accounts
SET balance = balance - 30
WHERE id = 42;

Alternatively, take an explicit lock on the rows you intend to modify:

BEGIN;
SELECT balance FROM accounts WHERE id = 42 FOR UPDATE;
-- compute and update here
COMMIT;

These fixes handle conflicts on rows you write. Write skew is harder precisely because the rows you read and the rows you write do not overlap — no lock, no constraint, and no in-place arithmetic can help.

Serializable: detection instead of prevention

PostgreSQL’s Serializable level uses Snapshot Isolation with additional predicate-lock-based monitoring — an approach usually abbreviated SSI. The key design choice: the predicate locks never block. They exist only so the database can detect, at commit time, that a dangerous read/write dependency formed between concurrent transactions. If the resulting schedule could not have come from running the transactions one after another in any order, one of them is aborted:

ERROR:  could not serialize access due to read/write dependencies among transactions

Re-run the doctor scenario at Serializable and exactly one of the two transactions commits; the other gets this error. The cost is a false-positive rate: some transactions are aborted that would have been perfectly fine, and every Serializable transaction must be wrapped in a retry loop. Treat the error — SQLSTATE 40001 — as a signal to run the transaction again, not as a failure:

package main

import (
	"context"
	"errors"
	"fmt"

	"github.com/jackc/pgx/v5"
	"github.com/jackc/pgx/v5/pgconn"
	"github.com/jackc/pgx/v5/pgxpool"
)

func withSerializableRetry(ctx context.Context, db *pgxpool.Pool,
	fn func(pgx.Tx) error,
) error {
	var lastErr error
	for attempt := 0; attempt < 5; attempt++ {
		err := doSerializableTx(ctx, db, fn)
		if err == nil {
			return nil
		}
		var pgErr *pgconn.PgError
		if errors.As(err, &pgErr) && pgErr.Code == "40001" {
			lastErr = err
			continue // serialization failure: retry from scratch
		}
		return err // anything else is a real error
	}
	return fmt.Errorf("transaction failed after 5 attempts: %w", lastErr)
}

func doSerializableTx(ctx context.Context, db *pgxpool.Pool,
	fn func(pgx.Tx) error,
) error {
	tx, err := db.Begin(ctx)
	if err != nil {
		return err
	}
	defer tx.Rollback(ctx)

	if _, err := tx.Exec(ctx,
		"SET TRANSACTION ISOLATION LEVEL SERIALIZABLE"); err != nil {
		return err
	}
	if err := fn(tx); err != nil {
		return err
	}
	return tx.Commit(ctx)
}

Two operational notes from the isolation level documentation matter here. First, data read inside a Serializable transaction is unreliable until the transaction commits — an email sent based on an uncommitted read is an email based on nothing, so keep external side effects after commit. Second, a transaction declared as both READ ONLY and DEFERRABLE waits for a snapshot guaranteed to be free of serialization risks before starting, which makes it a good fit for reporting queries.

A decision framework

  • Read-only analytics and reports: Repeatable Read gives you a consistent snapshot cheaply, and nothing skews if you write nothing.
  • Single-row read-modify-write: Read Committed is fine with in-place updates or SELECT FOR UPDATE.
  • Multi-row invariants enforced in application code: this is where write skew lives. Either move the invariant into a constraint that produces a write conflict, or run Serializable with retries.
  • High-contention hot paths: measure before adopting Serializable. Retries add latency variance, and under heavy contention the abort rate can dominate.

The broader lesson: isolation level names are a marketing layer over implementation-specific behavior. What your database promises is what its engine actually does at commit time, and the only way to reason about that is to know which anomaly your chosen level still permits. Write skew is the quiet one — no error, no lock wait, just an invariant that stopped being true while every transaction reported success.

Leave a Reply

Your email address will not be published. Required fields are marked *