6. SQL Deep Dive: ACID, Isolation, MVCC

SQL is decades old and still the right default. To design with it well you must understand what transactions actually guarantee — and where they leak.

6.1 ACID

A transaction groups operations into an atomic unit. The classic ACID guarantees:

Atomicity: all or nothing. A transaction either fully commits or has no effect. (Misnamed historically: "atomic" here means not divisible into a partially-applied state.)
Consistency: the database moves from one valid state to another (constraints, FKs, app-defined invariants). This C is the weakest — much of "consistency" is the application's responsibility, not the DB's.
Isolation: concurrent transactions appear to run as if they were serial.
Durability: once committed, survives crashes.

ACID is not a precise standard — DB vendors implement it differently. The interesting axis is isolation level.

6.2 Isolation levels

Two transactions touching the same data can interfere. The SQL standard defines levels by which interferences are prevented.

Anomalies

Dirty read: T1 reads T2's uncommitted write. T2 then aborts → T1 saw a value that "never existed."
Dirty write: T1 overwrites T2's uncommitted write. Lost work.
Non-repeatable read (read skew): T1 reads X, T2 commits a change to X, T1 reads X again and gets a different value.
Phantom read: T1 runs a range query, T2 inserts a new row matching the predicate, T1 re-runs and gets a different result set.
Lost update: T1 reads X, computes X+1, writes X+1. T2 does the same concurrently. One increment is lost (last writer wins).
Write skew: two transactions read overlapping data, write disjoint changes that together break an invariant. Classic example: two on-call doctors both go off-call simultaneously because each saw the other was on-call.

Levels (from weakest to strongest)

Level	Dirty read	Non-repeatable	Phantom	Lost update	Write skew
Read uncommitted	possible	possible	possible	possible	possible
Read committed	prevented	possible	possible	possible	possible
Repeatable read / snapshot	prevented	prevented	prevented*	prevented*	possible
Serializable	all prevented

*Postgres "Repeatable Read" is actually snapshot isolation; it prevents phantoms. MySQL InnoDB "Repeatable Read" uses next-key locking → also prevents phantoms.

What each DB gives you

Postgres default: Read Committed. Most apps run here. Snapshot Isolation (REPEATABLE READ) and Serializable Snapshot Isolation (SERIALIZABLE) are opt-in.
MySQL/InnoDB default: Repeatable Read.
Oracle: Read Committed, with serializable available.
Spanner / CockroachDB: serializable by default.

Read Committed in detail

Each statement sees a consistent snapshot taken at statement start.
Two statements in the same transaction can see different snapshots.
Implemented via row-level locks for writes + snapshots for reads.

Snapshot Isolation (SI) in detail

Each transaction sees a consistent snapshot taken at transaction start.
Implemented via MVCC (Multi-Version Concurrency Control): every write creates a new version with a timestamp; reads pick the version visible at their snapshot.
Prevents most anomalies; does not prevent write skew.

Serializable Snapshot Isolation (SSI)

SI + an additional check that detects write skew. Postgres SERIALIZABLE uses SSI. Tracks read sets; if commits would form a serialization conflict, abort the later transaction. Cheap on contention-light workloads, abort-prone under heavy contention.

6.3 MVCC

Multi-Version Concurrency Control. The mechanism that makes "readers don't block writers" possible.

How it works (Postgres flavor):

Every row has hidden columns: xmin (creating txn ID), xmax (deleting/updating txn ID).
Each transaction gets a snapshot: a list of "in-flight" txn IDs.
When reading, a row is visible if xmin is committed-and-≤-snapshot AND xmax is null/in-flight/>-snapshot.
Updates don't overwrite; they insert a new row version and mark the old one's xmax.

Side effects:

Old row versions accumulate until no transaction needs them. Vacuum (autovacuum) reclaims space. Long-running transactions block vacuum → bloat. Vacuum is the #1 Postgres operational headache.
Replication lag can extend the visibility horizon (hot_standby_feedback). Same problem on standbys.

6.4 Locking

Even with MVCC, writes need exclusion.

Row-level locks: held until commit. SELECT ... FOR UPDATE takes them explicitly.
Table-level locks: schema changes, vacuum full, etc.
Predicate locks: serializable mode only — prevent inserts that would match a queried predicate.
Advisory locks: app-managed, named locks (Postgres pg_advisory_lock). Useful for distributed cron coordination.

Deadlock: two transactions each hold a lock the other wants. Postgres detects and aborts one (ERROR: deadlock detected). App must retry. Rule: always lock in a consistent order; keep transactions short.

6.5 Constraints

NOT NULL: cheap, fast, always use unless you mean nullable.
CHECK: row-level invariant.
UNIQUE: guaranteed via a unique B-tree index.
FOREIGN KEY: referential integrity. Costs a lookup on insert/update; required FK index on child for fast cascading deletes.
EXCLUDE (Postgres-specific): "no two rows have overlapping ranges/circles/etc." — calendar booking constraints.

Use constraints aggressively: they catch bugs at the DB layer, the closest place to the data. Application-side validation can be skipped, drift, or fail under race conditions.

6.6 Indexes (SQL flavor)

(See chapter 5 for index types.) SQL-specific notes:

B-tree index (default for CREATE INDEX)
Hash index (Postgres has them; rarely used; B-tree usually fine)
GIN (Generalized Inverted Index): for arrays, JSONB, full-text search
GiST (Generalized Search Tree): for geometry (PostGIS), ranges, full-text
BRIN (Block Range INdex): tiny, lossy index for naturally-ordered data (timestamps in append-only tables)
EXPLAIN ANALYZE: read it. The query planner is your friend; pgMustard / pev2 visualizers help.

Index gotchas

An index on (a, b, c) does not help a query filtered only by b.
Functions in WHERE break index use unless you have an expression index: CREATE INDEX ON users (lower(email)).
LIKE 'foo%' uses a B-tree index; LIKE '%foo' does not (use trigram GIN).
High-cardinality + low-selectivity columns are good index candidates; boolean columns are not.

6.7 Query optimization

The query planner picks join orders, index uses, and access methods. It uses statistics (collected by ANALYZE) about value distribution, NDV (number of distinct values), most-common-values, histogram bins.

Common pathologies:

N+1 queries (ORM): fetch a list, then for each row fetch related rows. Fix with JOIN or IN (...).
OR with mixed columns: planner may not use indexes. Rewrite with UNION if needed.
Stale stats after a bulk import → bad plans. ANALYZE after big changes.
Parameter sniffing on prepared statements: planner caches a plan optimal for the first parameter values; later values may be terrible.

6.8 Connection pooling

A Postgres connection holds memory (~10 MB) and a backend process (Postgres) or thread (MySQL). 100s of app instances × 10 connections each → DB melts.

Use a pooler:

PgBouncer (lightweight, transaction-pooling mode)
Pgpool, Odyssey
Supabase / Neon include built-in poolers
AWS RDS Proxy, GCP AlloyDB Auth Proxy

In transaction pooling mode, connections are reused per transaction → no support for session-scoped state (advisory locks, prepared statements without protocol-level handling).

6.9 Schema migration

Every long-lived service migrates schemas. Patterns:

Backwards-compatible writes first: add nullable column, deploy code that writes both old and new, backfill, deploy code that reads new only, drop old.
Avoid ALTER TABLE that rewrites the table on huge tables. Use online migration tools: pt-online-schema-change (Percona), gh-ost (GitHub), Postgres ALTER TABLE ... ADD COLUMN is safe for nullable columns, but ADD COLUMN ... DEFAULT rewrites pre-PG 11.
Backfill in batches with throttling.
Watch for long locks. ALTER TABLE waits for current transactions; if a long-running query is in flight, the migration blocks all subsequent queries.

6.10 What "ACID" doesn't give you

No protection across multiple databases. Distributed transactions are a separate, harder problem (chapter 16).
No protection from app bugs that violate domain invariants. The DB doesn't know "an order must have at least one line item" unless you encode it.
Isolation isn't free. Serializable can be 2-10× slower than read committed under contention.

Key takeaways

ACID is a meaningful baseline; isolation level is the dial.
Postgres default = Read Committed; opt up to Snapshot or Serializable when you need it.
MVCC = readers don't block writers; you pay in vacuum.
Use constraints liberally — push validation into the DB.
Connection pooling is non-negotiable beyond ~10 app instances.
Schema migrations are a lifecycle, not an event.