S042 · Transactions & ACID — Isolation Levels, MVCC
The single feature that lets banks, e-commerce, and every serious business run on a database. Learn ACID, the four isolation levels, MVCC, and why ‘SERIALIZABLE’ is not the answer you want.
🎯 Reason about concurrency anomalies (dirty read, non-repeatable read, phantom, write skew) and pick the right isolation level per query — not per database.
Why this session exists
Every real system has more than one user at a time. The moment two transactions touch the same row, physics wins and something races. ACID is the four-letter promise a database makes about what "correct" means under concurrency — and isolation levels are the dial you turn to trade correctness for throughput. Most engineers never learn where that dial actually sits by default (spoiler: not where you think), so they ship silent bugs that only show up under load. Ninety minutes here saves years of "why did we double-charge that customer?" post-mortems.
- Spell out A, C, I, D and give a concrete example of a bug each one prevents.
- Rank the four SQL isolation levels and name the anomaly each one still allows.
- Explain MVCC in one sentence and draw why Postgres readers never block writers.
- Pick the right isolation level for a bank transfer vs an analytics query vs a like-count increment.
- Recognise write skew and know why SERIALIZABLE (not REPEATABLE READ) is the only thing that stops it.
Prerequisites
- S040 — Joins & Set Operations (you need to be comfortable reading multi-table SQL).
- S041 — Indexes — B-Tree Intuition (helps for understanding row locks vs range locks).
(a) Intuition · 5 min
Two chefs share a single recipe card that reads: "Flour = 500 g. Add 100 g. Write new total." Chef A reads 500, calculates 600, and is interrupted. Chef B reads the still-500 card, writes 600, and walks off. Chef A finally writes 600. You added 200 g of flour but the card says 600. That is a lost update, and every database in existence has a story about it.
ACID is the kitchen's operating discipline: "only one chef holds the card at a time" (isolation), "if the fire alarm goes off before you finish, nothing you wrote counts" (atomicity), "when you're done, the card can't say ‘650.7 g’ if the rule is integers" (consistency), and "even if the building burns down, the card survives" (durability).
Translate that to a bank: two ATM withdrawals debit the same account of ₹10,000. Without isolation, both read balance = ₹10k, both write ₹9k after taking ₹1k each. The customer paid ₹2k and the bank recorded ₹1k. Multiply by a million transactions per day.
Transactions are the primitive that wraps "read, decide, write" into a unit the database can defend. Isolation levels are the language you use to tell the database how much defense you need, because more defense = less throughput.
ACID in one line each
- Atomicity — a transaction is all-or-nothing. Half a bank transfer never happens, even if the power dies mid-COMMIT.
- Consistency — the database moves from one valid state to another. Constraints, foreign keys, and triggers hold at commit boundaries.
- Isolation — concurrent transactions look like they ran one after another (to some agreed degree). This is the dial you tune.
- Durability — once COMMIT returns, the data survives crashes, power loss, and the CFO tripping over the server cord.
A quick history so you know why the world looks like this
- 1976Jim Gray defines transactionsIBM Research paper ‘Granularity of Locks in a Shared Data Base’ invents the transaction concept.
- 1983‘ACID’ coinedHärder & Reuter's paper ‘Principles of Transaction-Oriented Database Recovery’ gives the acronym its final form.
- 1992SQL-92 standardises 4 isolation levelsREAD UNCOMMITTED, READ COMMITTED, REPEATABLE READ, SERIALIZABLE. Every vendor implements them differently.
- 1995Oracle ships MVCCMulti-Version Concurrency Control: readers see a snapshot; writers never block readers. Postgres follows in 1996.
- 2008Postgres adds SERIALIZABLE via SSI‘Serializable Snapshot Isolation’ — the first practical serializable that doesn't kill throughput.
- 2012Google SpannerExternal consistency across continents using GPS + atomic clocks (TrueTime). ACID at planet scale is real.
(b) Visual walkthrough · 15 min
The four anomalies, ordered by nastiness
You read a value another transaction wrote but hasn't committed yet. It rolls back — you acted on data that never existed.
You read row X, another txn updates row X and commits, you read row X again — different value. Same query, same transaction, two answers.
You SELECT WHERE status='pending', someone INSERTs a new pending row and commits, you re-SELECT — a new row appeared out of nowhere.
Two transactions each read the same set, each check ‘I'm still safe to write’, and both commit — together they violate an invariant. The subtle one.
MVCC — the model that made Postgres eat the world
In one sentence: each transaction sees a snapshot of the database as it was when the transaction started. Writers never block readers. Readers never block writers. That single design decision is why Postgres and Oracle beat MySQL's older locking model for OLTP workloads for two decades.
Isolation level cheat-sheet (what Postgres actually does)
Postgres default. Each statement gets a fresh snapshot.
- Blocks dirty reads
- Allows non-repeatable reads (each statement re-snapshots)
- Fast, forgiving, right answer 80% of the time
- Use for: web request handlers, most CRUD
Postgres = snapshot isolation. The whole txn sees one snapshot.
- Blocks dirty + non-repeatable reads
- Blocks phantoms (in Postgres, not SQL-92 REPEATABLE READ)
- Allows write skew
- Use for: reports, complex reads inside one transaction
SSI — real serial execution, statistically.
- Blocks everything, including write skew
- Postgres detects serialization conflicts at commit and aborts one txn (retry loop needed)
- Costs 10-25 % throughput
- Use for: money movement, inventory, constraints across rows
"My database is ACID compliant, so my transactions are fully isolated — concurrent transactions behave as if they ran one after another."
Full serialisability is almost never the default. Postgres and most engines default to READ COMMITTED; MySQL InnoDB defaults to REPEATABLE READ. Both permit anomalies that serialisability forbids — non-repeatable reads, phantoms, and write skew. The "I" in ACID is a dial with several settings, and you are almost certainly not on the strongest one.
Because ACID is marketed as a single guarantee and the letter I is stated without qualification, so isolation sounds binary. It is not: the SQL standard defines four levels precisely because full isolation is expensive, and vendors default to a weaker level so that ordinary workloads are fast. The anomalies are rare under light load, which means they surface first in production under concurrency — the least convenient possible time.
Check your actual level, and reproduce write skew with two concurrent sessions:
SHOW transaction_isolation; -- Postgres: 'read committed' by default
-- Write skew: rule is 'at least one doctor must stay on call'.
-- Two doctors go off call simultaneously; each check passes, rule is violated.
-- Session A: Session B:
BEGIN; -- BEGIN;
SELECT COUNT(*) FROM oncall -- SELECT COUNT(*) FROM oncall
WHERE on_call = true; -- 2 -- WHERE on_call = true; -- 2
UPDATE oncall SET on_call=false -- UPDATE oncall SET on_call=false
WHERE name = 'alice'; -- WHERE name = 'bob';
COMMIT; -- COMMIT;
-- Result: zero doctors on call. Both transactions committed. No error.
-- SERIALIZABLE would abort one of them; REPEATABLE READ does not.Why does durability require a write-ahead log, when the database could simply write the changed data pages to disk before acknowledging the commit?
- 1A transaction typically modifies a few rows scattered across many different pages of the table and its indexes.forced by · rows are placed by key or by insertion order, not by which transaction touches them
- 2Writing those pages directly means many random writes, scattered across the storage device, before the commit can return.forced by · each page lives at its own location determined by the table's layout
- 3Random writes are far slower than sequential ones, and worse, a multi-page write is not atomic — a crash midway leaves some pages updated and some not, with no record of which.forced by · the device guarantees atomicity only at roughly the page level, not across a set of pages
- 4So instead, append a compact record of what changed to a single sequential log file, and fsync only that. One sequential append, one flush.forced by · sequential appends are the fastest write pattern available, and a single file gives one clear ordering
- 5Once the log record is durably on disk, the change is recoverable — the actual data pages can be written lazily in the background, and a crash is repaired by replaying the log.forced by · the log is the authoritative record of intent; the data pages are a materialised cache of it
Therefore the WAL converts many random synchronous writes into one sequential synchronous write, and simultaneously provides the atomicity that page writes cannot. Durability and atomicity fall out of the same mechanism.
And note what this predicts: commit latency is bounded by one fsync, which is why grouping many small transactions into one is such a large throughput win, and why synchronous_commit = off is so tempting and so dangerous. It also predicts the architecture of nearly everything downstream — Kafka's partitions, LSM-tree storage engines, Delta and Iceberg transaction logs, and database replication are all the same insight: the log is the database, and the tables are a view over it.
Do not picture the database as tables that get edited in place. Picture an append-only log of intentions — "set x to 5", "delete row 9" — that is written durably and in order, plus a set of data pages that are merely a fast materialised view of replaying that log.
Commit means "my records are durably in the log". Rollback means "my records are marked abandoned and will never be applied". Recovery means "replay the log from the last checkpoint". Every ACID property is a consequence of that one structure.
- Atomicity and durability come from the log. Isolation comes from concurrency control — locking or MVCC — and is a separate, tunable mechanism.
- Under MVCC, readers see a consistent snapshot and never block writers. This is why "reads don't block writes" is true in Postgres and why old row versions accumulate until vacuumed.
- The four levels in ascending strictness: READ UNCOMMITTED, READ COMMITTED, REPEATABLE READ, SERIALIZABLE — each eliminating one more anomaly class at more cost.
- Consistency (the C) is the odd one out: it is not enforced by the engine beyond declared constraints. It is your invariants, and it is only as strong as the constraints you actually declared.
Fire this model the moment you see: a check-then-act pattern (read a balance, then update it) · "this worked in test but corrupts under load" · a uniqueness rule enforced in application code instead of by a constraint · a deadlock in the logs · a long-running transaction blocking vacuum · an idempotency requirement in a pipeline · exactly-once semantics in a streaming system.
A workflow must read a value, compute from it, and write it back, with correctness under concurrency. Pessimistic locking, optimistic concurrency, or SERIALIZABLE isolation?
Match the mechanism to the invariant. If the rule is about one row, a row lock or a version column expresses it exactly. If the rule is about a set of rows — "at least one must remain", "no overlapping bookings" — then no row-level mechanism can enforce it, and you need SERIALIZABLE or an explicit constraint like an exclusion constraint. That distinction is what write skew is teaching you.
And prefer declared constraints over transactional cleverness wherever possible. A unique index enforces uniqueness under every isolation level, at every concurrency, forever, with no retry logic — it is strictly stronger than any application-level check-then-insert, and it is one line of DDL.
(c) Hands-on · 25 min
Two psql sessions demonstrating each anomaly with your own eyes. Save as acid_demo.sh, run against a local Postgres (docker run --rm -p 5432:5432 -e POSTGRES_PASSWORD=demo postgres:16).
#!/usr/bin/env bash
# acid_demo.sh — reproduce dirty-read (can't in Postgres), non-repeatable read,
# phantom, and write skew across two psql sessions.
set -euo pipefail
export PGPASSWORD=demo
PSQL="psql -h localhost -U postgres -v ON_ERROR_STOP=1"
$PSQL <<'SQL'
DROP TABLE IF EXISTS accounts;
CREATE TABLE accounts (id INT PRIMARY KEY, owner TEXT, balance INT);
INSERT INTO accounts VALUES (1,'alice',1000),(2,'bob',1000);
DROP TABLE IF EXISTS on_call;
CREATE TABLE on_call (doctor TEXT PRIMARY KEY, is_on_shift BOOLEAN);
INSERT INTO on_call VALUES ('alice',TRUE),('bob',TRUE);
SQL
echo "--- Demo 1: non-repeatable read at READ COMMITTED ---"
(
$PSQL <<'SQL' &
BEGIN ISOLATION LEVEL READ COMMITTED;
SELECT balance FROM accounts WHERE id=1; -- 1000
SELECT pg_sleep(3);
SELECT balance FROM accounts WHERE id=1; -- ??? see what happens
COMMIT;
SQL
sleep 1
$PSQL -c "UPDATE accounts SET balance=1500 WHERE id=1;"
wait
)
echo "--- Demo 2: phantom read at READ COMMITTED ---"
(
$PSQL <<'SQL' &
BEGIN ISOLATION LEVEL READ COMMITTED;
SELECT count(*) FROM accounts WHERE balance >= 1000;
SELECT pg_sleep(3);
SELECT count(*) FROM accounts WHERE balance >= 1000;
COMMIT;
SQL
sleep 1
$PSQL -c "INSERT INTO accounts VALUES (3,'carol',5000);"
wait
)
echo "--- Demo 3: write skew (only SERIALIZABLE catches this) ---"
# Rule: at least one doctor MUST be on shift.
# Two txns each read the set, each see 2 on shift, each take themselves off.
# Result: zero doctors on shift. That's write skew.
(
$PSQL <<'SQL' &
BEGIN ISOLATION LEVEL REPEATABLE READ; -- try both RR and SERIALIZABLE
SELECT count(*) FROM on_call WHERE is_on_shift; -- 2
SELECT pg_sleep(2);
UPDATE on_call SET is_on_shift=FALSE WHERE doctor='alice';
COMMIT;
SQL
$PSQL <<'SQL' &
BEGIN ISOLATION LEVEL REPEATABLE READ;
SELECT count(*) FROM on_call WHERE is_on_shift; -- 2
SELECT pg_sleep(2);
UPDATE on_call SET is_on_shift=FALSE WHERE doctor='bob';
COMMIT;
SQL
wait
)
echo "--- Final state (should be at least one TRUE; watch it be all FALSE) ---"
$PSQL -c "SELECT * FROM on_call;"What each block does
Anatomy of the script
Install pgbench (ships with Postgres). Run the standard TPC-B benchmark at two isolation levels and diff the TPS:
pgbench -i -s 10 -h localhost -U postgres
pgbench -c 16 -j 4 -T 30 -h localhost -U postgres # default RC
pgbench -c 16 -j 4 -T 30 -h localhost -U postgres -M prepared --define="isolation_level=serializable"You'll typically see a 10-25 % TPS drop at SERIALIZABLE plus some retry churn. That's the real cost — measure it before you flip the switch on a hot table.
(d) Production reality · 15 min
Jepsen ran isolation tests against MongoDB, YugabyteDB, FaunaDB, CockroachDB, and others. Almost every one claimed "serializable" and almost every one exhibited anomalies under network partitions or clock skew.
The most infamous: MongoDB advertised "causal consistency" but showed dirty reads under specific replica-set failover conditions until v4.2.
Databases patched. Jepsen wrote 40+ public reports. The industry lesson: never trust an isolation level claim without a Jepsen test. Even ACID-claiming databases lie under load.
An account-transfer service ran at READ COMMITTED and used the classic pattern: SELECT balance, check >= amount in Java, UPDATE balance -= amount. Two concurrent withdrawals both saw balance = ₹10k, both passed the check, both committed. Customer overdrew by ₹9k and the bank ate the loss.
Two options: (a) wrap in SERIALIZABLE + retry loop, or (b) rewrite as a single atomic update: UPDATE accounts SET balance = balance - 1000 WHERE id = ? AND balance >= 1000 and check rowcount == 1. Both work; the atomic-UPDATE version is faster.
if.A 43-second MySQL replica partition triggered Orchestrator failover. When the split healed, both regions had accepted writes to overlapping rows. Reconciling the divergent MVCC histories took the site into degraded mode for 24 hours.
GitHub rewrote failover to require a strict quorum (block writes to any region that loses quorum) and moved key services to a single-primary topology. Post-mortem is legendary reading.
Where this shows up in the rest of the plan
(e) Recall + stretch · 10 min
Explain-out-loud test
- ACID in 30 seconds — the four letters, one example each.
- Write skew in one example — the on-call doctors story is fine.
- Why READ COMMITTED is the right default — one throughput argument, one correctness workaround.
What comes next
Hub: The 6-Month Learning Plan
Part of a 130-session evergreen learning series. Session structure: intuition → visual → hands-on → production war stories → recall. Duration: 90 minutes.