ACID transactions simulator

The database holds two accounts, A and B, starting at 100 each, so the total is always 200 unless something goes wrong. It has three tiers: the buffer pool in memory, where pages are read and changed; the write-ahead log, a list of change records that are first kept in memory and then flushed to disk; and the data pages on disk. A crash erases memory and leaves the disk as it was.

Choose a property. Each runs a short script, a transfer or a pair of deposits, sometimes with a crash in it. Then switch the safeguard off and run it again:

• Atomicity: with the log, recovery undoes the half-finished transfer; without it, 30 disappears.
• Consistency: with a CHECK constraint the overdraft is refused; without it, A goes to −30.
• Isolation: with row locks the second deposit waits and both count; without them one is lost.
• Durability: if the log is flushed at commit, the crash cannot undo the transfer; if commit does not wait for the disk, a "committed" transfer vanishes.

This is a teaching model in the style of ARIES, the recovery method many databases build on, much simplified: two rows, one page each, and a crash only where the script puts it. Real engines add checkpoints, compensation log records, group commit and much more, but the rules shown here are the ones they follow.

A transaction is a group of reads and writes that a database treats as one unit, such as "subtract 30 from account A and add 30 to account B". ACID names the four guarantees a database gives for transactions:

• Atomicity: all of the transaction's changes happen, or none do.
• Consistency: the transaction takes the database from one valid state to another; rules such as constraints are never broken.
• Isolation: transactions running at the same time do not interfere; each behaves as if it ran alone (to a degree set by the isolation level).
• Durability: once the database says a transaction committed, it stays committed, even after a crash or power cut.

Together they let application code be written as if nothing ever failed and nobody else used the database at the same time, which is the reason transactions exist.

Atomicity means a transaction cannot stop halfway. If anything goes wrong before it commits, whether an error, a constraint violation, a deadlock, a client disconnecting or a crash, every change it made is undone, and the database looks as if the transaction never started. The application can then simply retry.

The hard case is a crash. To work fast, databases let changed pages sit in memory and write them to disk later, and they may even write a page changed by a transaction that has not committed, if memory is needed for something else (the simulator's atomicity script does exactly that). So after a crash, the disk can hold half a transaction. The write-ahead log fixes this: every change is logged with its old and new value before the changed page may be written, so recovery can find the unfinished transaction and put the old values back. Despite the name, atomicity is not about concurrency; that is isolation.

Consistency means the data always obeys its rules. Some rules the database enforces itself: NOT NULL, UNIQUE, foreign keys, and CHECK constraints such as CHECK (balance >= 0). A statement that would break one fails, and the transaction can be rolled back as a whole. Other rules live only in the application, such as "the total of all accounts never changes in a transfer"; the database keeps them only if every transaction is written correctly.

For that reason, many authors, including Martin Kleppmann in Designing Data-Intensive Applications, point out that the C is different from the other three: it is a property the application achieves using atomicity, isolation and durability, plus whatever constraints it declares. Declaring constraints in the database, rather than only checking in code, is what turns a rule into a guarantee.

Isolation means concurrent transactions do not see or overwrite each other's unfinished work. The strongest form, serializability, guarantees the result is the same as running them one at a time. In the simulator's isolation script, two deposits read the same balance at the same time; without locking, the second one writes back a value computed from a stale read and the first deposit is lost.

Databases provide isolation with locks (a transaction waits for rows another transaction is changing) or with multi-version concurrency control (each transaction reads a consistent snapshot, and conflicting writes are blocked or rejected). Full serializability costs throughput, so databases offer weaker isolation levels, and most run at read committed by default, which does not prevent every anomaly. The isolation levels simulator shows each level against each anomaly, checked on PostgreSQL.

Durability means a committed transaction survives crashes. The database does not need to write the changed data pages to disk at commit, which would be slow, because they could be anywhere on the disk. It only needs to make sure the transaction's log records are on disk: they are appended in one place, so writing them is fast, and they are enough to redo the changes after a crash. The commit is reported to the client only after the operating system confirms, with fsync or similar, that the log has reached stable storage.

Durability against a single machine failing outright, a dead disk or a lost data centre, needs copies elsewhere: replicas, ideally with synchronous replication for data that must not be lost, and backups.

The simulator's four scripts, with each safeguard on and off:

Atomicity and durability both rest on one rule: log the change before writing it. Before a changed data page goes to disk, the log records describing that change must already be there; before a commit is acknowledged, the commit record must be there. After a crash, recovery reads the log and does two things:

1. Redo: replay logged changes, so committed work whose pages never reached disk is restored.
2. Undo: roll back changes by transactions that never committed, using the old values recorded in the log.

This lets a database use memory freely, writing pages whenever convenient (the "steal" and "no-force" policies), while still keeping every guarantee. PostgreSQL's WAL, MySQL InnoDB's redo and undo logs, SQL Server's transaction log and SQLite's WAL mode all work on this principle, as do the ARIES algorithms published by C. Mohan and colleagues at IBM in 1992.

Waiting for the disk on every commit is the main cost of durability, so databases let you trade it away. These settings are fine for some data and dangerous for other data:

When a transaction changes data on several servers, atomicity needs every server to commit or none. The classic protocol is two-phase commit: a coordinator asks every participant to prepare (make its part durable and promise to commit), and only if all agree tells them all to commit. Its weakness is that participants that have prepared must wait if the coordinator fails. Distributed SQL databases such as Spanner, CockroachDB and TiDB combine two-phase commit with consensus replication (Paxos or Raft), so that each participant and the coordinator's decision are themselves replicated and survive failures.

BASE, "basically available, soft state, eventually consistent", was coined in the late 1990s by Eric Brewer and colleagues at Berkeley to describe large distributed systems that relax ACID, above all immediate consistency across replicas, to stay available and scale out. Early NoSQL databases were often described this way, offering atomic operations on single records but no multi-record transactions.

The two are no longer separate camps. MongoDB has supported multi-document ACID transactions since version 4.0 (2018), DynamoDB offers transactions across items, and distributed SQL databases provide ACID with horizontal scaling. The useful question is not "ACID or BASE" but which guarantees each piece of data needs, at what cost.

Jim Gray described the transaction as a unit of atomic, consistent and durable work in "The Transaction Concept: Virtues and Limitations" (1981). Two years later, Theo Härder and Andreas Reuter added isolation and coined the acronym in "Principles of Transaction-Oriented Database Recovery" (1983). Gray received the Turing Award in 1998 for his work on transaction processing.