Isolation Levels

TL;DR

The SQL standard defines four isolation levels by which anomalies they prevent:

In practice, every database implements these slightly differently:

• Postgres “Read Committed” is the default. Reads see the latest committed snapshot at each statement.
• Postgres “Repeatable Read” is actually snapshot isolation — stronger than the standard’s repeatable read; it also prevents phantoms.
• Postgres “Serializable” uses SSI (Serializable Snapshot Isolation): snapshot isolation plus runtime checks; aborts transactions that would have produced a non-serializable result.
• Most other engines (MySQL InnoDB, Snowflake, SQL Server) use names that mean different things again. Read the vendor docs every time.

The practical advice: default to Read Committed, escalate to Serializable when correctness against concurrent writers matters (payments, inventory, anything money-related). Use snapshot/Repeatable Read for analytical queries that need a consistent view.

The four anomalies, with examples

Dirty read

T1 reads a value T2 wrote but hasn’t committed; T2 then rolls back. T1 saw a value that never existed.

Prevented at Read Committed and above. Postgres never allows dirty reads even at Read Uncommitted (it implements Read Uncommitted as Read Committed; the level exists for syntactic compatibility).

Non-repeatable read

T1 reads row R; T2 updates R and commits; T1 reads R again, gets a different value.

-- T1
BEGIN;
SELECT balance FROM accounts WHERE id = 1;   -- 100
-- (T2 commits an UPDATE setting balance = 50)
SELECT balance FROM accounts WHERE id = 1;   -- 50  ← non-repeatable
COMMIT;

Prevented at Repeatable Read and above. T1 in repeatable read would see 100 both times.

Phantom read

T1 runs a range query; T2 inserts a row matching the range and commits; T1 re-runs the query, sees the new row.

-- T1
BEGIN;
SELECT COUNT(*) FROM orders WHERE total > 100;   -- 5
-- (T2 INSERTs a new order with total = 200, COMMITs)
SELECT COUNT(*) FROM orders WHERE total > 100;   -- 6  ← phantom
COMMIT;

The SQL standard says Repeatable Read may allow phantoms; Serializable prevents them. Postgres’ Repeatable Read (= snapshot isolation) prevents phantoms because it operates on a snapshot taken at transaction start.

Lost update

Two transactions read a value, both compute a new value based on the read, both write — the second write overwrites the first without incorporating it.

-- T1                                  -- T2
BEGIN;                                 BEGIN;
SELECT balance FROM accounts WHERE id=1;   -- 100
                                       SELECT balance FROM accounts WHERE id=1;   -- 100
UPDATE accounts SET balance = 100 - 30 WHERE id=1;
COMMIT;
                                       UPDATE accounts SET balance = 100 - 50 WHERE id=1;
                                       COMMIT;
-- Final: 50, not 20. T1's deduction is lost.

Read Committed and standard Repeatable Read both allow this. Snapshot isolation may detect it (Postgres aborts T2 in Repeatable Read with “could not serialize access”). Serializable always prevents it.

The other fix is SELECT ... FOR UPDATE, which holds a row lock until commit, serializing the read-modify-write.

What Postgres actually does

SET TRANSACTION ISOLATION LEVEL { READ COMMITTED | REPEATABLE READ | SERIALIZABLE };

The aborts in Repeatable Read and Serializable are the price you pay for correctness. Your application must catch serialization_failure (SQLSTATE 40001) and retry. Code that doesn’t retry will silently fail under contention.

A worked example — write skew (the SSI case)

Write skew is the canonical “snapshot isolation isn’t enough” anomaly.

Scenario: a hospital requires at least one doctor on call. Two doctors (Alice and Bob) are both on call. Each tries to remove themselves simultaneously.

-- T1 (Alice)                              -- T2 (Bob)
BEGIN ISOLATION LEVEL REPEATABLE READ;     BEGIN ISOLATION LEVEL REPEATABLE READ;
SELECT count(*) FROM oncall WHERE on_duty;   -- 2
                                           SELECT count(*) FROM oncall WHERE on_duty;   -- 2
-- Alice's check: 2 > 1, safe to leave.
                                           -- Bob's check: 2 > 1, safe to leave.
UPDATE oncall SET on_duty = false WHERE name='Alice';
                                           UPDATE oncall SET on_duty = false WHERE name='Bob';
COMMIT;
                                           COMMIT;
-- Final: zero doctors on call. Invariant violated.

Both transactions read a consistent snapshot. Neither modifies a row the other read. Snapshot isolation allows this. Serializable (SSI) detects the conflict and aborts one transaction.

The fix at Repeatable Read: SELECT ... FOR UPDATE, taking row locks on all rows both transactions read, so they serialize. The fix at Serializable: nothing — the engine handles it; the app catches the abort and retries.

Cost of higher levels

The correct level for your workload is usually Read Committed unless you have an invariant that snapshot isolation can’t preserve (write skew exists, lost updates matter). For ML feature pipelines, Read Committed is almost always fine — features are append-only or upsert by key.

Vendor differences (the real frustration)

The lesson: don’t trust the level name. Test what your DB actually does. The Hermitage repo (linked in resources) is exactly this kind of test suite — runs known race patterns at each level and shows what each engine allows.

Common pitfalls

• Not handling serialization_failure retries. Repeatable Read and Serializable can abort; your code must retry with backoff. Frameworks like SQLAlchemy let you wrap retries in a decorator.
• Assuming “Repeatable Read” means the same in every DB. It doesn’t.
• Long transactions in Serializable. Conflict detection holds onto predicate-locks for the transaction’s lifetime; long transactions cause more aborts in others.
• Locks held across application logic. A SELECT ... FOR UPDATE followed by a Python network call is a recipe for lock contention. Decide quickly, COMMIT quickly.
• Mixing isolation levels in one application. Possible but confusing. Pick one default, escalate per-transaction when needed.

Production patterns for ML

1. Read Committed for analytics, Snapshot for reproducible reports. Daily/weekly metrics often want a single snapshot for the whole run:

BEGIN ISOLATION LEVEL REPEATABLE READ READ ONLY;
SELECT ... FROM events ...;     -- all queries see the same snapshot
SELECT ... FROM users ...;
COMMIT;

Without this, query 2 might see writes that happened during query 1’s runtime — different denominators, drifting numbers.

2. Serializable for correctness-critical updates. Inventory deductions, quota tracking, anti-fraud counters — anywhere a “read-then-decide-then-write” race could violate an invariant — should be Serializable with retry on abort:

for attempt in range(5):
    try:
        with conn.transaction(isolation_level='SERIALIZABLE'):
            conn.execute("SELECT count(*) FROM oncall WHERE on_duty")
            # ... decision logic ...
            conn.execute("UPDATE oncall SET on_duty = false WHERE name = $1", me)
        break
    except SerializationFailure:
        time.sleep(0.05 * (2 ** attempt))   # exponential backoff

Resources

• PostgreSQL — transaction isolation — postgresql.org/docs
• Hermitage — isolation level test suite — github.com/ept/hermitage — Kleppmann’s repo showing what each DB actually does.
• Jepsen analyses — jepsen.io/analyses — empirical proofs that vendor isolation claims often lie.
• Designing Data-Intensive Applications, ch. 7 — dataintensive.net
• PostgreSQL documentation — postgresql.org/docs