Two Tricks and a Shared Lie: How Modern Databases Actually Deliver ACID

When we talk about QPS, most of it is read traffic — in a typical system the read/write ratio can easily hit 100:1. For reads, modern distributed systems have plenty of answers: multi-layer caching, read-write splitting, hot-cold separation (OLTP vs. OLAP). But for writes — especially the cases that demand strong consistency — the relational database is still where everything converges. So let’s talk about how modern relational databases actually deliver ACID.

Every introduction to databases recites the same four letters: Atomicity, Consistency, Isolation, Durability. Transactions are all-or-nothing, they don’t step on each other, and committed data survives a crash. What those introductions rarely explain is how — and the how turns out to be more interesting than the what, because almost all of ACID falls out of just two mechanisms: the write-ahead log and multi-version concurrency control. Understand those two, and the four guarantees stop being marketing and start being engineering.

There’s a mapping error worth correcting up front. ACID names guarantees; WAL, MVCC, and locks are mechanisms; and the relationship between them is many-to-many, not one-to-one. The write-ahead log serves both durability and atomicity. MVCC and locking jointly serve isolation. And consistency, as we’ll see, is barely the database’s job at all.

flowchart TB
    U[Undo data<br/>old row versions] --> A[Atomicity<br/>all or nothing]
    W[Write-ahead log<br/>redo records] --> A
    W --> D[Durability<br/>survives crashes]
    M[MVCC + locks<br/>snapshots, 2PL] --> I[Isolation<br/>no interference]
    C[Constraints<br/>keys, checks] --> CO[Consistency<br/>valid states]

Trick one: the write-ahead log

Suppose a transaction updates rows scattered across fifty different 8KB pages. Making that durable the naive way means writing fifty pages to fifty random disk locations and waiting for all of them to be confirmed before acknowledging the commit. Random writes are the slowest thing storage does, and a crash midway through would leave the database in a state that is neither the old version nor the new one.

The write-ahead log solves both problems with one rule, which is also its name: write the log ahead of the data. Every change is first described in an append-only log record — “transaction 42 changed these bytes on page P from old value to new value” — and that record must reach disk before the modified page can, and before the commit is acknowledged. At commit time, the database flushes only the log, up to and including the commit record. One sequential write, one fsync. The actual data pages can be written back lazily, minutes later, whenever it’s convenient. The log converts a random-write problem into a sequential-append problem, and that conversion is arguably the single most important performance trick in database history. Group commit sweetens it further: when many transactions commit at nearly the same moment, their log records ride to disk on a single shared fsync.

Durability follows directly — the commit record is on stable storage before the client hears “committed.” But the log also delivers atomicity across crashes, because it gives recovery everything it needs to finish the story either way.

ARIES, or how recovery repeats history

The canonical recovery algorithm is ARIES (Algorithms for Recovery and Isolation Exploiting Semantics), published by C. Mohan and colleagues at IBM in 1992 and implemented, in letter or in spirit, by nearly every serious database since. After a crash, ARIES runs three passes over the log.

flowchart LR
    A["1. Analysis<br/>find dirty pages<br/>and loser transactions"] --> R["2. Redo<br/>repeat all history,<br/>even losers'"] --> UN["3. Undo<br/>roll back losers,<br/>logging CLRs"]

The analysis pass scans forward from the last checkpoint to reconstruct the state of the world at the moment of the crash: which pages might have had unsaved changes, and which transactions were still in flight — the “losers.” The redo pass then does something counterintuitive: it replays every logged change, including changes made by transactions that are about to be rolled back. This is ARIES’s “repeating history” principle — first restore the exact state at the instant of failure, then clean up. Only then does the undo pass walk backward through the losers’ records and reverse them.

The subtle brilliance is in the undo pass: each undo action is itself written to the log as a compensation log record (CLR). If the system crashes during recovery, the next recovery reads the CLRs, sees which undo work is already done, and doesn’t repeat it. Recovery is idempotent; crashes during crashes are fine.

This machinery is what makes the buffer manager’s life easy. ARIES permits a “steal, no-force” policy: dirty pages from uncommitted transactions may be written to disk early (steal), and committed changes need not be forced to disk at commit (no-force). Steal is why recovery needs an undo pass; no-force is why it needs a redo pass. The log pays for both freedoms.

One supporting actor deserves a mention: checkpoints. Without them, redo would replay the log since the database was created. A checkpoint periodically writes dirty pages out and records a position from which recovery can safely begin, trading a bit of steady-state I/O for bounded recovery time. Log for safety, checkpoints for a bounded restart — the design is complete only with both.

Durability’s fine print

Durability rests on fsync, and fsync rests on hardware and kernels that don’t always tell the truth. Consumer SSDs have been known to acknowledge flushes from volatile cache that a power cut would erase. Worse, in 2018 the PostgreSQL community discovered — in an episode now known as “fsyncgate” — that it had misunderstood Linux fsync semantics for roughly twenty years: if an fsync fails, the kernel may drop the dirty pages, so a retry can succeed while the data is silently gone. Postgres now deliberately crashes on fsync failure rather than retry, on the theory that a panic and clean recovery is safer than believing a lie.

The practical upshot is that modern durability is increasingly defined across machines rather than within one. Synchronous replication — the commit isn’t acknowledged until another node has the log record too — protects against the failure modes a single disk cannot. The “D” in ACID started as a promise about one disk and has quietly become a promise about a quorum.

Rollback: two philosophies

Crash recovery is one form of atomicity; ordinary ROLLBACK is the other, and here the major engines split into two coherent, opposite designs.

InnoDB (MySQL) and Oracle update rows in place and keep the previous images in undo logs, stored in rollback segments. Rollback physically restores the old images; a background purge discards undo data once no one could need it.

PostgreSQL never updates in place. An UPDATE writes a brand-new row version and leaves the old one where it was, so at rollback time there is almost nothing to do: the transaction is simply marked aborted in the commit log, and every version it created becomes invisible to everyone. Abort is nearly free — the bill arrives later, when VACUUM has to sweep through tables reclaiming the dead versions. Same guarantee, opposite choice about who pays and when: InnoDB pays at rollback, Postgres pays at vacuum.

Trick two: MVCC — and the locks that never left

The old answer to isolation was pure two-phase locking: readers take shared locks, writers take exclusive locks, and everybody waits for everybody. Multi-version concurrency control changed the game by keeping several versions of each row, so that a reader doesn’t need to lock anything — it works against a snapshot, a frozen view defined by which transactions had committed at the moment the snapshot was taken, and simply ignores versions created afterward. Readers never block writers; writers never block readers.

But it’s a common misreading to think MVCC eliminated locks. It demoted them. Write-write conflicts still serialize through row locks: two transactions updating the same row still queue up. InnoDB goes further at its default isolation level, taking gap locks and next-key locks — locks on the empty ranges between index entries — to stop other transactions from inserting phantom rows into a range it has touched. The honest description of a modern engine is: MVCC for reads, locking for writes.

Isolation levels are presets, not mechanisms

Here is the connection most explanations miss entirely: isolation levels — read committed, repeatable read, serializable — are not additional machinery. They are settings on two knobs the engine already has. Knob one: how often a transaction takes a fresh snapshot. Knob two: how aggressively it locks or detects conflicts. Every named level is a preset of those knobs, trading performance against which anomalies you’re willing to tolerate. Atomicity and durability are untouched by any of this; every level writes the same WAL and fsyncs the same commits. Isolation levels tune only the I.

Read committed versus repeatable read is purely a snapshot-timing difference. Same version chains, same visibility rules — read committed just refreshes its view of the committed world before each statement, while repeatable read freezes it at the first query. The “non-repeatable read” anomaly is nothing more mysterious than statement two using a newer snapshot than statement one.

InnoDB’s repeatable read shows the second knob turning. Plain SELECTs read from the snapshot, but the moment a transaction issues an UPDATE, DELETE, or SELECT ... FOR UPDATE, InnoDB performs a “current read”: it operates on the latest committed version rather than the snapshot, taking next-key locks along the way. Snapshot reads and current reads coexist in the same transaction, which produces a classic confusion — the rows you just read peacefully from your snapshot are not necessarily the rows your update just modified.

The anomaly the standard forgot

The SQL standard defines isolation levels by which of three anomalies they forbid: dirty reads, non-repeatable reads, and phantoms. The trouble, laid out in Berenson et al.’s 1995 paper “A Critique of ANSI SQL Isolation Levels,” is that this list is incomplete — and the omission matters, because snapshot isolation, the thing nearly everyone actually implements, passes all three tests while still permitting a serious anomaly the standard never named: write skew.

The canonical example: a hospital requires at least one doctor on call. Alice and Bob are both on call. In concurrent transactions, each checks their snapshot — “is at least one other doctor on call?” — each sees yes, and each removes themselves. Neither transaction touched a row the other wrote, so no lock conflict fired, no anomaly from the standard’s list occurred, and yet the invariant is broken. The transactions were individually correct and jointly wrong.

This is why “repeatable read” in PostgreSQL is simultaneously stronger than the standard requires (it’s real snapshot isolation, so plain queries never see phantoms) and still not serializable. Snapshot isolation doesn’t sit cleanly on the standard’s ladder at all.

Two roads to serializable

If you actually request serializable, the two engines reveal opposite philosophies.

InnoDB adds locks: plain reads become shared-locking reads, which is essentially a return to two-phase locking, with the blocking and deadlock risk that implies. Pessimistic — prevent conflicts before they happen.

PostgreSQL adds detection. Its implementation of Serializable Snapshot Isolation (SSI), from Cahill, Röhm, and Fekete’s 2008 work, rests on a theoretical result: every serialization anomaly under snapshot isolation contains a specific pattern — two consecutive read-write antidependencies, where a transaction reads a version that a concurrent transaction overwrites. So Postgres runs everything optimistically on snapshots, tracks these rw-conflicts with non-blocking predicate locks, and when a transaction accumulates both an incoming and an outgoing rw-conflict — the dangerous structure — it aborts one participant with a serialization failure and lets the application retry. Nothing ever blocks on a read. The trade-off is honesty about false positives: the dangerous structure is necessary but not sufficient for a real anomaly, so SSI sometimes aborts transactions that would have been harmless. Cheap detection, occasionally overzealous.

And yet almost nobody runs serializable in production. The defaults — read committed in Postgres, repeatable read in MySQL — are where the world actually lives, which means most applications have implicitly accepted a set of anomalies their authors may never have named. That is neither scandal nor negligence; it’s a reasonable trade of correctness guarantees for concurrency. But it’s a trade, and trades go better when you know you’re making them.

Consistency: the letter the database can’t give you

Three of the four letters name things the engine does. The fourth is different in kind. Atomicity, isolation, and durability are mechanical guarantees; consistency — “transactions move the database from one valid state to another” — is a contract, and the application signs half of it. The database enforces what you declare: primary keys, unique constraints, foreign keys, check constraints. But “valid” in any richer sense (accounts balance, at least one doctor is on call) is an application invariant the engine has never heard of. What the engine actually promises is conditional: if each of your transactions individually preserves your invariants, then atomicity and isolation ensure the interleaved whole does too. Joe Hellerstein has quipped that the C was mostly there to make the acronym pronounceable. Harsh, but clarifying — and the write-skew example shows the condition has teeth, since at snapshot isolation, “isolation” itself isn’t quite strong enough to keep every individually-correct transaction jointly correct.

One more source of confusion deserves defusing while we’re here, because the same letter appears in a completely unrelated context. In distributed systems — MySQL Group Replication acknowledging a commit once a majority of nodes has it, Raft and Paxos electing leaders by quorum — people also talk about “consistency.” That C means replica agreement: all nodes converge on the same ordered history, and reads don’t return stale data. It has nothing to do with ACID’s C of invariant preservation; the majority-quorum machinery is really an extension of durability (your commit survives any minority of node failures) and, at higher guarantee levels, of read freshness. Similarly, two-phase commit — the protocol for making one transaction span multiple databases — extends atomicity across machines, not consistency. Two different Cs, one unfortunate coat.

What to keep

The compressed version: the write-ahead log turns random writes into a sequential append, and that one trick buys durability (commit = log flushed) and atomicity (ARIES redoes history and undoes losers, idempotently, thanks to compensation records). MVCC keeps old row versions around so reads never block, which buys most of isolation — with row locks still arbitrating writes underneath. Isolation levels are just presets on snapshot timing and lock aggressiveness, and the level everyone actually runs permits write skew, an anomaly the SQL standard forgot to name. Serializable exists, via locking (InnoDB) or conflict detection (Postgres SSI), for the transactions that truly need it. And consistency, the C that made the acronym work, is the one guarantee you and the database deliver together.

ACID isn’t magic, and it isn’t free. It’s a log, some versions, a few locks, and a set of trades — made honestly once you can see them.