ACID Properties¶

Each property is enforced by a specific mechanism elsewhere in this handbook — ACID isn't four independent rules, it's a name for what four separate storage-engine mechanisms add up to guaranteeing together:

Atomicity: A transaction is all-or-nothing. If the transaction commits, all changes are durable. If it aborts (explicitly or due to error), all changes are undone as if the transaction never ran. Implemented via undo logs: before writing a new value, the old value is recorded in the undo log. If the transaction aborts, the undo log is replayed in reverse to restore previous values.

Why: Without atomicity, a partial failure (crash mid-transfer) leaves the database in a state where money has been debited but not credited. Impossible to recover without knowing which operations completed.

Consistency: The database transitions from one valid state to another. Consistency is the database's responsibility (NOT NULL, UNIQUE, FOREIGN KEY, CHECK constraints) and the application's responsibility (business invariants that constraints cannot express). The database enforces structural validity; the application enforces semantic validity.

Isolation: Concurrent transactions do not interfere with each other. Without isolation, transactions can read uncommitted data, re-read changed values, or see new rows inserted mid-transaction. Isolation is the hardest ACID property to achieve at scale — stronger isolation requires more locking or more version tracking, reducing throughput.

Durability: Once a transaction commits, its changes survive crashes. Implemented via WAL: every modification is written to the WAL on disk (fsync'd before acknowledging commit) before being applied to heap pages. On crash recovery, WAL is replayed from the last checkpoint.

Common pitfall¶

Treating "Consistency" as something the database guarantees on its own is the most common misreading of the acronym — the C only covers invariants the schema can actually express (CHECK (balance >= 0), a foreign key). "This order's total matches the sum of its line items" is a business invariant no CHECK constraint can see across tables; that half of consistency is the application's responsibility, not something turning on ACID mode buys automatically. The other three letters (A, I, D) are genuinely enforced by the engine with no application code required — only C is split between the two.