Many things go wrong in a data system: the database crashes mid-write, the network drops the acknowledgement, two clients overwrite each other, a reader sees a half-finished update. A transaction groups several reads and writes into one unit that either succeeds entirely (commit) or fails entirely (abort), so the application can ignore most partial-failure scenarios and simply retry. Transactions are a simplifying abstraction, not a law of nature, and the guarantees they offer vary enormously between products.

This chapter matters for interviews because almost every "ACID" database runs by default at a weak isolation level that permits real concurrency bugs. Knowing which anomalies read committed, snapshot isolation and serializable isolation prevent, and how each is implemented, separates engineers who have debugged production race conditions from those who have only read the acronym.

In one minute

• ACID: atomicity is abortability, consistency is the application's invariants, isolation means concurrent transactions do not interfere, durability means committed data survives crashes. The letters hide very different guarantees in different databases.
• Read committed prevents dirty reads and dirty writes with row locks for writers and old/new value pairs for readers. It allows read skew, lost updates and write skew.
• Snapshot isolation (PostgreSQL and MySQL call it repeatable read, Oracle calls it serializable) gives each transaction a consistent view as of its start, implemented with MVCC. Readers never block writers and vice versa.
• Lost updates are fixed by atomic operations, SELECT ... FOR UPDATE, automatic detection (PostgreSQL, Oracle, SQL Server; not MySQL InnoDB) or compare-and-set.
• Write skew generalises lost updates: two transactions read overlapping data and write different objects, breaking an invariant. Phantoms are the case where the read is a search whose result another transaction changes. Snapshot isolation does not prevent these.
• Serializability rules out all race conditions, via actual serial execution (VoltDB, Redis), two-phase locking with predicate or index-range locks, or serializable snapshot isolation (PostgreSQL 9.1+, FoundationDB), which detects conflicts optimistically at commit.
• Weak isolation is the default almost everywhere; when correctness under concurrency matters, ask for serializable explicitly and understand its cost.

The slippery concept of a transaction

Transactions date to IBM System R in 1975 and today's relational databases still follow that design. The NoSQL wave abandoned them for replication and partitioning, and two opposing myths emerged: that transactions are the enemy of scalability, and that they are essential for serious data. Both are hyperbole; transactions have real benefits and real costs.

The meaning of ACID

ACID (Härder and Reuter, 1983) has become so loose that one database's ACID is not another's, especially for isolation.

Atomicity. Not about concurrency (that is isolation). If a transaction makes several writes and a fault occurs partway (crash, network loss, full disk, constraint violation), the transaction aborts and the database discards its writes so far. Without this a client cannot tell what took effect or retry safely. Abortability would be a better name.

Consistency. The C means application-defined invariants (credits balance debits, foreign keys resolve) hold before and after a transaction. The database can enforce some via constraints but cannot stop you writing bad data that passes the schema. Consistency is the application's property; Joe Hellerstein has noted the C was added mainly to make the acronym work.

Isolation. Concurrent transactions do not interfere. The textbook formalisation is serializability: results as if transactions ran one after another. In practice most databases offer the weaker snapshot isolation by default.

Durability. Committed data is not lost after a crash: on one node, written to non-volatile storage with a write-ahead log; in a replicated database, copied to some nodes before acknowledging. Perfect durability does not exist (correlated disk failures, firmware bugs, SSDs losing data unpowered, fsync bugs); replication and backups reduce risk rather than eliminating it.

Single-object and multi-object operations

Multi-object atomicity and isolation require knowing which operations belong together; relational databases tie this to the client connection between BEGIN and COMMIT. Many non-relational databases lack such grouping, so a multi-put may succeed on some keys and fail on others. Single-object writes also need atomicity (no half-written 20 KB document) and isolation (no reader sees a half-updated value); storage engines universally provide these with a log and a per-object lock, and some add atomic increment and compare-and-set. Calling those "lightweight transactions" or "ACID" stretches the term. Multi-object transactions are needed for foreign-key references, denormalised copies in document databases and secondary indexes. Many distributed stores dropped them as hard across partitions; some are adding them back (Chapter 9).

Handling errors and aborts

The ACID philosophy is that an aborted transaction is simply retried; leaderless stores take a "best effort" view and leave recovery to the application. Retrying is imperfect: a lost commit acknowledgement makes a retry execute twice unless deduplicated; retrying under overload worsens it (bound retries, use exponential backoff); retrying permanent errors is pointless; side effects outside the database (sending email) repeat, which needs two-phase commit (Chapter 9); and a client crash mid-retry loses the data.

Weak isolation levels

Concurrency bugs appear only when two transactions touch the same data at the same time, which makes them rare in tests and devastating in production. Databases hide concurrency with transaction isolation, ideally serializable, but its cost pushes most to weaker defaults. Learn the anomalies rather than trusting a vendor's label.

Read committed

Two guarantees: no dirty reads (you only see committed data) and no dirty writes (you only overwrite committed data). Default in Oracle, PostgreSQL, SQL Server 2012+ and MemSQL. A dirty read would show a user a new unread count before the inbox list updates, or expose data later rolled back; a dirty write could let two buyers racing for a car end up with the listing naming Bob and the invoice going to Alice.

Dirty writes are prevented with row-level locks held until commit or abort. Dirty reads could use the same locks, but one long-running writer would stall every reader, so databases instead keep the old committed value next to the new uncommitted one and serve the old value until the writer commits.

Snapshot isolation and repeatable read

Read committed allows read skew (nonrepeatable read): Alice reads account 1 (500), a transfer of 100 from account 2 to account 1 commits, she reads account 2 (400), total 900. Each read saw committed data, but at different points in time. Tolerable on a web page; fatal for backups (a restored backup permanently contains inconsistent data) and for analytic queries and integrity checks scanning data that changes underneath them.

Snapshot isolation fixes this: each transaction reads from a consistent snapshot of everything committed when it started, ignoring later commits. PostgreSQL, MySQL (InnoDB), Oracle and SQL Server support it.

MVCC. Writers take locks against other writers; readers take no locks: readers never block writers and writers never block readers. Because transactions started at different times need different states, the database keeps several committed versions of each object, which is multi-version concurrency control. Read committed uses a fresh snapshot per query; snapshot isolation one snapshot per transaction.

In PostgreSQL every transaction has an increasing txid; every row version carries created_by and deleted_by txids, and an update is a delete plus an insert. A version is visible if its creator committed before the reader started, was not aborted, and was not in progress at the reader's start, and it has not been deleted by such a transaction. Invisible deleted versions are garbage-collected (vacuum). Indexes either point at all versions and filter at query time (PostgreSQL skips index updates when the new version fits on the same page) or use append-only, copy-on-write B-trees where each write creates a new root that is itself a snapshot (CouchDB, Datomic, LMDB).

Naming. Oracle calls this level serializable (it is not). PostgreSQL and MySQL call it repeatable read, because the SQL standard's definition predates snapshot isolation and is ambiguous. IBM DB2's repeatable read means serializable. Always ask which anomalies are prevented.

Preventing lost updates

Two writers can conflict beyond dirty writes. The lost update occurs when two read-modify-write cycles interleave and the second write clobbers the first: incrementing a counter, editing a field inside a JSON document, two users editing a wiki page.

Technique	How it works	Caveats
Atomic write operations	UPDATE counters SET value = value + 1 WHERE key = 'foo'; MongoDB modifiers; Redis commands; implemented via an exclusive object lock or single-threading	Not all logic fits; ORMs make unsafe read-modify-write easy
Explicit locking	SELECT ... FOR UPDATE locks the rows read so concurrent writers wait	Easy to forget; introduces deadlocks
Automatic detection	Transactions run in parallel; the database detects the lost update and aborts the later writer	PostgreSQL repeatable read, Oracle serializable, SQL Server snapshot isolation do this; MySQL/InnoDB repeatable read does not
Compare-and-set	UPDATE ... SET content = 'new' WHERE id = 1234 AND content = 'old' applies only if unchanged	Unsafe if the WHERE clause reads an old snapshot
Conflict resolution (replicated)	Locks and CAS assume one copy; multi-leader and leaderless stores keep siblings and merge, or use commutative operations (Riak counters)	LWW, the default in many replicated databases, loses updates

Write skew and phantoms

Alice and Bob are the two on-call doctors; the rule requires at least one. Both feel ill and click "go off call" at the same moment. Each transaction checks that at least two are on call (both see two under snapshot isolation) and updates its own row. Both commit; nobody is on call. This is write skew: two transactions read the same objects and update different ones. It is neither a dirty write nor a lost update, since they changed different rows, but it violates the invariant.

Write skew generalises lost updates (the case where both write the same object), and the lost-update tools mostly fail: single-object atomic operations do not span rows; automatic lost-update detection does not fire; multi-object constraints need triggers or materialized views. What works is true serializable isolation, or explicitly locking the rows the decision depends on with FOR UPDATE on the doctors query.

The same shape recurs: a room-booking system checking for overlaps before inserting; a game checking a square is empty; claiming a username; preventing double-spending. A SELECT checks a precondition, code decides, a write happens, and the write invalidates the precondition for others. When the precondition is that rows do not exist (no overlapping booking, no such username), locking fails because FOR UPDATE cannot lock absent rows. A write in one transaction changing the result of a search in another is a phantom. Snapshot isolation avoids phantoms for read-only queries; in read-write transactions they cause write skew.

Materializing conflicts works around this by creating lockable rows in advance: a table of every room-and-15-minute-slot for six months, which a booking locks with FOR UPDATE before checking and inserting. It leaks concurrency control into the data model and is error-prone; use it only when a serializable isolation level is unavailable.

Serializability

Isolation levels are confusing, inconsistently implemented and impossible to audit by reading code. Serializable isolation guarantees that the outcome of concurrent transactions equals some serial order. The question is how it is implemented and what it costs. Three families exist.

Actual serial execution

Run transactions literally one at a time on a single thread. This became practical around 2007: RAM got cheap enough to hold the active dataset, so transactions stop waiting on disk, and OLTP transactions are short, while long analytic queries run on a snapshot outside the loop. VoltDB/H-Store, Redis and Datomic do this, and a single-threaded loop can beat a multithreaded system by avoiding locking overhead. Throughput is limited to one CPU core.

Stored procedures. An interactive transaction (query, wait, decide, query again) spends most of its time on network round trips, which under serial execution block everyone, so the whole transaction must be submitted up front as a stored procedure. Their reputation is poor: vendor-specific languages (PL/SQL, T-SQL, PL/pgSQL) lacking libraries; code in the database is hard to version, test, debug and monitor; one bad procedure hurts every tenant. Modern systems use general-purpose languages (VoltDB Java or Groovy, Datomic Java or Clojure, Redis Lua). VoltDB also replicates by executing the same procedure on each replica, so procedures must be deterministic.

Partitioning. To exceed one core, give each partition its own thread, which works when most transactions touch a single partition (VoltDB requires designing the schema for this). Cross-partition transactions need lock-step coordination and are dramatically slower (VoltDB has reported on the order of a thousand cross-partition writes per second). The constraints: small, fast transactions; the active dataset in memory; write throughput that fits one core or partitions cleanly.

Two-phase locking (2PL)

For about thirty years two-phase locking was the only widely used serializable algorithm. It is unrelated to two-phase commit (2PC, Chapter 9), which handles atomic commit across nodes.

2PL is far stricter than snapshot isolation: several transactions may read an object concurrently, but as soon as anyone writes, exclusive access is required. Writers block readers and readers block writers. Each object has a lock with shared and exclusive modes: readers take shared locks (waiting if an exclusive lock is held), writers take exclusive locks (waiting if any lock is held), a reader that then writes upgrades. All locks are held until commit or abort. The two phases are acquiring locks during execution and releasing them at the end. MySQL InnoDB and SQL Server use it for serializable, DB2 for repeatable read.

Deadlocks are frequent (A waits for B, B waits for A); the database detects the cycle and aborts one transaction for the application to retry. Performance is the big cost: lock overhead, but mainly reduced concurrency, where one slow transaction stalls a queue of others, producing unstable high-percentile latency.

Predicate locks handle phantoms: a lock on all objects matching a search condition (all bookings for room 123 between noon and 1 p.m.), including objects that do not yet exist. Readers check for conflicting exclusive predicate locks; writers check whether a new or modified object matches any existing predicate lock. With predicate locks, 2PL prevents all write skew.

Index-range locks (next-key locks) approximate predicate locks because checking every predicate against every write is slow: widen the predicate to an index-shaped range, locking all bookings for room 123 via the room index, or all bookings in that time range via the time index. Anything matching the original predicate also matches the approximation, so the lock is safe but coarser. Without a suitable index the database falls back to a shared lock on the entire table.

Serializable snapshot isolation (SSI)

Serializable snapshot isolation, from Michael Cahill's 2008 PhD thesis, provides full serializability with a small penalty over snapshot isolation. PostgreSQL's serializable level has used it since 9.1; FoundationDB uses a distributed variant.

2PL is pessimistic: wait if anything might go wrong. Serial execution is pessimistic to the extreme. SSI is optimistic: transactions proceed without blocking, and at commit the database checks whether isolation was violated, aborting if so. This performs badly under high contention (many wasted aborts) and well at low contention with spare capacity; commutative atomic operations reduce contention.

SSI builds on snapshot isolation and adds detection of serialization conflicts. The insight: a transaction reads data, decides, and writes; by commit time the data it read may be outdated, so its decision rests on a stale premise. Two cases must be detected.

Stale MVCC reads (an uncommitted write preceded the read). When transaction 43 reads the doctors table, visibility rules hide transaction 42's uncommitted change. If 42 commits later, 43's premise is stale. The database records when a transaction ignores another's writes due to visibility rules; at 43's commit it checks whether any ignored writes have since committed, and aborts 43 if so. It waits until commit rather than aborting immediately because 42 might abort, or 43 might be read-only, in which case there is no conflict. This is why read-only transactions are cheap under SSI.

Writes affecting prior reads (the write followed the read). This is the index-range lock idea without blocking. When a transaction reads via an index, the database records a SIREAD lock on the index entry (or table); it blocks nobody and survives the reader's commit. When another transaction writes that data, it finds the SIREAD locks and notifies the transactions that recently read it. If the writer commits first, the reader is aborted at its own commit; if the reader commits first, the writer aborts. In the doctors example, 42 and 43 notify each other and whichever commits second is aborted.

Performance. Tracking granularity is a trade-off between precision and overhead. Unlike 2PL, SSI never blocks a transaction waiting on another, so latency is predictable and read-only queries need no locks. Unlike serial execution, it is not limited to one core; FoundationDB distributes the detection. Long read-write transactions are likely to conflict and abort, so SSI favours short transactions, though it tolerates long ones better than the alternatives.

Design trade-offs

Isolation level	Dirty read	Read skew	Lost update	Write skew / phantom	Implementation	Cost
Read committed	Prevented	Possible	Possible	Possible	Row write locks + old/new values	Low
Snapshot isolation ("repeatable read")	Prevented	Prevented	Detected in PostgreSQL, Oracle, SQL Server; not MySQL	Possible	MVCC	Low to moderate
Serializable: 2PL	Prevented	Prevented	Prevented	Prevented	Shared/exclusive + index-range locks	High, unstable latency
Serializable: SSI	Prevented	Prevented	Prevented	Prevented	MVCC + conflict detection at commit	Moderate; aborts under contention
Serializable: serial execution	Prevented	Prevented	Prevented	Prevented	Single thread + stored procedures	One core per partition

Use the default (read committed or snapshot isolation) for simple reads and single-row writes. Reach for atomic operations and FOR UPDATE on read-modify-write cycles. When an invariant spans rows or a precondition is "no such row exists", run those transactions at serializable isolation with a retry loop; SSI in PostgreSQL makes this cheaper than people expect. Reserve serial execution for small, in-memory, partitionable OLTP.

Interview angle

• Define ACID and name the odd one out. Atomicity: all writes applied or all discarded on failure, so retries are safe. Consistency: application invariants hold, which is the application's job, so it does not belong with the others. Isolation: concurrent transactions do not interfere; textbook meaning is serializability but defaults are weaker. Durability: committed data survives crashes via a write-ahead log or replication.

• What does read committed prevent, and how without blocking readers? Dirty reads and dirty writes. Writers hold row locks until commit. Readers get the old committed value, kept alongside the new uncommitted one, instead of taking locks, so a long writer never stalls reads.

• Explain MVCC and PostgreSQL's visibility rule. Each row version carries the creating txid and, if deleted, the deleting txid; updates are delete-plus-insert. A transaction's snapshot records in-progress txids at its start. A version is visible if its creator committed before the snapshot and is not in that set, and it was not deleted by such a transaction. Readers take no locks; vacuum removes versions nobody can see.

• Two users increment a counter concurrently and one increment is lost. Give three fixes. An atomic update (SET value = value + 1) serialises the cycle in the database. SELECT ... FOR UPDATE locks the row before reading. Compare-and-set (WHERE value = old) with a retry on zero rows. PostgreSQL, Oracle and SQL Server also detect the lost update automatically under snapshot isolation; MySQL InnoDB does not.

• What is write skew and why doesn't snapshot isolation prevent it? Two transactions read the same data (two doctors on call), then each writes a different object based on it, breaking an invariant each preserved alone. Snapshot isolation only guarantees a consistent view; it does not recheck the premise at commit. Fix with serializable isolation, or lock the rows read with FOR UPDATE when they exist; when the precondition is the absence of rows (phantoms), only serializable isolation or materialised conflicts work.

• Compare 2PL and SSI. Both are serializable. 2PL is pessimistic: readers and writers block each other, locks are held to commit, phantoms need predicate or index-range locks, and latency is unstable with deadlocks. SSI is optimistic: snapshot reads without blocking, SIREAD locks and ignored-write tracking, and at commit any transaction with a stale premise aborts. SSI wins at low contention and for read-only work; 2PL may waste less under heavy contention.

• When would you choose actual serial execution? Short transactions, an active dataset in memory, write throughput fitting one core or partitioning cleanly, and the ability to ship deterministic stored procedures so no network round trips happen inside the loop. VoltDB and Redis take this path; cross-partition transactions remain slow.

• Design a meeting-room booking service that never double-books. A booking checks for overlaps then inserts. Under snapshot isolation two concurrent bookings both see no overlap and both insert (write skew via a phantom). Options: run at serializable isolation (PostgreSQL SSI) with retries; materialise conflicts with a pre-populated room-and-slot table locked via FOR UPDATE; or a unique constraint on (room, slot) if slots are discrete. Explain that rows that do not exist cannot be locked, which is why these are needed.

Pitfalls and misconceptions

• "The database is ACID, so my code is safe." Defaults are read committed or snapshot isolation, which allow lost updates (sometimes) and write skew (always).
• "Repeatable read means the same thing everywhere." PostgreSQL and MySQL: snapshot isolation. DB2: serializable. Oracle's "serializable": snapshot isolation.
• "Consistency is the database's job." The C is the application's invariants; the database enforces only declared constraints.
• "2PL and 2PC are the same." 2PL is single-node concurrency control; 2PC is atomic commit across nodes.
• "Snapshot isolation prevents lost updates." Only where detected: PostgreSQL, Oracle, SQL Server yes; MySQL InnoDB no.
• "Compare-and-set is always safe." If the WHERE clause is evaluated against an old snapshot, it can pass after another transaction committed a change.
• "Serializable is too slow to use." SSI has modest overhead at low contention, and serial execution can outperform locking for in-memory OLTP.
• "Retrying an aborted transaction is always correct." Retries double-apply when the acknowledgement was lost, amplify overload and repeat external side effects; make them idempotent and bounded.

Key terms

Term	Meaning
Atomicity (abortability)	On failure, a transaction's partial writes are discarded
Durability	Committed data survives crashes, via WAL or replication
Serializability	Concurrent transactions produce the same result as some serial order
Dirty read	Reading another transaction's uncommitted write
Dirty write	Overwriting another transaction's uncommitted write
Read committed	Prevents dirty reads and dirty writes
Read skew (nonrepeatable read)	Two reads in one transaction observe different points in time
Snapshot isolation	Each transaction reads a consistent snapshot as of its start
MVCC	Keeping several versions of each object so readers need no locks
Lost update	Two read-modify-write cycles where one overwrites the other
Compare-and-set	Write only if the current value matches what was read
Write skew	Two transactions read overlapping data and write different objects, breaking an invariant
Phantom	A write in one transaction changes the result of a search in another
Materializing conflicts	Creating placeholder rows so a precondition on absent rows can be locked
Stored procedure	Transaction code submitted up front to avoid interactive round trips
Two-phase locking (2PL)	Shared/exclusive locks acquired during execution and released at commit
Predicate lock	A lock on all objects matching a search condition, including future ones
Index-range (next-key) lock	A coarser, index-shaped approximation of a predicate lock
Serializable snapshot isolation (SSI)	Optimistic serializability: snapshot reads plus conflict detection at commit
SIREAD lock	A non-blocking record that a transaction read some data, used by SSI

Further reading

• DDIA, Chapter 7, "Transactions". Reference list: https://github.com/ept/ddia-references
• Berenson et al., "A Critique of ANSI SQL Isolation Levels", SIGMOD 1995: https://arxiv.org/abs/cs/0701157
• Cahill, Röhm and Fekete, "Serializable Isolation for Snapshot Databases", SIGMOD 2008
• Ports and Grittner, "Serializable Snapshot Isolation in PostgreSQL", VLDB 2012: https://arxiv.org/abs/1208.4179
• PostgreSQL documentation, "Transaction Isolation": https://www.postgresql.org/docs/current/transaction-iso.html
• Kleppmann, Hermitage: testing transaction isolation levels: https://github.com/ept/hermitage
• Bailis et al., "Highly Available Transactions: Virtues and Limitations", VLDB 2014: https://arxiv.org/abs/1302.0309
• Stonebraker et al., "The End of an Architectural Era (It's Time for a Complete Rewrite)", VLDB 2007
• Jepsen analyses of databases' isolation claims: https://jepsen.io/analyses