Transactions, ACID and isolation levels

What a transaction promises

A transaction groups several reads and writes into one unit that either takes effect completely (commits) or not at all (aborts and rolls back). Chapter 0 introduced the idea with a bank transfer. This section is about the part most developers misunderstand: what happens when many transactions run at the same time, which guarantees databases really give, and how to choose. The examples use SQL, but the reasoning applies to any store that offers transactions.

Transactions matter architecturally because they decide where business rules can be enforced cheaply. Inside one database, a transaction can keep a ledger balanced or stop a table being double-booked with a few lines of code. Across services there is no such tool, which is why data ownership and sagas are so important. Knowing exactly what a single database guarantees tells you which rules belong inside one owner's boundary.

ACID in detail

ACID was coined by Theo Härder and Andreas Reuter in 1983, building on Jim Gray's earlier work. Each letter is narrower than it sounds. Atomicity is about failure, not concurrency: if anything goes wrong part-way (a crash, a constraint violation, a deadlock, the application deciding to abort), every change made so far is undone, so the application can safely retry. Abortability would be a more accurate name.

Consistency means that a transaction takes the database from one valid state to another, where valid means your invariants hold: debits equal credits, stock is never negative, every order has a customer. The database can enforce some invariants itself (keys, foreign keys, CHECK constraints, unique indexes), but most are business rules that only the application's transactions keep. Consistency is therefore a property of the application's use of atomicity and isolation, not something the database provides on its own. It is also unrelated to the C in CAP, which means linearisability (Chapter 6).

Isolation means that concurrently running transactions do not interfere with each other. In its strictest form, serializability, the result of running transactions concurrently is the same as if they had run one at a time in some order. Weaker levels allow some interference in exchange for performance, and the rest of this section is about exactly which interference. Durability means that once a commit is acknowledged, the data survives crashes. Single-node databases achieve it with a write-ahead log flushed to disk before the commit returns; replicated databases add copies on other machines. Durability is never absolute: a disk can lie about a flush, and asynchronous replication can lose the last few acknowledged writes when the leader fails (see replication).

What goes wrong without isolation

Concurrency bugs are hard to find because they depend on timing: they pass every test on a quiet developer machine and appear at the dinner-time peak. The clearest way to understand isolation levels is through the specific anomalies (also called phenomena) they allow or prevent. The lost update below is the most common in business code.

The SQL standard isolation levels

The SQL standard (from SQL-92 onwards) defines four isolation levels by which of three phenomena they forbid: dirty reads, non-repeatable reads and phantoms. The standard makes serializable the default, but almost no mainstream product follows that; most default to read committed or repeatable read for performance.

In 1995, Hal Berenson, Phil Bernstein, Jim Gray, Jim Melton, Elizabeth O'Neil and Patrick O'Neil published A Critique of ANSI SQL Isolation Levels. They showed that the standard's English definitions were ambiguous, that forbidding the three phenomena does not by itself give serializability, and that the standard's levels failed to describe isolation levels that real products used. They added phenomena the standard missed, notably the dirty write, the lost update, read skew and write skew, and defined snapshot isolation: each transaction reads from a consistent snapshot of the committed data as of its start, and a transaction cannot commit a write to a row that a concurrent transaction has already changed and committed (first committer wins). Snapshot isolation prevents dirty reads, non-repeatable reads, read skew and lost updates, and the phantoms that affect reads, but it allows write skew, so it is not serializable. It fits awkwardly between the standard's levels, which is why products label it differently.

MVCC: how snapshots work

Most modern databases implement snapshot reads with multi-version concurrency control (MVCC). Instead of overwriting a row in place, a write creates a new version tagged with the writing transaction's identifier, and the old version is kept until no running transaction can still need it. Each reader is given a snapshot, essentially a list of which transactions had committed when it started, and sees only the versions those transactions created. The great benefit is that readers never wait for writers and writers never wait for readers; only two writers to the same row conflict.

The cost is housekeeping. Old versions must be cleaned up: PostgreSQL keeps them in the table and removes them with vacuum, while Oracle and MySQL InnoDB keep before-images in undo logs. A transaction left open for hours pins old versions, so tables and undo logs bloat and performance degrades. Long-running transactions are therefore an operational smell in any MVCC database, and a reason to keep analytical queries off the operational database. In read committed under MVCC, each statement gets a fresh snapshot; in snapshot isolation, the whole transaction uses one.

Real defaults, and why the names mislead

Isolation level names are not portable. The same word can mean different guarantees in different products, and the defaults differ. Martin Kleppmann's Hermitage project tested the actual behaviour of many databases against the anomalies above; vendor documentation confirms the defaults. Treat this table as a starting point and check the documentation for the version you run.

Achieving serializability

Databases that offer true serializability use one of three techniques. Actual serial execution runs transactions one at a time on a single thread, which is fast if every transaction is short and the data is in memory (VoltDB, and Redis scripts and transactions, work this way). Two-phase locking (2PL), used by SQL Server's and MySQL's serializable levels, makes readers take shared locks and writers exclusive locks, including locks on ranges to stop phantoms, and holds them to the end of the transaction; it is correct but readers and writers block each other, throughput drops under contention and deadlocks are common. Serializable snapshot isolation (SSI), described by Michael Cahill, Uwe Röhm and Alan Fekete in 2008 and used in PostgreSQL since version 9.1 (CockroachDB uses a related optimistic technique), runs transactions on snapshots without blocking, tracks read-write dependencies between them, and aborts one transaction when a dangerous pattern appears.

Whichever is used, serializable transactions can fail with a serialization error (SQLSTATE 40001 in PostgreSQL and CockroachDB) or a deadlock, and the application must retry the whole transaction. This is the hidden cost of the strongest level: every transactional code path needs a retry loop that is safe to repeat, which means no side effects such as sending emails or calling payment providers inside the transaction.

Pessimistic and optimistic concurrency control

In practice most applications run at read committed or repeatable read and protect specific read-modify-write paths explicitly. There are three tools. The best, when possible, is an atomic update that lets the database do the arithmetic and the check in one statement, such as UPDATE accounts SET balance = balance - 30 WHERE id = 7 AND balance >= 30, then checking that exactly one row changed. Under PostgreSQL's read committed, a concurrent update makes the second statement wait and then re-evaluate its condition against the new row, so no update is lost.

When the decision needs logic the database cannot express, use pessimistic locking: lock the rows you read with SELECT ... FOR UPDATE, so concurrent transactions that want them wait until you commit. It is simple and suits short, highly contended operations (assigning one courier to one order). The costs are waiting, the risk of deadlocks (lock rows in a consistent order, such as by ascending id) and reduced throughput. Variants include NOWAIT, which fails immediately instead of waiting, and SKIP LOCKED, which skips rows already locked and is the standard way to build a reliable job queue on a relational table.

BEGIN;
-- Lock the row: other FOR UPDATE readers and writers must wait
SELECT balance FROM accounts WHERE id = 7 FOR UPDATE;
-- application checks: balance (100) >= 30, so allow it
UPDATE accounts SET balance = 70 WHERE id = 7;
INSERT INTO ledger (account_id, amount, kind)
  VALUES (7, -30, 'withdrawal');
COMMIT;  -- lock released; the waiting transaction now sees 70

Optimistic concurrency control takes no locks while the user or the code is thinking. Each row carries a version number (or a timestamp or hash). The writer updates only if the version is still the one it read, and increments it. If no row changes, someone else got there first, and the application reloads and retries or tells the user. This suits low-contention data and long think time: a restaurant manager editing a menu in a browser for five minutes must not hold a database lock all that time. HTTP has the same idea built in, with ETags and the If-Match header.

-- Read (no lock); remember version = 12
SELECT id, name, price, version FROM menu_items WHERE id = 501;

-- Later, write only if the version is unchanged
UPDATE menu_items
   SET price = 11.00, version = version + 1
 WHERE id = 501 AND version = 12;

-- Rows affected = 1: success.
-- Rows affected = 0: a concurrent edit won; reload and retry
-- or show the user the other change.

Choosing an isolation strategy

There is no single right level; there is a right level for each piece of logic. A practical approach starts from the invariants, not from the settings.

As a rule of thumb, read committed with explicit locking or version checks is a sound, fast default for most business applications, provided the team is disciplined about finding read-modify-write paths. Serializable is the safer default when the rules are complex, the team is large or the cost of a violated invariant is high (money, medicine, safety), and many teams now use it for the critical paths, accepting retries. Practitioners genuinely disagree here: some argue that serializable by default prevents a whole class of bugs that code review will never catch; others argue that its retries and contention cost too much and that explicit locking makes intent visible. Both positions are defensible if the choice is deliberate and tested.