Write skew under REPEATABLE READ

Both transactions commit and nobody is on call. PostgreSQL's REPEATABLE READ is snapshot isolation: each transaction reads its own snapshot, sees two doctors on call, and updates a different row, so there is no write-write conflict to detect. This anomaly is write skew. SERIALIZABLE catches it and aborts one transaction with SQLSTATE 40001; the app must retry it.

Snapshot isolation only detects two transactions writing the same row. Write skew writes different rows based on a shared read.

PostgreSQL's SERIALIZABLE (serializable snapshot isolation, SSI) tracks read/write dependencies and aborts one transaction of a dangerous cycle.

Alternatives when you can't use SERIALIZABLE: lock the rows you read with SELECT ... FOR UPDATE, or turn the rule into a constraint the database enforces.

We ran this exact scenario on PostgreSQL 17: READ COMMITTED and REPEATABLE READ both left 0 doctors on call; SERIALIZABLE left 1.

import psycopg
from psycopg import IsolationLevel
from psycopg.rows import TupleRow


def reset() -> None:
    with psycopg.connect(autocommit=True) as c:
        c.execute("DROP TABLE IF EXISTS doctors")
        c.execute(
            "CREATE TABLE doctors"
            " (name text PRIMARY KEY, on_call bool NOT NULL)"
        )
        c.execute(
            "INSERT INTO doctors VALUES ('ana', true), ('ben', true)"
        )


def on_call(conn: psycopg.Connection[TupleRow]) -> int:
    row = conn.execute(
        "SELECT count(*) FROM doctors WHERE on_call"
    ).fetchone()
    assert row is not None
    n: int = row[0]
    return n


def both_go_off_call(level: IsolationLevel) -> None:
    reset()
    a, b = psycopg.connect(), psycopg.connect()
    a.isolation_level = b.isolation_level = level
    # Both check "someone else stays on call"...
    if on_call(a) >= 2 and on_call(b) >= 2:
        # ...then each takes only itself off call.
        a.execute(
            "UPDATE doctors SET on_call = false WHERE name = 'ana'"
        )
        b.execute(
            "UPDATE doctors SET on_call = false WHERE name = 'ben'"
        )
    results = []
    for conn in (a, b):
        try:
            conn.commit()
            results.append("commit")
        except psycopg.errors.SerializationFailure:
            results.append("40001")
        conn.close()
    with psycopg.connect() as c:
        print(level.name, results, "on call:", on_call(c))


for lvl in (
    IsolationLevel.READ_COMMITTED,
    IsolationLevel.REPEATABLE_READ,
    IsolationLevel.SERIALIZABLE,
):
    both_go_off_call(lvl)

Common mistakes

• Believing REPEATABLE READ prevents every anomaly except phantoms, as the SQL standard table suggests.
• Switching to SERIALIZABLE without adding a retry loop for 40001.
• Fixing it with a check in application code, which reads the same stale snapshot.

Name the anomaly, explain why snapshot isolation misses it (different rows, shared read), and give two fixes: SERIALIZABLE plus retries, or explicit locks or constraints.

Follow-up questions

Think of your answer first, then open the hint.

1. Why doesn't FOR UPDATE on the count query alone fix it?

   Show hint

   Aggregates can't take row locks; lock the doctor rows you read instead (SELECT ... FOR UPDATE on the rows).

2. Where else does write skew appear?

   Show hint

   Booking the last seat in two different rows, double-spending across two accounts, unique usernames checked in app code.