Backend / Databases / SQL / 08_transactions_isolation.md

Transaction isolation levels

Updated 6 interview angles 9 min read source
On this page10
  1. The levels in PostgreSQL
  2. How a snapshot works
  3. READ COMMITTED: what a fresh snapshot per statement allows
  4. REPEATABLE READ: write skew
  5. SERIALIZABLE: how SSI catches it
  6. Choosing a level, or locking instead
  7. Where it breaks in production
  8. The trade not taken
  9. Related
  10. Interview angle

Transaction isolation levels

Article.

Isolation levels decide which interleavings of concurrent transactions you are allowed to observe. The standard defines them by the anomalies they forbid; each database implements them differently, and PostgreSQL’s implementation, snapshots, explains every row of the table better than the table does.

Does REPEATABLE READ prevent a lost update? In PostgreSQL yes, with an error you must handle; in MySQL no. Every result below comes from a two-session psycopg 3 script against PostgreSQL 18.4. ACID as a whole is in Transactions and ACID; the cross-database catalogue of anomalies in Isolation anomalies across databases.

The levels in PostgreSQL

Level Snapshot Still allows
READ UNCOMMITTED same as READ COMMITTED (no dirty reads in PG)
READ COMMITTED (default) new one per statement non-repeatable read, read skew, lost update, phantoms
REPEATABLE READ one per transaction write skew
SERIALIZABLE one per transaction, plus SSI nothing: equals some serial order
sql 
BEGIN ISOLATION LEVEL SERIALIZABLE;   -- or SET TRANSACTION ... as the first statement

PostgreSQL’s REPEATABLE READ is stronger than the standard asks: snapshot isolation also removes phantoms. Its SERIALIZABLE exists for the one anomaly snapshots allow, write skew.

How a snapshot works

A snapshot is three numbers: the oldest transaction still running (xmin), the next id to be assigned (xmax), and the list in progress between them. Each row version carries the id that created it and the id that deleted it (PostgreSQL architecture); a row is visible if its creator committed before the snapshot and its deleter did not.

sql 
SELECT pg_current_snapshot();          -- 224397:224397:  (xmin:xmax:in-progress)

The snapshot is taken by the first statement, not by BEGIN:

text 
BEGIN ISOLATION LEVEL REPEATABLE READ;
    -- another session commits bal = 555
SELECT bal ...     -> 555      the first query took the snapshot
    -- another session commits bal = 666
SELECT bal ...     -> 555      the snapshot stays fixed

READ COMMITTED takes a fresh snapshot for every statement; REPEATABLE READ and SERIALIZABLE keep the first one until the end.

READ COMMITTED: what a fresh snapshot per statement allows

Non-repeatable reads and read skew

text 
same row, same transaction:      100 then 50
transfer of 50 from account 1 to 2, read between the two statements:
  reader saw 100 + 150 = 250     (true total 200)
same reader under REPEATABLE READ: 100 + 100 = 200

The second is read skew: each value was committed, but they come from different moments, so a report or a balance check sees money that does not exist. Any multi-statement read that must be consistent needs REPEATABLE READ.

Lost update

python 
bal = cur.execute("SELECT bal FROM acct WHERE id = 1").fetchone()[0]
cur.execute("UPDATE acct SET bal = %s WHERE id = 1", (bal + 10,))

Two sessions running this concurrently from 100:

Level Second writer Final (want 120)
READ COMMITTED committed 110: one update lost
REPEATABLE READ 40001 concurrent update 110, and an error
RC, atomic + 10 waited, then applied 120

Under READ COMMITTED the second write silently overwrote the first. Under REPEATABLE READ PostgreSQL applies first-updater-wins: the second transaction’s row was changed after its snapshot, so it fails with could not serialize access due to concurrent update and must be retried. MySQL’s REPEATABLE READ instead updates the latest version and loses it silently (Isolation anomalies across databases).

The re-check after a wait

An atomic UPDATE ... SET bal = bal + 10 worked under READ COMMITTED because an UPDATE that waits for a locked row re-reads the new version of that row and re-evaluates its WHERE on it. Only on that row: it does not re-run the whole query. The documentation’s own example:

text 
session 1: UPDATE website SET hits = hits + 1;     -- 9 -> 10, 10 -> 11, not committed
session 2: DELETE FROM website WHERE hits = 10;    -- waits for session 1
session 1: COMMIT;
session 2: deleted 0 rows; table now [10, 11]

A row with hits = 10 existed before and after, and nothing was deleted: the old 10 had become 11 when re-checked, and the new 10 was not in the statement’s snapshot. Complex WHERE clauses on contended rows can behave like this at READ COMMITTED.

REPEATABLE READ: write skew

The doctors on call

Two doctors are on call; each may go off call if someone else is still on:

sql 
SELECT count(*) FROM oncall WHERE on_call;                 -- both see 2
UPDATE oncall SET on_call = false WHERE doctor = 'alice';  -- bob: his own row
COMMIT;
Level Second COMMIT Doctors on call
REPEATABLE READ committed 0
SERIALIZABLE 40001 read/write dependencies 1

Each transaction updated a different row, so there was no write conflict to detect; each decided on a snapshot the other made false. That is write skew, and no snapshot can see it.

SERIALIZABLE: how SSI catches it

Predicate tracking

Serializable Snapshot Isolation runs like REPEATABLE READ and additionally records what each transaction read, as SIReadLock entries:

text 
pg_locks during the serializable SELECT:
  ('relation', 'SIReadLock', 'oncall')

These are not locks anyone waits on; they let the server notice a dangerous pattern, two transactions each reading something the other then wrote, and cancel one with could not serialize access due to read/write dependencies among transactions. The cancellation can come at any statement or at COMMIT, always before the effects become visible.

False positives and retries

Two consequences:

  • False positives. The lock above covers the whole table because the query was a sequential scan; with an index it would cover only the rows or pages read. Coarser locks mean more conflicts that were not real anomalies, so missing indexes raise the abort rate.
  • Retries are mandatory, for the whole transaction:
python 
def run_serializable(engine, work, attempts=5):
    for attempt in range(attempts):
        try:
            with Session(engine) as s, s.begin():
                return work(s)               # re-reads everything on retry
        except OperationalError as e:
            code = getattr(e.orig, "sqlstate", None)
            if code not in ("40001", "40P01") or attempt == attempts - 1:
                raise
            time.sleep(0.01 * 2 ** attempt * random.random())

Use the error’s sqlstate (psycopg 3; pgcode in psycopg2), not string matching. Retry deadlocks (40P01) the same way. Never retry only the failed statement: the decision was made on reads that are now stale.

Choosing a level, or locking instead

The booking that FOR UPDATE does not protect

sql 
SELECT * FROM booking WHERE slot = 5 FOR UPDATE;   -- no row yet: locks nothing
INSERT INTO booking VALUES (5, 'ann');
Level Second COMMIT Bookings for slot 5
RC + FOR UPDATE committed 2
SERIALIZABLE 40001 1

FOR UPDATE locks rows that exist. When the conflict is about a row that does not exist yet, use a UNIQUE (slot) constraint, an exclusion constraint for ranges (Index types), a lock on a parent row that does exist (the slot’s own row), or SERIALIZABLE.

Row lock modes

Clause Blocks
FOR UPDATE every other row lock and update
FOR NO KEY UPDATE the same, except foreign-key checks
FOR SHARE updates, not other readers’ locks
FOR KEY SHARE only key changes and deletes

Foreign-key checks take FOR KEY SHARE on the parent. An UPDATE that does not change a key takes FOR NO KEY UPDATE, so inserting child rows does not block on it; an explicit SELECT ... FOR UPDATE on the parent does block those inserts. Prefer FOR NO KEY UPDATE when you lock a row to update non-key columns. SKIP LOCKED turns a table into a work queue; NOWAIT fails instead of waiting.

Where it breaks in production

Retry storms

A hot row under SERIALIZABLE or REPEATABLE READ: every retry conflicts again, latency climbs, and the abort rate feeds itself. Count 40001 and 40P01 (%e in log_line_prefix, or application metrics); back off with jitter; reduce contention on the row rather than adding attempts.

Long REPEATABLE READ reports

A report at REPEATABLE READ keeps one snapshot for its whole run, and for that time VACUUM cannot remove anything newer (PostgreSQL architecture). For a long consistent read, BEGIN ISOLATION LEVEL SERIALIZABLE READ ONLY DEFERRABLE waits for a safe snapshot and then can never be cancelled by SSI; run it on a replica only at REPEATABLE READ, because standbys refuse SERIALIZABLE.

The level set too late, or leaking

SET TRANSACTION ISOLATION LEVEL must be the first statement of the transaction, and an ORM may already have run one. SET SESSION CHARACTERISTICS under PgBouncer transaction pooling applies to whichever client gets that server connection next. Set the level per transaction, in the driver or ORM: psycopg’s conn.isolation_level, SQLAlchemy’s execution_options(isolation_level=...), Django’s database OPTIONS.

Deadlocks

Two transactions lock rows in opposite order; after deadlock_timeout (1 s) one is cancelled with 40P01. Lock in a consistent order (sort ids before FOR UPDATE), turn on log_lock_waits, and use pg_blocking_pids() to see who waits on whom.

The trade not taken

The alternative to SERIALIZABLE everywhere is READ COMMITTED plus hand-placed protection: atomic updates, row locks, unique and exclusion constraints, advisory locks (PostgreSQL Advisory Locks). It is faster and never aborts, but every invariant needs its own correct lock, and the booking case shows how easily one is missed.

SERIALIZABLE protects invariants nobody thought about, at the cost of predicate tracking and retries. A common compromise: READ COMMITTED by default, SERIALIZABLE for the few transactions that enforce cross-row rules, with a retry wrapper around them.

Interview angle 6

  • “What are the levels, and what does each prevent?” - READ COMMITTED: a snapshot per statement, so no dirty reads but non-repeatable reads, read skew and lost updates. REPEATABLE READ in PostgreSQL: one snapshot, so no phantoms, and lost updates raise an error; write skew remains. SERIALIZABLE: SSI aborts anything not equivalent to a serial order.
  • “Does REPEATABLE READ prevent a lost update?” - in PostgreSQL the second writer fails with 40001 ... concurrent update, so yes, if you retry. MySQL’s REPEATABLE READ updates the latest version and loses it.
    • Follow-up: “And at READ COMMITTED?” - a read in Python then a write lost one of two increments; SET bal = bal + 10 did not, because the waiting update re-reads the row.
  • “What is write skew?” - two transactions read overlapping data and write different rows, each invalidating the other’s decision: both doctors went off call under REPEATABLE READ. SERIALIZABLE cancelled the second.
  • “Two users book the last slot with SELECT ... FOR UPDATE and still double-book. Why?” - there was no row to lock. Fix with a unique or exclusion constraint, a lock on an existing parent row, or SERIALIZABLE.
  • “Why not run everything at SERIALIZABLE?” - aborts and retries under contention, more of them when queries scan whole tables, and it is not available on replicas.
    • Follow-up: “How do you write the retry?” - catch SQLSTATE 40001 and 40P01, re-run the whole transaction including its reads, back off with jitter, cap the attempts.
  • “When is the snapshot taken?” - at the first statement, not at BEGIN.
Previous
N+1 query problem