DBMS · Transactions and Concurrency

Anomalies and Isolation Levels

JrCodex7 min readChapter 30 of 43

Jr Codex DBMS Notes

Level: Advanced Prerequisites: Chapter 1: Transactions in Depth Time to complete: ~25 minutes

Table of Contents

  1. The Four Classic Anomalies
  2. The Four Isolation Levels
  3. Write Skew
  4. What Your Database Actually Does
  5. Choosing a Level
  6. Summary & Next Steps

1. The Four Classic Anomalies

DIRTY READ
─────────────────────────────────────────
  Reading data another transaction has written but
  NOT committed.

    T1: UPDATE accounts SET bal = 500 WHERE id='A'
    T2: SELECT bal FROM accounts WHERE id='A'
        ──► reads 500
    T1: ROLLBACK
        ──► 500 never existed. T2 acted on a
            phantom value.

NON-REPEATABLE READ
─────────────────────────────────────────
  Reading the same ROW twice in one transaction
  and getting different values.

    T1: SELECT bal WHERE id='A'   ──► 300
    T2: UPDATE accounts SET bal=500 WHERE id='A'
        COMMIT
    T1: SELECT bal WHERE id='A'   ──► 500
        ──► T1's two reads disagree

PHANTOM READ
─────────────────────────────────────────
  Re-running the same QUERY and getting different
  ROWS, because another transaction inserted or
  deleted.

    T1: SELECT COUNT(*) WHERE city='Pune' ──► 3
    T2: INSERT a Pune student; COMMIT
    T1: SELECT COUNT(*) WHERE city='Pune' ──► 4

  Differs from non-repeatable read: no existing row
  changed. A NEW row appeared, so row-level locks
  cannot prevent it.

LOST UPDATE
─────────────────────────────────────────
  Two transactions read the same value, both
  compute from it, and the second overwrites the
  first.

    T1: read bal=100
    T2: read bal=100
    T1: write bal=100-60=40
    T2: write bal=100-50=50   ← T1's deduction gone
─────────────────────────────────────────

2. The Four Isolation Levels

The SQL Standard Table
─────────────────────────────────────────
  LEVEL             DIRTY  NON-REP  PHANTOM
  ─────────────────────────────────────────
  READ UNCOMMITTED   yes     yes      yes
  READ COMMITTED     no      yes      yes
  REPEATABLE READ    no      no       yes
  SERIALIZABLE       no      no       no

  "yes" = the anomaly is PERMITTED at that level.
─────────────────────────────────────────

SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
BEGIN;
    -- ...
COMMIT;
 
-- Or per session:
SET SESSION CHARACTERISTICS AS TRANSACTION ISOLATION LEVEL REPEATABLE READ;
Level by Level
─────────────────────────────────────────
  READ UNCOMMITTED
    Almost never used. PostgreSQL treats it as READ
    COMMITTED — it will not give you dirty reads
    even if you ask.

  READ COMMITTED
    Each STATEMENT sees a fresh snapshot of
    committed data. Two statements in the same
    transaction may see different data.
    ── the most common default

  REPEATABLE READ
    The whole TRANSACTION sees one snapshot, taken
    at its first read. Rows you read will not
    change under you.

  SERIALIZABLE
    Full correctness (Chapter 1). Transactions may
    be ABORTED to preserve it, so your code must
    handle retries.
─────────────────────────────────────────

3. Write Skew

The anomaly the standard's table does not mention, and the one that catches experienced developers.

The Scenario
─────────────────────────────────────────
  RULE: at least one doctor must always be on call.
  Currently Alice and Bob are both on call.

  Both try to go off call, simultaneously, under
  REPEATABLE READ:

    T1: SELECT COUNT(*) WHERE on_call ──► 2. Fine.
    T2: SELECT COUNT(*) WHERE on_call ──► 2. Fine.
    T1: UPDATE doctors SET on_call=false
          WHERE name='Alice'
    T2: UPDATE doctors SET on_call=false
          WHERE name='Bob'
    Both COMMIT.

  Now ZERO doctors are on call. The invariant is
  broken, and NO anomaly from Section 1 occurred.
─────────────────────────────────────────

Why It Slips Through
─────────────────────────────────────────
  Each transaction read data the OTHER then
  modified — but they wrote to DIFFERENT rows.

  No dirty read. No non-repeatable read. No
  phantom. No lost update. Every classic anomaly
  check passes.

  This is why REPEATABLE READ is not "almost
  serialisable". It is meaningfully weaker, and the
  gap is exactly this shape: a constraint spanning
  multiple rows.
─────────────────────────────────────────

-- FIX 1: SERIALIZABLE. The database detects the conflict and aborts one.
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
 
-- FIX 2: take an explicit lock so the reads conflict.
SELECT COUNT(*) FROM doctors WHERE on_call FOR UPDATE;
 
-- FIX 3: make the invariant a constraint the database can enforce alone.
-- e.g. a summary row with a CHECK, updated by both transactions.
The General Lesson
─────────────────────────────────────────
  Write skew happens whenever a decision is based
  on reading rows OTHER than the ones you write.

  Check-then-act across rows:
    "if no overlapping booking exists, insert one"
    "if the balance stays positive, withdraw"
    "if this username is free, claim it"

  All of these are write-skew-shaped, and all of
  them are wrong under REPEATABLE READ.
─────────────────────────────────────────

4. What Your Database Actually Does

The standard describes minimums. Real implementations differ, and the differences matter.

POSTGRESQL
─────────────────────────────────────────
  READ COMMITTED   default. MVCC snapshot per
                   STATEMENT.
  REPEATABLE READ  a true snapshot for the whole
                   transaction, so it also prevents
                   PHANTOMS — stronger than the
                   standard requires. Still allows
                   write skew.
  SERIALIZABLE     Serializable Snapshot Isolation
                   (SSI). Detects dangerous
                   patterns and aborts one
                   transaction with a
                   serialization_failure. YOU MUST
                   RETRY.

MYSQL / INNODB
─────────────────────────────────────────
  REPEATABLE READ  default. Uses NEXT-KEY LOCKING
                   (Chapter 3) which locks gaps
                   between index entries, so it
                   also prevents most phantoms.
  Note: plain SELECT is a non-locking snapshot
  read; SELECT ... FOR UPDATE is a locking read
  with different visibility. Mixing them surprises
  people.

ORACLE
─────────────────────────────────────────
  READ COMMITTED   default.
  "SERIALIZABLE"   is actually snapshot isolation —
                   it permits WRITE SKEW despite
                   the name. There is no true
                   serialisable level.
─────────────────────────────────────────

The Practical Warning
─────────────────────────────────────────
  Do not port concurrency assumptions between
  databases.

  Code correct under MySQL's REPEATABLE READ can be
  wrong under PostgreSQL's, and code correct under
  Oracle's "SERIALIZABLE" can be wrong under
  PostgreSQL's actual serialisable.

  Read your database's documentation for the level
  you are on. This is one of very few places where
  "it works the same everywhere" is badly false.
─────────────────────────────────────────

5. Choosing a Level

Decision Guide
─────────────────────────────────────────
  Reads that tolerate slight staleness —
  dashboards, listings, search results
      ──► READ COMMITTED. The default, and fine.

  A report that must be internally consistent
  across many queries
      ──► REPEATABLE READ. One snapshot, so totals
          agree with details.

  Money, inventory, booking, or any multi-row
  invariant
      ──► SERIALIZABLE, and write the retry loop.

  A single-row check-then-act
      ──► SELECT ... FOR UPDATE at READ COMMITTED
          is usually enough and cheaper.
─────────────────────────────────────────

import psycopg
from psycopg.errors import SerializationFailure
import time, random
 
def run_serialisable(conn_factory, work, max_attempts=5):
    """SERIALIZABLE transactions CAN be aborted. Retrying is mandatory, not optional."""
    for attempt in range(max_attempts):
        try:
            with conn_factory() as conn:
                conn.isolation_level = psycopg.IsolationLevel.SERIALIZABLE
                with conn.transaction():
                    return work(conn)
        except SerializationFailure:
            if attempt == max_attempts - 1:
                raise
            time.sleep((2 ** attempt) * 0.01 + random.random() * 0.01)   # backoff + jitter
The Rule That Comes With SERIALIZABLE
─────────────────────────────────────────
  If you set SERIALIZABLE and do not write a retry
  loop, you have not made your code correct. You
  have made it fail differently.

  Serialisation failures are NORMAL operation under
  contention, not exceptional errors.
─────────────────────────────────────────

6. Summary & Next Steps

Key Takeaways

  • The four classic anomalies are dirty read, non-repeatable read, phantom read and lost update, and each isolation level permits a specific subset.
  • Write skew is not in the standard's table: it occurs when transactions read rows other than the ones they write, and it defeats REPEATABLE READ entirely.
  • Real databases deviate from the standard in both directions — PostgreSQL's REPEATABLE READ is stronger than required, Oracle's SERIALIZABLE is weaker than its name.
  • SERIALIZABLE aborts transactions to preserve correctness, so adopting it without a retry loop replaces one bug with another.

Concept Check

  1. What distinguishes a phantom read from a non-repeatable read, and why does that distinction matter for locking?
  2. Explain why the on-call doctors example breaks under REPEATABLE READ despite no classic anomaly occurring.
  3. Why is "we set SERIALIZABLE, so we are safe" incomplete as a statement?

Next Chapter

→ Chapter 3: Locking and Two-Phase Locking

Jr Codex — 1-on-1 Personalized Coaching | Back to Module Index | Back to DBMS Index