S042 · Transactions & ACID — Isolation Levels, MVCC

Module M04: Databases & SQL · Session 42 of 130 · Track: DE 🔒

What you'll be able to do after this session

• Explain Transactions & ACID to a friend in one minute, out loud, no notes.
• Recognise it when you see it in real code / systems / papers.
• Complete the hands-on exercise at the end without looking up help.

Prerequisites

If you can't answer these in 30 seconds each, redo the linked session first:

• S041 — Indexes — B-Tree Intuition, When to Add

Pre-read (skim before the session)

• 🎥 Intuition (5–15 min) · Relational Database ACID Transactions (Explained by Example) — Hussein Nasser walks through a bank-transfer example. The clearest ACID intro on YouTube.
• 🎥 Deep dive (30–60 min) · Transactions: myths, surprises and opportunities — Martin Kleppmann — the author of Designing Data-Intensive Applications demolishes what you think you know about isolation levels.
• 🎥 Hands-on demo (10–20 min) · Distributed Systems 1.1: Introduction — Martin Kleppmann — sets up the concurrency vocabulary you need to reason about MVCC.
• 📖 Canonical article · PostgreSQL Docs — Transaction Isolation — the definitive reference for the four SQL isolation levels, with concrete anomaly examples.
• 📖 Book chapter / tutorial · Jepsen — Consistency Models — Kyle Kingsbury's map of every consistency model, from linearizable down to read-uncommitted. Bookmark it.
• 📖 Engineering blog · Martin Fowler / Unmesh Joshi — Write-Ahead Log — how the "D" in ACID (durability) is actually implemented.

(a) Intuition · 5 min

The one-paragraph mental model. Why this concept exists, what problem it solves, and the analogy that makes it stick.

In one sentence: Correctness under concurrency.

Two people at the same ATM try to withdraw ₹5,000 from an account that has ₹6,000. Without transactions, both requests read balance = 6000, both check 6000 >= 5000, both subtract, and both write balance = 1000. The bank just gave out ₹10,000 from an account that had ₹6,000. That is a race condition, and transactions exist specifically to prevent it.

A transaction is a promise the database makes: a group of statements either all happen or none happen, and other users see a consistent picture the whole time. The acronym ACID captures the four guarantees: Atomicity (all-or-nothing — if any statement fails, the whole thing rolls back), Consistency (constraints like foreign keys and CHECKs are never violated), Isolation (concurrent transactions don't step on each other), and Durability (once you get "commit successful," the data survives a power cut).

The gotcha to carry forever: "isolated" does not mean "serial." By default, most databases run at READ COMMITTED, which allows weird anomalies (non-repeatable reads, phantoms, lost updates). If you assume "the database will handle concurrency for me" and don't think about isolation levels, you will ship a bug that only shows up at scale. Postgres and MySQL/InnoDB use MVCC (multi-version concurrency control) — every writer creates a new version of the row, so readers never block writers. This is fast and elegant, but it means the "snapshot" you see is a specific point in time, and reasoning about what that point is is the hard part.

(b) Visual walkthrough · 15 min

Diagrams, worked examples, step-by-step visuals.

The SQL standard defines four isolation levels. Higher isolation = fewer anomalies but more locking / more retries.

Worked example — the lost-update anomaly:

Two concurrent transactions T1 and T2 both increment a page-view counter starting at 100:

Two fixes: (a) use UPDATE page SET views = views + 1 WHERE id=1 (atomic increment, no read-modify-write), or (b) SELECT ... FOR UPDATE to lock the row, or (c) run at SERIALIZABLE and retry on conflict.

MVCC in one picture: in Postgres, each row has hidden xmin (creating transaction) and xmax (deleting transaction) columns. A SELECT at snapshot time T sees a row iff xmin <= T and (xmax > T or xmax is null). That's why readers never block writers — they read old versions. VACUUM cleans up dead versions later.

(c) Hands-on · 20 min

Code you type and run. Not pseudo-code — real, runnable, copy-pasteable.

Reproduce a lost update in Postgres using two psql sessions.

# Start Postgres
docker run --rm -d --name pg-tx -e POSTGRES_PASSWORD=pw -p 5432:5432 postgres:16
sleep 3
psql "postgres://postgres:pw@localhost:5432/postgres" <<'SQL'
CREATE TABLE accounts(id int PRIMARY KEY, balance int);
INSERT INTO accounts VALUES (1, 1000);
SQL

Now open two terminals. In each, connect: psql "postgres://postgres:pw@localhost:5432/postgres".

-- Terminal A                         -- Terminal B
BEGIN;                                BEGIN;
SELECT balance FROM accounts          SELECT balance FROM accounts
  WHERE id=1;  -- sees 1000             WHERE id=1;  -- sees 1000
-- (app computes new=1000-100=900)   -- (app computes new=1000-200=800)
UPDATE accounts SET balance=900       -- waits...
  WHERE id=1;
COMMIT;                               UPDATE accounts SET balance=800
                                        WHERE id=1;  -- now proceeds
                                      COMMIT;
 
SELECT balance FROM accounts WHERE id=1;
-- => 800.  The 100-rupee debit is LOST. Should be 700.

Now repeat at SERIALIZABLE and watch Postgres reject the second commit:

-- Reset
UPDATE accounts SET balance=1000 WHERE id=1;
 
-- Terminal A                         -- Terminal B
BEGIN ISOLATION LEVEL SERIALIZABLE;   BEGIN ISOLATION LEVEL SERIALIZABLE;
SELECT balance FROM accounts WHERE id=1;
                                      SELECT balance FROM accounts WHERE id=1;
UPDATE accounts SET balance=900       UPDATE accounts SET balance=800
  WHERE id=1;                           WHERE id=1;  -- blocks / then errors
COMMIT;                               -- ERROR: could not serialize access
                                      -- ROLLBACK; retry from BEGIN.
 
# Cleanup: docker stop pg-tx

Observe (checklist):

1. At READ COMMITTED (the default), the "lost update" really happens — the database will not save you.
2. At SERIALIZABLE, Postgres detects the conflict and forces one transaction to retry.
3. The atomic form UPDATE ... SET balance = balance - 100 avoids the race entirely without any special isolation.
4. SELECT ... FOR UPDATE inside a transaction takes a row lock — the second reader blocks until the first commits.
5. SHOW default_transaction_isolation; confirms your session's level.

Try this modification: re-run the first experiment but replace both UPDATEs with UPDATE accounts SET balance = balance - 100 and -200. Does the lost update still happen? Why not?

(d) Production reality · 10 min

How this shows up in real systems, gotchas, war stories, common bugs.

The most expensive kind of data-integrity bug is one you can't reproduce in dev because dev has one user. GitHub had a famous 2018 incident where a race condition in issue-number allocation gave two issues the same number — root cause: READ COMMITTED + a read-modify-write pattern. The fix was a database sequence, not more locking. Prefer atomic operations (UPDATE ... SET x = x + 1, INSERT ... ON CONFLICT, sequences) over any transaction gymnastics. They are always cheaper and always safer.

MVCC has its own class of pathology: long-running transactions bloat the database. A BEGIN you forgot to COMMIT in a stuck worker prevents VACUUM from cleaning up any dead rows anywhere in the database. Postgres will happily grow to 500 GB of dead tuples on a 10 GB working set. Symptoms: query performance slowly degrades over hours. Fix: monitor pg_stat_activity for state='idle in transaction' sessions older than a few minutes and kill them; set idle_in_transaction_session_timeout.

Distributed transactions across services are a rabbit hole. Two-phase commit (2PC) is real but slow, brittle, and blocks on coordinator failure — most modern shops (Uber, Netflix, Shopify) explicitly avoid it and instead use sagas (compensating transactions) or outbox patterns (write the intent to a table in the same local transaction as the business change, then a background worker publishes the event). If you find yourself typing "XA transaction" in 2026, stop and ask if you can restructure.

Finally: retry logic is not optional at SERIALIZABLE. Postgres will throw 40001 serialization failures under contention and your app must catch, back off, and retry. Frameworks like SQLAlchemy and Django's atomic() do not automatically retry. Wrap it yourself.

(e) Quiz + exercise · 10 min

1. What does the "I" in ACID guarantee, and what does it not guarantee?
2. Give one concrete anomaly that is possible at READ COMMITTED but impossible at SERIALIZABLE.
3. In MVCC, why can a SELECT never block a concurrent UPDATE on the same row?
4. Why does a long-idle BEGIN (no COMMIT) cause table bloat in Postgres?
5. Give one atomic SQL pattern that avoids the lost-update problem without changing the isolation level.

Stretch (connects to S041 — Indexes): if you SELECT ... FOR UPDATE WHERE indexed_col = ? at READ COMMITTED, exactly which rows get locked? What happens if the column is not indexed? (Hint: the answer involves the "phantom" anomaly and gap locks in MySQL vs predicate locks in Postgres SERIALIZABLE.)

Explain-out-loud test

If you can't teach these 3 things to a friend without notes, redo the session:

1. What is Transactions & ACID? (one sentence, no jargon)
2. When would you use it? (one concrete example)
3. When would you NOT use it? (one anti-pattern)

What comes next

• Next session (S043): Query Planning — EXPLAIN, Execution Plans, Tuning
• Previous (S041): Indexes — B-Tree Intuition, When to Add
• Hub: The 6-Month Learning Plan

Part of a 130-session evergreen learning series. Session structure: (a) intuition · (b) visual walkthrough · (c) hands-on · (d) production reality · (e) quiz + exercise. Duration: 60 minutes.