Isolation levels and anomalies

• The first three are about reads. The last three corrupt data that the application then writes.
• Lost update and write skew happen at the default level, READ COMMITTED.

An anomaly is a result that no one-at-a-time order of the same transactions could give.

• READ UNCOMMITTED behaves as READ COMMITTED. The standard allows dirty reads there; Postgres never shows them.
• The standard allows phantoms at REPEATABLE READ. Postgres prevents them, because the whole transaction reads one snapshot.
• REPEATABLE READ and SERIALIZABLE stop a lost update with error 40001. The application must retry.
• Only SERIALIZABLE stops write skew. It uses Serializable Snapshot Isolation, which aborts a transaction instead of blocking it.

In Postgres, REPEATABLE READ is snapshot isolation. It stops phantoms and lost updates, but it lets write skew through.

• A snapshot is the set of transactions that had committed when it was taken.
• Readers never block writers, and writers never block readers. Each reader sees its snapshot.
• Two writers to one row still queue on the row lock, at every level.

READ COMMITTED takes a new snapshot for each statement. REPEATABLE READ keeps the first one.

The lab sends each statement from two real connections, in this order. A shaded lane is waiting on a lock, read from pg_locks.

At READ COMMITTED the second UPDATE waits, then overwrites. At REPEATABLE READ the same UPDATE waits, then fails with 40001.

Postgres gives me snapshots, row locks, constraints, and serializable checks that abort instead of block. I pick the cheapest one that protects the invariant.

• Rows known: the decision reads rows you can name and lock.
• Rule fits: the invariant is "no two rows with the same key", a row check, or "no overlap".
• Low contention: few transactions touch the same rows, so few retries.

One row: an atomic or conditional update. A read-then-write decision: FOR UPDATE. A rule that fits a constraint: the constraint. The rest: SERIALIZABLE with retry.

A fix either makes the second session wait and re-read, or makes Postgres refuse the bad write.

run(transaction):
  REPEAT up to N times:
    BEGIN ISOLATION LEVEL SERIALIZABLE1
      read what the decision needs2
      decide, then write
    COMMIT
    IF it committed: RETURN
    IF error is 40001 or 40P013:      // safe to run again
      wait a few random ms4
    ELSE: RETURN the error
  RETURN gave up5

// Serializable runs fn in a SERIALIZABLE transaction. When Postgres aborts it to keep the
// result serializable, it runs fn again from the start. It returns the number of retries.
func Serializable(ctx context.Context, db *pgxpool.Pool, maxAttempts int, fn func(pgx.Tx) error) (int, error) {
  for attempt := 1; ; attempt++ {
    err := pgx.BeginTxFunc(ctx, db, pgx.TxOptions{IsoLevel: pgx.Serializable}, fn)
    if err == nil {
      return attempt - 1, nil
    }
    if !Retryable(err) || attempt == maxAttempts {
      return attempt - 1, fmt.Errorf("serializable transaction, attempt %d: %w", attempt, err)
    }
    // Wait a random time that grows with each attempt, up to 10 ms, so the same pair of
    // transactions does not collide again at once.
    time.Sleep(time.Duration(rand.Int64N(int64(min(attempt, 10)) * int64(time.Millisecond))))
  }
}

Under SERIALIZABLE I wrap the whole transaction in a retry loop. A 40001 is normal there, not a bug.

• SIREAD locks never block. They only record what each transaction read.
• In the recorded run, both UPDATEs succeed. The second COMMIT fails: "Canceled on identification as a pivot, during commit attempt."
• The retry reads 1 doctor on call, so bob stays.

SERIALIZABLE tracks reads with SIREAD locks. Two transactions that each read what the other wrote form a cycle, so one commit fails.

The lab transfer reads two balances and writes both. FOR UPDATE runs at READ COMMITTED and locks rows in id order. Do not start at SERIALIZABLE for everything: every caller then needs a retry loop.

I start at READ COMMITTED with atomic updates and constraints. I add locks where a decision reads rows, and SERIALIZABLE only where an invariant spans rows and contention is low.