ACID Properties

What atomicity, consistency, isolation and durability each promise, which part of the database delivers each one, and where the promises stop.

Overview

Why transactions exist

A transaction groups several reads and writes into one unit. The database then either applies all of them or none of them, and keeps other clients from seeing the work half done. Without transactions, every application would have to handle crashes, half-finished writes and concurrent clients on its own.

ACID names four promises a transaction makes. The acronym comes from Theo Härder and Andreas Reuter's 1983 paper on database recovery. Each letter solves a different problem, and a different part of the database delivers it.

A crash or error halfway through — Atomicity — Rollback of the unfinished transaction
Data that breaks a business rule — Consistency — Constraints and application code
Two clients working on the same data — Isolation — Concurrency control (locks, MVCC)
Power loss right after a commit — Durability — Write-ahead log flushed to disk, replicas

Atomicity: all or nothing

Atomicity means a transaction that cannot finish leaves no trace. If a statement fails, the client disconnects or the server crashes before commit, every write in the transaction is discarded. The client can then retry safely, because nothing was half applied.

The word atomic here is about failures, not concurrency. It does not say anything about what other transactions can see while this one runs; that is isolation's job.

A rollback in SQL shows the same thing on a small scale. First, two accounts.

1 — 500
2 — 100

Consistency: the rules stay true

Consistency means the data satisfies its rules before and after every transaction. Some rules the database can check itself. Most business rules it cannot see, so the application must write transactions that keep them. The database only helps: atomicity and isolation make sure a correct transaction is not spoiled by a crash or a concurrent client.

A column is never empty — Database: NOT NULL
No two users share an email — Database: UNIQUE
Every order points to a real customer — Database: FOREIGN KEY
A balance never goes below zero — Database: CHECK (if you declare it)
Money is neither created nor lost in a transfer — Application: debit and credit in one transaction
Linked checking and savings never go below zero together — Application, plus the right isolation level

The last row is the classic trap. Each transaction can be correct on its own and the rule can still break when two run at the same time. That is a problem of isolation (see Write Skew and Phantom Reads).

Isolation: transactions do not step on each other

Isolation means concurrent transactions do not see each other's unfinished work. The strongest form, serializable, promises the result is the same as running the transactions one at a time. Most databases default to something weaker, because full isolation costs throughput.

PostgreSQL — Read committed
MySQL (InnoDB) — Repeatable read

So a product that says it supports ACID may still allow anomalies such as lost updates or write skew at its default level. The levels and what each one allows are in Isolation Levels.

Durability: a commit survives a crash

Durability means that once the database says a transaction committed, the change is not lost, even if the server crashes a moment later. The usual tool is a write-ahead log (WAL). The database appends a record of each change to the log and forces the log to disk with fsync before it reports success. Data files are updated later. After a crash, the database replays the log to redo any change that had not reached the data files.

Durability is only as strong as its settings. In PostgreSQL the trade-offs look like this.

fsync = on (default) — Recovery to a consistent state after an OS or power crash — Nothing; this is the safe choice
fsync = off — Faster writes — Unrecoverable corruption after a crash; only for data you can rebuild
synchronous_commit = off — Commits return before the WAL flush — The last few commits can vanish after a crash, but the data stays consistent
synchronous_commit = on with a synchronous standby — Each commit is flushed on the primary and on the standby — Commit latency now includes a network round trip

No setting gives perfect durability. If every copy is lost at once, or a disk reports a write as done when it is not, committed data can still disappear. Durability lowers the odds; replicas in other places lower them further.

Undoing a transaction after a crash

Engines differ in how they throw away an unfinished transaction. Many keep undo records (the old values) and apply them during recovery. PostgreSQL works differently: its crash recovery only redoes changes from the WAL. Rows written by a transaction that never committed stay on disk, but every row version records the transaction that created it (xmin), and readers skip versions whose transaction did not commit. VACUUM later reclaims the space.

Undo log (ARIES-style recovery) — Recovery writes the old values back
Multi-version rows (PostgreSQL) — The new version stays, but is invisible because its transaction never committed

What you will be able to do

Say what each ACID letter promises and which mechanism delivers it.
Explain why consistency is mostly the application's job.
Walk through the commit path of a write-ahead log and what fsync adds.
Question a claim of ACID compliance by asking which isolation level is the default.
Name the durability settings that trade safety for speed.

Key concepts

Transaction: A group of reads and writes that the database treats as one unit: it commits as a whole or aborts as a whole.
Atomicity: If a transaction cannot finish, all its writes are discarded, so a failed transaction can be retried safely.
Consistency: The data meets its rules before and after each transaction. The database checks declared constraints; the application owns the rest.
Isolation: Concurrent transactions do not see each other's unfinished work. How strong it is depends on the isolation level.
Durability: Once a commit is acknowledged, the change survives a crash of the database process or the machine.
Write-ahead log (WAL): An append-only log of changes. A change is logged and flushed before the data files are touched, so recovery can redo it.
fsync: A system call that forces buffered file data to the storage device. Without it, a log record may sit only in memory.
Synchronous replication: The primary waits for a standby to confirm it stored the commit before telling the client, so one lost machine does not lose the commit.

When to use it

Explaining why a money transfer must run as one transaction.
Separating the rules a database can enforce from the rules only the application knows.
Checking what an "ACID" product really promises: its default isolation level and durability settings.
Discussing commit latency, fsync and synchronous replication in a design review.

Interview questions

What does durability mean in practice, and how is it implemented?
How is atomicity different from isolation?
Is consistency guaranteed by the database?
A vendor says their database is ACID compliant. What do you ask?
How does a database throw away an unfinished transaction after a crash?