Transactions and isolation levels

Why this matters

This is the single most misunderstood topic in databases. Even the SQL standard is wrong about it. Most engineers have a wrong mental model — including ones with 10 years of experience. Getting this right is a senior-engineer differentiator.

If you've ever:

• Had a "race condition" that you couldn't reproduce
• Seen your code "occasionally" allow two users to register the same username
• Wondered why SELECT FOR UPDATE exists
• Been told to "use Serializable" and not known why

...this section is for you.

ACID — the actual meanings

The "C" is the most-misunderstood. Consistency in ACID just means: if your transaction code is correct (preserves invariants), the database won't break them. The database doesn't define what consistency means — your application does. This is unlike "consistency" in CAP theorem, which means linearizability of operations across replicas.

The isolation levels — what each actually prevents

The SQL standard defines four isolation levels by which anomalies they prevent. The standard is incomplete; real implementations differ.

The anomalies in plain English with concrete examples

Dirty read: Reading uncommitted data.

T1: BEGIN; UPDATE accounts SET balance = 100 WHERE id = 1;
T2: SELECT balance FROM accounts WHERE id = 1;  -- sees 100
T1: ROLLBACK;
-- T2 acted on data that never existed.

Almost never allowed in production databases. Most don't even support this isolation level by default.

Non-repeatable read: A row's value changes within a transaction.

T1: BEGIN; SELECT balance FROM accounts WHERE id = 1;  -- 1000
T2: BEGIN; UPDATE accounts SET balance = 500 WHERE id = 1; COMMIT;
T1: SELECT balance FROM accounts WHERE id = 1;  -- 500 (different!)

If T1 was, say, generating a report, the report has inconsistent numbers.

Phantom read: A range query returns different rows on repeat.

T1: BEGIN; SELECT count(*) FROM orders WHERE amount > 100;  -- 5
T2: INSERT INTO orders (amount) VALUES (150); COMMIT;
T1: SELECT count(*) FROM orders WHERE amount > 100;  -- 6

The range result set "grew" between reads.

Lost update: Two transactions reading the same value and both writing back lose one update.

T1: BEGIN; SELECT counter FROM stats WHERE id = 1;  -- 10
T2: BEGIN; SELECT counter FROM stats WHERE id = 1;  -- 10
T1: UPDATE stats SET counter = 11 WHERE id = 1; COMMIT;
T2: UPDATE stats SET counter = 11 WHERE id = 1; COMMIT;  -- T1's increment lost

Two reads of 10, two writes of 11. Net: one increment instead of two. This is the bug that bites every developer who writes "read-modify-write" code at Read Committed isolation without locking.

Write skew: Two transactions each read a set of rows, check an invariant, then update different rows in a way that violates the invariant when combined.

Setup: on_call doctors = {Alice, Bob}; invariant: ≥1 must be on call.

T1 (Alice marks self off-call):
  BEGIN;
  SELECT count(*) FROM doctors WHERE on_call = true;  -- 2
  -- 2 ≥ 1, so it's safe to remove me
  UPDATE doctors SET on_call = false WHERE id = 'alice';
  COMMIT;

T2 (Bob marks self off-call, CONCURRENT):
  BEGIN;
  SELECT count(*) FROM doctors WHERE on_call = true;  -- 2 (snapshot from before T1)
  -- 2 ≥ 1, so it's safe
  UPDATE doctors SET on_call = false WHERE id = 'bob';
  COMMIT;

Result: zero doctors on call. Invariant violated.

Critically: Snapshot Isolation does NOT prevent write skew. Each transaction saw a consistent snapshot showing the invariant was satisfied. Neither read each other's update.

Real-world write-skew scenarios

• Bank account with co-owners: each spouse withdraws, each checks balance is sufficient, both succeed, account overdrafts.
• Inventory reservation: two carts add the last item; both see "1 in stock"; both succeed; oversold.
• Username registration: two requests register the same username; both check "username doesn't exist"; both insert.
• Document edit lock: two editors see "lock not held"; both acquire lock simultaneously.
• Calendar booking: two requests book overlapping slots; each checks "no conflict"; both succeed; double-booked.

What real databases actually do

Postgres

• Read Committed (default): true Read Committed. No locks on reads; statement-level snapshots. Lost updates can happen; use SELECT FOR UPDATE to prevent.
• Repeatable Read: actually Snapshot Isolation. Stronger than the SQL standard's RR — prevents phantoms too via the snapshot. Doesn't prevent write skew.
• Serializable: Serializable Snapshot Isolation (SSI). The DB tracks read-write dependencies between concurrent transactions; if it detects a "dangerous" cycle, it aborts one. Provides true serializability at modest cost (typically 1.5–3× slower than RR). Write-skew prevention without coarse locking.

MySQL InnoDB

• Read Committed: similar to Postgres.
• Repeatable Read (default): uses next-key locks (gap + record locks) to prevent phantoms in many but not all cases. Stronger than SQL standard RR; different from Postgres SI.
• Serializable: full table/range locks. Slow; rarely used in production.

Oracle

• Only "Read Committed" and "Serializable" levels exposed.
• "Serializable" in Oracle is actually Snapshot Isolation — does NOT prevent write skew. Watch out. This caused a famous bug at TPC-C benchmarks where Oracle claimed Serializable performance was X, but applications relying on Serializable to prevent write skew had bugs.

SQL Server

• Supports both lock-based isolation and SI (with explicit ALTER DATABASE ... ALLOW_SNAPSHOT_ISOLATION).

Cassandra / DynamoDB

• No general-purpose multi-row transactions.
• Conditional writes (compare-and-swap) for single-row updates.
• DynamoDB has Transactional API for multi-item transactions with serializable isolation — limited to 25 items max, with cost.

How to actually prevent the anomalies you care about

Lost updates

The most common bug. Three fixes:

• Atomic operations: UPDATE balance SET amount = amount + 100 WHERE id = 1. The DB handles concurrency.
• SELECT FOR UPDATE: SELECT amount FROM balance WHERE id = 1 FOR UPDATE takes an exclusive lock. Other transactions block until this commits.
• Snapshot Isolation (Postgres RR): the DB detects the conflict and aborts one transaction. App retries.

Write skew

Three fixes:

• Serializable isolation: SSI in Postgres detects and aborts conflicts.
• Explicit predicate locks: SELECT * FROM doctors WHERE on_call = true FOR UPDATE. Locks all rows matching the predicate.
• Materialise the predicate as a row: have a "constraints" table with one row per invariant; transactions that touch the invariant must update that row, forcing serialisation.

MVCC in depth — how readers and writers stay non-blocking

MVCC (Multi-Version Concurrency Control) is how modern databases achieve "writers don't block readers." Each row stores metadata:

• xmin: transaction ID that created this version.
• xmax: transaction ID that deleted/updated this version (null if still live).

Each transaction has a "snapshot" — the set of transaction IDs visible to it. A row version is visible if:

xmin is in snapshot (the creating transaction is committed and visible)
AND
(xmax is null OR xmax is not in snapshot)
  -- the deleting transaction doesn't exist OR isn't visible

Updates don't modify in place. They:

1. Mark the old row version's xmax as the current transaction ID.
2. Insert a new row version with xmin = current transaction.
3. Both versions exist; readers see whichever matches their snapshot.

The cost: dead tuples and VACUUM

Updates and deletes create dead row versions. They take space, slow scans, and must be reclaimed.

Postgres VACUUM:

• Scans tables for dead tuples (xmax in any committed but unreachable transaction).
• Marks their space as reusable.
• Updates statistics.
• Can be incremental (lock-light) or full (rewrites whole table; needs exclusive lock).

The critical issue: a long-running transaction holds an old snapshot. VACUUM cannot reclaim any row version newer than the oldest snapshot. Tables can balloon under one analyst's 1-hour query.

Pessimistic vs Optimistic locking

Distributed transactions

The pain begins when a transaction spans multiple shards or services.

Two-Phase Commit (2PC)

sequenceDiagram
    participant C as Coordinator
    participant P1 as Participant 1
    participant P2 as Participant 2

    Note over C,P2: Phase 1 — Prepare
    C->>P1: PREPARE
    C->>P2: PREPARE
    P1->>P1: write log, hold locks
    P2->>P2: write log, hold locks
    P1-->>C: VOTE-YES
    P2-->>C: VOTE-YES

    Note over C,P2: Phase 2 — Commit
    C->>C: log COMMIT decision
    C->>P1: COMMIT
    C->>P2: COMMIT
    P1-->>C: ACK
    P2-->>C: ACK

    Note over C,P2: If any P votes NO or times out — coordinator sends ABORT instead

Phase 1 — Prepare:
  Coordinator → Participants: PREPARE
  Each participant: persists "I'm ready to commit" to disk
  Participants → Coordinator: PREPARED (or ABORT)

Phase 2 — Commit:
  Coordinator → Participants: COMMIT (or ROLLBACK)
  Participants execute, ack.

Correct, but: if the coordinator dies between PREPARE and COMMIT, participants are stuck. They've voted to commit but don't know whether to actually commit or roll back. This is the "blocking" property of 2PC.

Used by: XA transactions, some message queues with DB integration. Avoided in modern systems because of the blocking property and operational complexity.

Saga pattern

Break the distributed transaction into a sequence of local transactions, each with a compensating action that undoes it.

Booking a trip:
  Step 1: BookHotel       → on failure later: CancelHotel
  Step 2: BookFlight      → on failure later: CancelFlight, CancelHotel
  Step 3: ChargeCard      → on failure later: RefundCard, CancelFlight, CancelHotel
  Step 4: SendConfirmation

Properties:

• No atomicity — intermediate states are visible to other observers.
• Compensation can fail too, requiring retry or manual intervention.
• No isolation — concurrent sagas can interleave.

Two implementation styles:

• Choreography: each service listens for events from others and reacts. Loose coupling; hard to debug end-to-end state.
• Orchestration: a central orchestrator drives the workflow. Temporal, AWS Step Functions, Cadence. Easier to debug.

The outbox pattern

You want to atomically (a) write to your DB and (b) publish an event to Kafka. You can't 2PC between Postgres and Kafka practically. So:

BEGIN;
  INSERT INTO orders (...);  -- the business write
  INSERT INTO outbox (event_type, payload) VALUES ('order.placed', '{...}');
COMMIT;

-- Separate worker:
SELECT * FROM outbox WHERE processed_at IS NULL FOR UPDATE SKIP LOCKED LIMIT 100;
-- For each row: publish to Kafka; UPDATE outbox SET processed_at = now() WHERE id = ?

The DB write and the outbox insert are atomic (one transaction). The worker reads from the outbox and publishes. If the worker crashes after publishing but before marking processed, the event is published twice — so Kafka consumers must be idempotent (deduplicate by event_id).

This is the foundation of "event-driven" architecture without distributed transactions. Used by Stripe, every payments company, most fintech, most CDC-driven systems.

Interview drills

CREATE TABLE idempotency_keys (
  key UUID PRIMARY KEY,
  request_hash TEXT NOT NULL,
  response JSONB,
  status TEXT NOT NULL,  -- 'pending' | 'complete' | 'failed'
  expires_at TIMESTAMPTZ
);

-- In application code:
BEGIN;
  -- Atomic claim or fetch
  INSERT INTO idempotency_keys (key, request_hash, status, expires_at)
  VALUES ($1, $2, 'pending', now() + interval '24 hours')
  ON CONFLICT (key) DO UPDATE SET request_hash = idempotency_keys.request_hash
  RETURNING *;
  -- Check if this is a retry of a different request with same key
  -- Check if status is 'complete' → return stored response
COMMIT;

-- If we got the row (new request): process the payment
response = call_payment_provider(...);

BEGIN;
  UPDATE idempotency_keys SET response = $1, status = 'complete' WHERE key = $2;
COMMIT;
RETURN response;

CREATE TABLE outbox (
  id BIGSERIAL PRIMARY KEY,
  aggregate_id UUID NOT NULL,
  event_type TEXT NOT NULL,
  payload JSONB NOT NULL,
  processed_at TIMESTAMPTZ
);
CREATE INDEX idx_outbox_unprocessed ON outbox (aggregate_id, id)
  WHERE processed_at IS NULL;

-- Business write + outbox in one transaction
BEGIN;
  INSERT INTO orders (...);
  INSERT INTO outbox (aggregate_id, event_type, payload)
  VALUES ($1, 'order.placed', $2);
COMMIT;

-- Worker, one per aggregate (or sharded by aggregate_id)
SELECT * FROM outbox
WHERE processed_at IS NULL
  AND aggregate_id = ANY($1)  -- worker's assigned aggregates
ORDER BY id
LIMIT 100
FOR UPDATE SKIP LOCKED;
-- For each: publish to Kafka with key = aggregate_id (preserves Kafka partition ordering)
-- Mark processed

Red flags

• "Postgres serializable and MySQL serializable are the same." Wrong — Postgres uses SSI, MySQL uses locking.
• "Snapshot isolation is the same as serializable." Wrong — doesn't prevent write skew.
• Doesn't know what write skew is.
• Suggests 2PC for high-throughput systems.
• Implements "check then insert" without a uniqueness constraint.
• Implements idempotency by reading then writing (race condition).