Transactions

Chapter 7 is about one of the most important abstractions in databases: the transaction. A transaction groups several reads and writes into one logical unit that either entirely succeeds (commit) or entirely fails (abort/rollback). This lets the application pretend that concurrency problems and partial failures don't exist — a massive simplification. But transactions are not a law of nature; they're a trade-off, and many distributed datastores weakened or dropped them for performance. This chapter examines what guarantees transactions actually provide, the surprising ways "isolation" can leak, and what it really takes to be fully serializable.

The Meaning of ACID

The safety guarantees of transactions are usually summarized by the acronym ACID — but Kleppmann stresses that the term has been diluted into a marketing slogan, with each database meaning something slightly different. Taken precisely:

• Atomicity — a transaction's writes are all-or-nothing. If anything goes wrong partway, the whole thing is aborted and any writes already made are discarded. A better name would be abortability: the key feature is the ability to throw everything away on error and safely retry.
• Consistency — the application's invariants (e.g. credits equal debits) hold. This is the odd one out: it's a property of the application, not the database. The database can enforce some constraints, but it's ultimately the app's job to define and preserve invariants. (Joe Hellerstein has noted the C was tacked on to make the acronym work.)
• Isolation — concurrently executing transactions don't step on each other; the result is as if they ran one at a time. This is the rich, subtle part, and most of the chapter.
• Durability — once a transaction commits, its data won't be lost, even on crash. Achieved with write-ahead logs and replication. (Perfect durability doesn't exist — every copy could be destroyed — so it's about reducing risk.)

Single-Object and Multi-Object Operations

Atomicity and isolation are easy to provide for a single object (most datastores do). The hard, valuable case is multi-object transactions, where several rows/documents must change together — e.g. transferring money between accounts, or keeping a denormalized counter in sync with the rows it counts. Many distributed stores abandoned multi-object transactions because they're hard to implement across partitions; the chapter argues they're more valuable than the NoSQL movement assumed. When an operation does fail and aborts, retrying is the right recovery — but with caveats (retrying a transaction that actually succeeded but whose acknowledgement was lost can double-apply side effects).

Weak Isolation Levels

Serializable isolation has a performance cost, so databases historically offered weaker levels that prevent some anomalies but not others. These weak levels are the source of countless subtle bugs, precisely because they look fine in testing and only break under concurrency.

Read Committed

The most basic level. It makes two guarantees: no dirty reads (you only see data that has been committed — never another transaction's uncommitted writes) and no dirty writes (you only overwrite data that has been committed — preventing two transactions' writes from interleaving badly). It's typically implemented by holding a write lock per row, while reads return the last committed value (remembering the old value while a write is in progress) so reads never block.

Snapshot Isolation and Repeatable Read

Read committed still allows read skew (a nonrepeatable read): if you read two rows at moments straddling another transaction's commit, you can see an inconsistent combination — like a bank balance that appears to have lost money mid-transfer. Snapshot isolation fixes this: each transaction reads from a consistent snapshot of the database as of the moment it started, so it sees all-or-nothing of every other transaction. The key implementation idea is multi-version concurrency control (MVCC): the database keeps several committed versions of each object, and each transaction sees the versions that were committed when it began. The big payoff: readers never block writers and writers never block readers. Snapshot isolation is invaluable for long-running reads like backups and analytics. (Confusingly, many databases call this "repeatable read.")

Preventing Lost Updates

The lost update problem is a read-modify-write race: two transactions read a value, each modifies it, and one write clobbers the other.

# two clients incrementing the same counter (starts at 42)
T1: read(counter) → 42
T2: read(counter) → 42         # both saw 42
T1: write(counter = 43)
T2: write(counter = 43)         # should be 44! one increment lost

Solutions, roughly in order of preference:

• Atomic write operations — let the database do the increment (UPDATE ... SET n = n + 1); the best option when applicable.
• Explicit locking — SELECT ... FOR UPDATE locks the rows you're about to modify.
• Automatic lost-update detection — some databases (with snapshot isolation) detect the race and abort the offending transaction.
• Compare-and-set — only write if the value hasn't changed since you read it.
• Conflict resolution — for replicated data, allow concurrent writes and merge them (avoiding LWW, which simply drops one).

Write Skew and Phantoms

Write skew is a generalization of the lost update: two transactions read the same set of objects, then each updates different objects based on what it read — and together they violate an invariant that each alone would have preserved. The canonical example: a hospital requires at least one doctor on call. Two doctors, both currently on call, simultaneously request to go off call; each transaction checks "is someone else still on call? yes" and proceeds — leaving nobody on call. This is a phantom: a write in one transaction changes the result of a search query in another. Snapshot isolation does not prevent write skew, because the two transactions read and wrote different rows and never directly conflicted. Defenses include explicit locks on the queried rows, or materializing conflicts by introducing rows to lock on.

Serializability

Serializable isolation is the strongest: it guarantees that even though transactions may run concurrently, the end result is the same as if they had run one at a time, serially, in some order. It rules out all the race conditions above. There are three ways to implement it.

Actual Serial Execution

The simplest idea: literally execute one transaction at a time, on a single thread. This was impractical for decades, but two changes made it viable: RAM got cheap enough to hold many datasets entirely in memory, and OLTP transactions are usually short and few. Systems like VoltDB and Redis do this. To make it work you must avoid interactive multi-statement transactions (network round-trips would stall the single thread), so transactions are submitted as stored procedures that run to completion. Throughput is limited to a single CPU core, so to scale you partition the data — but cross-partition transactions then need coordination and are much slower.

Two-Phase Locking (2PL)

For ~30 years the standard algorithm for serializability. The rule: readers and writers block each other. To read an object you take a shared lock; to write it you take an exclusive lock; a write must wait for all readers' shared locks to release, and vice versa (this is the difference from snapshot isolation, where readers and writers never block). Locks are held until the transaction ends. To prevent phantoms, 2PL uses predicate locks or, more practically, index-range locks that cover a range of possible matches. 2PL is correct but has real downsides: lots of locking overhead, frequent deadlocks (transactions waiting on each other, requiring detection and abort), and highly variable, often poor, latency.

Serializable Snapshot Isolation (SSI)

A newer algorithm (2008) that delivers full serializability with much better performance, used by PostgreSQL's serializable level and FoundationDB. It's optimistic: rather than blocking on locks, transactions proceed on a consistent snapshot (as in snapshot isolation), and the database tracks the reads and writes. At commit time it checks whether the transaction's reads are still valid — i.e. whether a concurrent transaction wrote something that would have changed this transaction's decisions. If a dangerous read-write dependency is detected, the database aborts the offending transaction and lets it retry. SSI performs well when contention is low (few aborts) and avoids the lock contention of 2PL, while still ruling out write skew and phantoms.