ACID Properties

ACID is the set of four guarantees a transaction — a bundle of operations treated as one logical unit — upholds so that concurrency and crashes can never leave data half-finished or corrupt: Atomicity, Consistency, Isolation, Durability. The framing most people miss is that the four letters are not peers: A, I, and D are machinery the database provides, while C is mostly the application's job.

Intermediate transactions acid atomicity consistency isolation durability wal mvcc savepoint interview-priority Created 2026-06-10 · Updated 2026-06-15

Key Components

Transaction: A bundle of operations treated as one logical unit, bracketed by BEGIN and COMMIT (or ROLLBACK). It is the unit over which all four ACID guarantees are defined — not the individual statement.
Atomicity: All-or-nothing: if any statement in a transaction fails to complete, the whole transaction fails and the database is left unchanged. From other transactions' point of view it either happens completely or not at all.
Write-Ahead Log (WAL): A durable append-only log that records intended changes before they touch the data pages. One log powers two directions: undo (reverse uncommitted work) for Atomicity, and redo (replay committed work) for Durability.
Consistency: A transaction moves the database from one valid state to another, preserving every declared invariant — CHECK, NOT NULL, FOREIGN KEY, UNIQUE, triggers. The database only enforces the rules it was given; everything else is the application's responsibility.
Isolation: Concurrent transactions leave the database in a state equivalent to having run sequentially (the gold standard, serializability). In practice it is a tunable dial — a spectrum of levels that trade anomaly prevention for speed.
Durability: Once a transaction is committed it stays committed, surviving system failure. The durability line is the COMMIT acknowledgment: before it, no promise; after it, ironclad.

Concrete Example

The canonical transaction is a bank transfer — two writes that must behave as one. Either both happen or neither does; a balance can never be debited without the matching credit:

BEGIN;
UPDATE accounts SET balance = balance - 100 WHERE name = 'Alice';
UPDATE accounts SET balance = balance + 100 WHERE name = 'Bob';
COMMIT;

Wrap these two statements in BEGIN…COMMIT and you are buying Atomicity: if the process dies after the debit but before the credit, the engine uses the WAL to undo the uncommitted debit on restart, and no money vanishes. Every ORM transaction block — db.transaction(...), @Transactional — is purchasing exactly this guarantee.

Savepoints add partial rollback: a nested checkpoint inside a transaction, so you can undo part of it without abandoning the whole:

BEGIN;
UPDATE accounts SET balance = balance - 100 WHERE name = 'Alice';
SAVEPOINT sp;
UPDATE accounts SET balance = balance + 100 WHERE name = 'Bob';
ROLLBACK TO sp;     -- undo only Bob's line; Alice's debit still pending
UPDATE accounts SET balance = balance + 100 WHERE name = 'Wally';
COMMIT;

After ROLLBACK TO sp, Bob's credit is discarded but Alice's debit survives inside the still-open transaction; the money instead lands with Wally at COMMIT. The transaction is the boundary; the savepoint is a finer-grained checkpoint within it.

The subtle trap lives in Consistency. Suppose a bug debits Alice but never credits Bob:

BEGIN;
UPDATE accounts SET balance = balance - 100 WHERE name = 'Alice';
-- bug: the credit line is missing
COMMIT;

This commits with no error, and the database is still perfectly "consistent." Money vanished, but no declared constraint was broken — "money is conserved" was an invariant that was never expressed as a CHECK or trigger, so the database had nothing to enforce. The database did not fail at C; C was simply never handed to it.

Visual Model

Picture a transaction as a wedding ceremony. Atomicity is "I now pronounce you married" — all-or-nothing, nobody ends up half-married. Consistency is the officiant checking the legal rules on the books (consent, age, valid license) — he refuses an illegal marriage but won't stop one you'll regret, because he only enforces the rules he was handed. Isolation is two weddings in adjacent halls that never bleed into each other. Durability is the signed certificate filed at the registry: official forever, surviving even if the venue burns down that night — and the filing is the commit.

The engine that delivers both Atomicity and Durability is a single Write-Ahead Log — one log, two directions. Step through a transaction's lifecycle below and watch where the durability line falls (the COMMIT fsync) and how the same log is replayed forward (redo) or backward (undo) after a crash.

Step 1 of N

Loading…

Deeper — Edge Cases & Gotchas

Why "C" is the odd one out

A, I, and D are mechanisms the database implements. Consistency is different: the database only checks the homework it was handed. It rejects and rolls back any transaction that violates a declared rule — a CHECK, NOT NULL, FOREIGN KEY, UNIQUE, or trigger — but it has no notion of business invariants it was never told about. This is why researchers (Hellerstein and others) argue C "doesn't really belong" in ACID: A, I, D are guarantees the engine provides unconditionally, whereas C is the engine validating the constraints the application declared. Consistency is only ever as strong as the invariants you write down.

One log, two directions — the WAL insight

The same Write-Ahead Log delivers both Atomicity and Durability, which is why it is the single most important mechanism to understand. Because intentions are logged before the data pages change, the engine always knows what to reverse and what to replay:

ATOMICITY  →  undo direction  →  reverse UNCOMMITTED changes on rollback/crash
DURABILITY →  redo direction  →  replay COMMITTED changes not yet in the heap

The redo target is precise: it is the gap between the WAL and the heap — committed changes that were fsync'd to the log but had not yet been lazily flushed to the data files — not "the whole database." That gap exists because durability means survives a crash, not written into the table. Right after commit a row may live only in the WAL on disk and not yet in the heap, and that is entirely fine: the log is durable. What is "not yet on disk" is the heap copy — never the log.

Isolation is a dial, not a switch

Equating isolation with "no dirty reads" is wrong; that is just the lowest bar. Isolation means behaving as if transactions ran serially (serializability), and it is offered as a spectrum of levels (Read Uncommitted → Serializable) where weaker levels deliberately allow some anomalies in exchange for speed. Two implementation families exist: pessimistic locking / two-phase locking (lock touched data, others wait), and optimistic MVCC (multiple row versions, each transaction sees the version valid at its start, so readers don't block writers). Postgres uses MVCC, and it diverges from the ANSI standard — request "Read Uncommitted" and you get Read Committed, because MVCC makes dirty reads impossible, and its Serializable uses Serializable Snapshot Isolation (SSI), not locking. That same MVCC is why an index-only scan sometimes still touches the heap: version-visibility metadata (xmin/xmax) lives on the heap tuple, not the index entry.

Anti-pattern: doing two related writes without wrapping them in a transaction, on the assumption that "the second one always runs."

-- NO transaction: two independent statements
UPDATE accounts SET balance = balance - 100 WHERE name = 'Alice';
-- ← request crashes / process dies HERE
UPDATE accounts SET balance = balance + 100 WHERE name = 'Bob';

Why it breaks: without BEGIN…COMMIT each statement commits on its own, so there is no Atomicity across the pair. If the request dies between them, Alice's debit is already durable and Bob's credit never happens — money is destroyed and an orphaned, half-finished state is left behind. The fix is to make the two writes one transaction so the WAL can undo the debit if the credit never commits.

Durability has limits

Durability is only as strong as storage honoring fsync. Setting fsync=off, or a lying disk controller that acknowledges writes still sitting in volatile cache, silently breaks the guarantee — the engine believes the WAL is on disk when it is not, and a crash loses "committed" work. And single-node durability is not the same as surviving disk destruction: that requires replication to another machine.

Test Yourself

A transfer debits Alice $100 but a bug skips crediting Bob; it commits with no error. Which ACID property is at stake, and why is the answer surprising?

In Postgres, "Read Uncommitted" behaves like Read Committed and dirty reads are impossible. Why?

Explain how the single WAL mechanism delivers both Atomicity and Durability — name the direction (undo/redo) for each.

Correct this claim: "Postgres durability means that after COMMIT my row is safely written into the table's data file."

In Postgres, where does Isolation come from, and why does that same mechanism explain why an index-only scan sometimes still touches the heap?