Transactions

A transaction exists to simplify the programming model: the database handles certain error and concurrency scenarios (its safety guarantees) so you don't have to. Not every app needs them, but the absence of ACID transactions was the probable technical cause of the Post Office Horizon scandal (Ch. 2). NoSQL abandoned or redefined transactions on the (now-disproven) belief they couldn't scale; NewSQL (CockroachDB, TiDB, Spanner, FoundationDB, YugabyteDB) provides strong ACID at scale by combining sharding with consensus (Ch. 10).

---

The Meaning of ACID

"ACID" (coined 1983) is precise in theory but has become mostly a marketing term, implementations differ, especially on isolation. (The vague opposite, "BASE," basically just means "not ACID.")

Fullscreen

• Atomicity, not about concurrency. It means: if a fault occurs partway through a series of writes, the transaction aborts and all its writes are discarded, so it can be safely retried. ("Abortability" would be a clearer name.)
• Consistency, the word has at least five meanings (replica consistency, a consistent snapshot, consistent hashing, CAP's linearizability, and ACID's). ACID consistency = your app-specific invariants (e.g., credits = debits) stay true. The DB enforces only what you declare as constraints; otherwise C is the application's responsibility, not the database's.
• Isolation, concurrent transactions don't step on each other. The textbook ideal is serializability (results as if transactions ran one at a time). Because it's costly, most DBs use weaker levels (Oracle's "serializable" is actually snapshot isolation).
• Durability, committed data won't be lost: written to nonvolatile storage (via fsync), protected by a WAL and checksums, and in replicated DBs, copied to several nodes. But perfect durability doesn't exist, disks lie, fsync has bugs, correlated faults and corruption happen; combine disk + replication + backups.

---

Single-Object and Multi-Object Operations

• Multi-object transactions modify several rows/documents at once (e.g., inserting an email and incrementing an unread counter). They prevent anomalies like seeing the new email but the old counter (a dirty read). In relational DBs, everything between BEGIN TRANSACTION and COMMIT on one TCP connection is one transaction. Many nonrelational DBs lack this grouping (a multi-put may partly succeed).
• Single-object writes: storage engines provide atomicity (via a log) and isolation (via a per-object lock) for one object, so a 20 KB document write isn't left half-applied or read mid-write. Some DBs add atomic increment and compare-and-set (CAS / conditional write), useful but not full transactions.
• Why multi-object transactions are needed: foreign-key references must stay valid; denormalized data must stay in sync; and secondary indexes must update with the data (otherwise a record can appear in one index but not another).
• Handling errors and aborts: the whole point of rollback is safe retry. But ORMs (Rails, Django) often don't retry. Retry caveats: a "failed" commit may have actually succeeded (duplicate on retry → need idempotence); retries worsen overload/contention (use backoff); don't retry permanent errors (constraint violations); beware side effects (don't resend emails, see 2PC); a crash mid-retry loses the write.

---

Weak Isolation Levels

Race conditions arise only when one transaction reads/writes data another is concurrently modifying. They're hard to test (timing-dependent) and have caused real losses (bankrupting a Bitcoin exchange, corrupting customer data). Serializable isolation prevents all of them but costs performance, so weaker levels are common, and an attacker can deliberately trigger concurrency bugs.

Fullscreen

Read committed

Two guarantees: no dirty reads (you only see committed data) and no dirty writes (you only overwrite committed data).

• Dirty read = seeing another transaction's uncommitted writes. Bad because you might see a partial update or data that's later rolled back (cascading aborts).
• Dirty write = overwriting another transaction's uncommitted write. Prevented with row-level locks held until commit/abort (e.g., prevents the car sale going to one buyer but the invoice to another). Does not prevent the lost-update race (two increments → 43 instead of 44, because the second write happens after the first commits).
• Implementation: write locks prevent dirty writes; dirty reads are prevented not by read locks (a long writer would block all readers) but by remembering the old committed value and serving it until the new one commits (the basis of MVCC). Default in Oracle, PostgreSQL, SQL Server. Read uncommitted is even weaker (no dirty writes, but dirty reads allowed).

Snapshot isolation and repeatable read

Read committed still allows read skew (a nonrepeatable read):

Fullscreen

Tolerable for a quick refresh, but fatal for backups, analytics, and integrity checks (which scan large parts of the DB and would see different points in time). Snapshot isolation fixes this: each transaction reads from a consistent snapshot, all data committed as of the transaction's start. Supported by PostgreSQL, MySQL/InnoDB, Oracle, SQL Server (and is the highest level in some, plus warehouses like BigQuery).

Multiversion concurrency control (MVCC) implements it:

Fullscreen

• Each transaction gets a monotonically increasing txid; writes are tagged with it. Updates become delete + insert; old versions linger until garbage collection.
• Visibility rules: a row version is visible if the transaction that inserted it had committed before you started, and it isn't (yet) deleted by a transaction that committed before you started. In-progress, aborted, and later transactions' writes are ignored.
• Immutable/copy-on-write B-trees (CouchDB, Datomic, LMDB) give each write a new root = a snapshot, no txid filtering needed.
• Naming confusion: snapshot isolation is called "repeatable read" in PostgreSQL, "serializable" in Oracle; MySQL's "repeatable read" is weaker; Db2's "repeatable read" means serializable. The SQL standard predates snapshot isolation, so "repeatable read" is ambiguous and "nobody really knows what it means."

Preventing lost updates

A lost update happens in a read-modify-write cycle when two transactions both read, modify, and write back, the later write clobbers the earlier (counters, JSON edits, full-page wiki saves).

Fullscreen

• Atomic operations (best when possible), DB does the cycle under an exclusive lock or on one thread (ORMs make it easy to accidentally write unsafe cycles).
• Explicit locking, SELECT ... FOR UPDATE when app logic (e.g., game-move validity) can't be a single query; risk of deadlock (DB aborts one, app retries).
• Automatic detection, PostgreSQL repeatable read, Oracle serializable, SQL Server snapshot detect and abort lost updates; MySQL/InnoDB does not (so by some definitions it isn't true snapshot isolation). Less error-prone (no special app code).
• Conditional write (CAS) / optimistic locking, UPDATE ... WHERE content = 'old' or a version-number check; retry if it didn't apply. (MVCC visibility has an exception so WHERE sees the latest value.)
• Replicated DBs, locks/CAS assume one up-to-date copy, so multi-leader/leaderless use siblings + merge; commutative ops (counters, set-add) merge safely (CRDTs), but LWW loses updates.

Write skew and phantoms

A subtler conflict where two transactions read the same objects, then update different objects, each individually valid but jointly violating an invariant. Classic example: two on-call doctors both check "≥2 on call → OK," then both go off call → zero doctors on call.

Fullscreen

• A generalization of lost update (if both updated the same object, it'd be a dirty write / lost update).
• Prevention is limited: atomic single-object ops don't help; automatic lost-update detection doesn't catch it; multi-object constraints usually aren't supported. Options: serializable isolation (the real fix), or explicit row locks (SELECT ... FOR UPDATE) when the read returns rows to lock.
• More examples: meeting-room double-booking, two players to the same square, claiming a username (here a uniqueness constraint works), double-spending.
• Phantoms: when a write in one transaction changes the result of another's search query, and the read returned no rows, so there's nothing to lock. Snapshot isolation avoids phantoms in read-only queries but not in these read-write patterns.
• Materializing conflicts: artificially create lock rows (e.g., a row per room-timeslot) so there's something to SELECT ... FOR UPDATE. Ugly, error-prone, a last resort, prefer serializable isolation.

---

Serializability

The strongest level: results identical to some serial order, preventing all race conditions. Three implementation techniques:

Fullscreen

Actual serial execution

Run one transaction at a time on a single thread, serializable by definition. Made feasible by (1) cheap RAM (whole active dataset in memory) and (2) short OLTP transactions (long analytics run on snapshots). Used by VoltDB/H-Store, Redis, Datomic.

• Interactive multi-statement transactions are too slow (network round-trips would stall the single thread), so the whole transaction is submitted as a stored procedure. Modern engines use general-purpose languages (Java, Lua, JavaScript) instead of PL/SQL.
• VoltDB also uses stored procedures for state machine replication (run the same deterministic procedure on each replica).
• Sharding scales it across cores/nodes if each transaction stays within one shard; cross-shard transactions need lockstep coordination and are orders of magnitude slower (~1,000/sec in VoltDB).
• Constraints: transactions must be small/fast, dataset in memory, single-core write throughput (or shardable without cross-shard coordination).

Two-phase locking (2PL)

The dominant serializability method for ~30 years. 2PL ≠ 2PC (2PL = serializable isolation; 2PC = distributed atomic commit).

Fullscreen

• Writers block readers and readers block writers (the opposite of MVCC's "readers never block writers"). Shared lock for reads, exclusive lock for writes; held until end of transaction (the two phases: growing = acquire, shrinking = release).
• Deadlocks are common; the DB detects and aborts one (app retries).
• Performance is the downside: lots of lock overhead and reduced concurrency → unstable, high tail latencies; one big read locks a whole table.
• Predicate locks prevent phantoms by locking all objects matching a condition (even ones that don't exist yet). Too slow in practice, so DBs use index-range (next-key) locks, a coarser approximation attached to an index entry (lock all of room 123, or all rooms noon-1pm). Falls back to a whole-table lock if no suitable index.

Serializable snapshot isolation (SSI)

Full serializability with a small penalty over snapshot isolation (first described 2008; used by PostgreSQL serializable, CockroachDB, FoundationDB). It's optimistic: let transactions run, then check at commit whether isolation was violated and abort if so (2PL and serial execution are pessimistic).

• Built on snapshot isolation (MVCC) + conflict detection. Performs well with spare capacity and low contention; badly under high contention (many aborts). Commutative atomic ops reduce contention.
• It detects when a transaction acted on an outdated premise (the read result changed before commit) via two mechanisms:
  • Stale MVCC reads: track when a transaction ignored another's uncommitted write; at commit, if that write has since committed, abort. (Wait until commit so read-only transactions and aborted writers don't cause needless aborts.)
  • Writes affecting prior reads: like index-range locks but as a non-blocking tripwire, a write notifies transactions that previously read the affected range that their read is now stale; the loser aborts at commit.
• Advantages: readers don't block writers (predictable latency), and unlike serial execution it scales across cores/machines (FoundationDB distributes conflict checking). Needs read/write transactions to be fairly short (long ones hit conflicts), but tolerates long read-only ones.

---

Distributed Transactions

When a transaction spans multiple nodes (shards, or a global secondary index), concurrency control is similar to single-node, but atomicity is the hard new problem. On one node, atomicity hinges on a single disk writing the commit record. Across nodes, naively sending commit to all risks committing on some and aborting on others, and a commit can't be retracted (others may have already read it). Ensuring all-or-nothing is the atomic commitment problem.

Two-phase commit (2PC)

Fullscreen

A coordinator (transaction manager) runs two phases:

1. Prepare: ask every participant "can you commit?" Each that replies yes must guarantee it can commit under all circumstances (data on disk, no conflicts), it surrenders the right to abort.
2. Commit/abort: if all said yes, the coordinator writes its decision to disk (the commit point) and tells everyone to commit; otherwise abort. After the commit point, it retries forever until every participant acknowledges.

The two points of no return (a yes vote, and the coordinator's logged decision) are what make 2PC atomic (single-node commit fuses them into one).

• Coordinator failure → in-doubt (uncertain) participants: once a participant votes yes, it can't decide alone; if the coordinator crashes before sending the outcome, the participant must wait (a timeout can't help, unilateral commit or abort risks inconsistency). Recovery: the coordinator reads its log and resolves in-doubt transactions; a lost coordinator log requires manual intervention.
• 3PC (three-phase commit) aims to be nonblocking but assumes bounded network delay; with unbounded delays/pauses (Ch. 9) it can't guarantee atomicity. The real fix is replacing the single coordinator with a fault-tolerant consensus protocol (Ch. 10).

Distributed transactions across systems

Two kinds, often conflated:

• Database-internal, all participants run the same software (NewSQL: CockroachDB, TiDB, Spanner, FoundationDB, YugabyteDB; also Kafka). Free to use optimized protocols → works well.
• Heterogeneous, different technologies (two vendors' DBs, or a DB + a message broker). Enables exactly-once message processing (acknowledge a message iff its DB writes committed). Standard: XA (a C API, since 1991; via JTA in Java) for 2PC across systems.

XA's problems:

• Holding locks while in doubt: a stuck transaction keeps its row locks (possibly forever if the coordinator's log is lost), blocking other transactions, large parts of the app can hang.
• Recovery: orphaned in-doubt transactions need manual commit/abort; heuristic decisions (unilateral resolution) are a euphemism for "probably breaking atomicity."
• The coordinator is a single point of failure, its local log is critical state, and the application code is also a SPOF (coordinator and participants can only talk through it). XA is a lowest common denominator (no cross-system deadlock detection, no SSI).

Database-internal distributed transactions fix XA's problems by replicating the coordinator (consensus, automatic failover), letting coordinator and shards talk directly, replicating shards, and coupling commit with distributed concurrency control, enabling snapshot isolation or SSI across shards.

Exactly-once message processing without distributed transactions

You don't actually need a distributed transaction. Make processing idempotent with a message-ID table: in one DB transaction, check if the message ID is already recorded; if so, drop it; otherwise record the ID, do the work, and commit; then acknowledge the broker; later delete the ID. A crash at any point is safe (a uniqueness constraint on message IDs prevents duplicates). This needs only DB-internal transactions, and Kafka Streams uses a similar approach (Ch. 12).

---

Real-world examples and analogies

• Atomicity, sending a group text that either reaches everyone or no one. If the message half-sends and fails, you don't want some friends to get it and others not; atomicity is "deliver to all, or to none, then let me retry cleanly."
• Isolation, two people editing the same shared spreadsheet cell. Without isolation they overwrite each other unpredictably; serializable isolation makes it look like they took turns, even if they typed at once.
• Dirty read, peeking at a half-cooked dish. Tasting food the chef is still assembling (uncommitted) and judging the meal on it, then the chef changes the recipe (rolls back). Read committed makes you wait until the plate is served.
• Read skew, checking two bank apps mid-transfer. You glance at savings ($500, before the deposit) and checking ($400, after the withdrawal) and panic that $100 vanished. A consistent snapshot shows both accounts at one instant.
• MVCC, a document's version history. Instead of overwriting, the database keeps every version with timestamps; your long-running read keeps seeing the version that existed when you started, while others edit freely.
• Lost update, two cashiers updating one tip jar from memory. Each reads "$42," adds a dollar, writes "$43", but it should be $44. Atomic increment ("add $1 directly") fixes it.
• Write skew, two bandmates both quitting because "the other one will cover the gig." Each checks "two of us are available, so I can bail," both bail, and nobody shows up. Individually fine, jointly disastrous.
• Phantom, booking a room nobody has booked yet. You can't put a "reserved" sticky note on a booking that doesn't exist; that's why locking the absence of rows needs predicate/index-range locks.
• 2PL pessimism vs SSI optimism, a shared printer. 2PL: take the key and lock the printer room while you work (others wait). SSI: everyone prints freely, and if two jobs clashed, one reprints. Optimism wins when clashes are rare.
• 2PC, a wedding officiant. "Do you?" / "Do you?" (prepare). Once both say "I do," neither can take it back; the officiant declaring you married (writing the decision) is the commit point. If the officiant faints right after, you're still married, you just ask later.
• In-doubt transaction, a guest who said "I do" then the officiant vanished. They can't un-say it and can't proceed alone; they must wait for the officiant to return and announce the outcome.

---

Cheat-sheet flashcards

Cover the answer and recall it.

• What does ACID stand for? → Atomicity, Consistency, Isolation, Durability.
• ACID atomicity really means? → Abortability, all-or-nothing, abort discards partial writes.
• Is atomicity about concurrency? → No, that's isolation.
• Who is responsible for ACID consistency? → Mostly the application (the DB enforces only declared constraints).
• What is the textbook ideal of isolation? → Serializability.
• Does perfect durability exist? → No, combine disk, replication, and backups.
• Multi-object transaction? → Several rows/documents changed as one unit.
• What does single-object atomicity/isolation prevent? → Half-written or mid-write reads of one object.
• Dirty read? → Seeing another transaction's uncommitted writes.
• Dirty write? → Overwriting another transaction's uncommitted write.
• Read committed guarantees? → No dirty reads, no dirty writes.
• Read skew / nonrepeatable read? → Seeing different parts of the DB at different times.
• Snapshot isolation? → Each transaction reads from a consistent snapshot as of its start.
• How is snapshot isolation implemented? → MVCC (multiple row versions + visibility rules by txid).
• MVCC mantra? → Readers never block writers, writers never block readers.
• Why is "repeatable read" confusing? → Different DBs mean different things by it.
• Lost update? → Two read-modify-write cycles clobber each other.
• Four lost-update fixes? → Atomic ops, explicit locks, auto-detection, compare-and-set.
• What is optimistic locking? → Update only if a version number/value is unchanged.
• Write skew? → Two transactions read the same data, update different objects, jointly breaking an invariant.
• What's the only general fix for write skew? → Serializable isolation (or explicit locks / constraints in special cases).
• Phantom? → A write changing another transaction's search-query result.
• Materializing conflicts? → Creating artificial lock rows for nonexistent objects (last resort).
• Three ways to get serializability? → Serial execution, 2PL, SSI.
• What made serial execution feasible? → Cheap RAM (in-memory) + short OLTP transactions.
• How do serial-execution DBs avoid network stalls? → Stored procedures.
• 2PL lock modes? → Shared (read) and exclusive (write).
• 2PL's two phases? → Growing (acquire locks), shrinking (release at end).
• 2PL vs MVCC on blocking? → 2PL: readers/writers block each other; MVCC: they don't.
• Predicate lock vs index-range lock? → Lock all matching objects vs a coarser index-based approximation.
• 2PL vs 2PC? → Serializable isolation vs distributed atomic commit (unrelated).
• Pessimistic vs optimistic concurrency control? → Block on possible danger (2PL) vs run and check at commit (SSI).
• How does SSI detect conflicts? → Stale MVCC reads + writes affecting prior reads (tripwire).
• The atomic commitment problem? → Make all nodes commit or all abort, never a mix.
• 2PC's two phases? → Prepare (can you commit?) then commit/abort.
• 2PC commit point? → When the coordinator writes its decision to disk.
• What is an in-doubt transaction? → A participant that voted yes but hasn't heard the coordinator's decision.
• Why is 2PC "blocking"? → Participants must wait for the coordinator to recover.
• Why does 3PC not work in practice? → It assumes bounded network/response delays.
• What is XA? → A standard C API for 2PC across heterogeneous systems.
• Big risk of XA in-doubt transactions? → Locks held (maybe forever), blocking other transactions.
• Exactly-once without distributed transactions? → Idempotent processing via a message-ID table.

---

Common interview questions

1. What problem do transactions solve, and what are the ACID properties? (Group reads/writes; all-or-nothing + isolation + durability; consistency is partly the app's job.)
2. Distinguish a dirty read, a dirty write, and read skew. (Uncommitted read; overwriting uncommitted; inconsistent snapshot across time.)
3. What is read committed, and what does it fail to prevent? (No dirty reads/writes; still allows read skew, lost updates, write skew.)
4. Explain snapshot isolation and how MVCC implements it. (Consistent snapshot per transaction; multiple versions + txid visibility rules; readers don't block writers.)
5. What is the lost-update problem and how do you prevent it? (Read-modify-write clobber; atomic ops, locks, auto-detection, CAS.)
6. What is write skew, why is it hard to prevent, and what's the real fix? (Read same / write different objects breaking an invariant; needs serializable isolation.)
7. What are phantoms, and how do predicate / index-range locks address them? (A write changes another's query result; lock matching-or-future rows.)
8. Compare the three ways to achieve serializability. (Serial execution, 2PL, SSI, performance/scaling trade-offs.)
9. Pessimistic vs optimistic concurrency control, give an example of each. (2PL blocks; SSI runs and aborts on conflict.)
10. Walk through two-phase commit. What happens if the coordinator crashes? (Prepare/commit, commit point; participants go in-doubt and must wait.)
11. Why are XA / heterogeneous distributed transactions problematic? (In-doubt locks, coordinator + app SPOF, lowest-common-denominator, no cross-system deadlock/SSI.)
12. How can you achieve exactly-once message processing without distributed transactions? (Idempotent processing with a message-ID dedup table inside one DB transaction.)

---

Key terms glossary

---

Forward links (where these ideas return)

• Linearizability, CAP, consensus, fault-tolerant coordinators → Chapter 10
• Network faults, process pauses, unbounded delays (why 3PC fails) → Chapter 9
• State machine replication and shared logs → Chapter 10
• Conflict resolution, CRDTs, siblings → Chapter 6
• Exactly-once semantics in stream processing (Kafka Streams) → Chapter 12
• Denormalization and secondary indexes → Chapters 3 and 7