Chapter 26 — Key Takeaways

The big idea

A transaction is an all-or-nothing unit of work — the mechanism for concurrency and durability (two of Chapter 1's five problems). It's what lets you trust a database with money, records, and inventory.

ACID

  • Atomicity — all statements succeed together or none do (the transfer can't half-happen). (Case Study 1.)
  • Consistency — every committed transaction leaves all constraints satisfied.
  • Isolation — concurrent transactions don't interfere (to the degree set by the isolation level).
  • Durability — once committed, changes survive crashes (via the WAL, Ch. 28).

Transactions

  • BEGIN ... COMMIT (or ROLLBACK); savepoints revert part of a transaction.
  • Wrap any multi-step change that must agree (debit+credit, order+items+inventory). Auto-commit makes each statement its own transaction — no atomic relationship between them. (Case Study 1.)

Isolation phenomena

  • Dirty read — reading uncommitted (maybe-rolled-back) data.
  • Non-repeatable read — same row, different value within one transaction. (Case Study 2.)
  • Phantom read — a re-run range query returns different rows.

Isolation levels (PostgreSQL)

Level Prevents
Read Committed (default) dirty reads
Repeatable Read + non-repeatable and phantom reads (one snapshot per transaction)
Serializable + serialization anomalies (as-if-serial; retry on failure)
  • Default (Read Committed) is right for most work; raise it for a stable multi-query view (reports → Repeatable Read) or true serializability (→ Serializable + retry loop).

MVCC (how PostgreSQL isolates)

  • Keeps multiple row versions → each transaction sees a snapshot per its isolation level.
  • Readers never block writers; writers never block readers. (Same-row writers still conflict — Ch. 27.)
  • Cost: dead tuples, cleaned by VACUUM (Ch. 28). Keep transactions short (long ones hold snapshots and block cleanup).

Common mistakes

Multi-step changes without a transaction (Case Study 1); assuming the default prevents all anomalies (Case Study 2); Serializable without retry logic; long-running transactions.

You can now…

  • ☐ Define ACID and apply each property.
  • ☐ Use BEGIN/COMMIT/ROLLBACK and savepoints for atomic work.
  • ☐ Identify dirty/non-repeatable/phantom reads and the level that prevents each.
  • ☐ Explain MVCC (readers/writers don't block).
  • ☐ Choose an isolation level and retry serialization failures.

Looking ahead

Chapter 27 — Concurrency Control. The mechanics underneath isolation: locks, deadlocks, optimistic vs. pessimistic strategies, SELECT FOR UPDATE, the lost-update problem, and connection pooling.

One sentence to carry forward: Wrap multi-step changes in a transaction so they're all-or-nothing, and raise the isolation level when several reads must agree — MVCC gives you both correctness and concurrency.