Transactions and isolation levels

ACID is the contract a transaction makes. Isolation levels control how strictly one transaction is shielded from concurrent ones.

ACID

• Atomicity — all statements in a transaction commit together or none do.
• Consistency — the transaction moves the DB from one valid state to another (constraints hold).
• Isolation — concurrent transactions don’t see each other’s intermediate state.
• Durability — once committed, changes survive crashes (typically via WAL — write-ahead log).

The four standard isolation levels

Note: PostgreSQL’s REPEATABLE READ uses snapshot isolation, which actually prevents phantoms too. The standard doesn’t require this.

The three phenomena

Dirty read — read uncommitted data that may roll back.

-- T1: UPDATE accounts SET balance = 0 WHERE id = 1;  -- not committed yet
-- T2: SELECT balance FROM accounts WHERE id = 1;     -- sees 0
-- T1: ROLLBACK;                                       -- T2 saw a value that never existed

Non-repeatable read — same row read twice in one transaction returns different values.

-- T1: SELECT balance FROM accounts WHERE id = 1;     -- 100
-- T2: UPDATE accounts SET balance = 50 WHERE id = 1; COMMIT;
-- T1: SELECT balance FROM accounts WHERE id = 1;     -- 50  ← changed mid-transaction

Phantom read — same query returns different sets of rows.

-- T1: SELECT COUNT(*) FROM orders WHERE user_id = 7; -- 3
-- T2: INSERT INTO orders (user_id, ...) VALUES (7, ...); COMMIT;
-- T1: SELECT COUNT(*) FROM orders WHERE user_id = 7; -- 4  ← new row appeared

Setting the level

-- Per transaction
BEGIN;
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
-- ...
COMMIT;

-- Session-wide (PG)
SET SESSION CHARACTERISTICS AS TRANSACTION ISOLATION LEVEL REPEATABLE READ;

In SQLAlchemy:

from sqlalchemy import create_engine
engine = create_engine(url, isolation_level="SERIALIZABLE")
# or per-connection:
with engine.connect().execution_options(isolation_level="REPEATABLE READ") as conn:
    ...

Snapshot isolation vs SERIALIZABLE

PG’s REPEATABLE READ gives snapshot isolation: every read in the transaction sees the DB as of transaction start. Fast, but suffers from write skew — two transactions read disjoint rows, both decide to update based on a constraint, and the constraint is violated after both commit.

-- Doctor on-call constraint: at least one doctor must be on call.
-- Two doctors run "if total > 1, set me off-call" simultaneously under REPEATABLE READ.
-- Both see total = 2, both update, constraint broken.

PG’s SERIALIZABLE detects this via SSI (serializable snapshot isolation) and aborts one transaction with 40001 serialization_failure. Application must retry.

The retry pattern

Under SERIALIZABLE (or in any optimistic-concurrency scheme), assume any write transaction can fail with a serialization error and code retries:

from time import sleep
import random

for attempt in range(5):
    try:
        with session.begin():
            # work
            ...
        break
    except OperationalError as e:
        if "40001" not in str(e.orig):
            raise
        sleep(0.05 * (2 ** attempt) + random.random() * 0.01)