Recipe

ACID Properties

A practical guide to Atomicity, Consistency, Isolation, and Durability in database transactions.

Atomicity

A transaction is all-or-nothing. If any statement fails, the entire unit rolls back. Think bank transfers — debit and credit must both succeed, or neither does.

BEGIN;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;
COMMIT;

Consistency

A transaction brings the database from one valid state to another, preserving all constraints — foreign keys, uniqueness, and CHECK clauses. The schema's rules are never violated.

Isolation

Concurrent transactions must not interfere. Isolation levels — Read Uncommitted, Read Committed, Repeatable Read, Serializable — trade performance for correctness. Serializable is the gold standard but carries the highest cost.

Dirty reads

Non-repeatable reads

Phantom reads

Serialization anomalies

Durability

Once committed, a transaction survives crashes. Write-ahead logs (WAL) flush to disk before acknowledging success. Power loss, kernel panic — the data persists.

Key takeaway

ACID is not a binary checkbox. Each property exists on a spectrum. Choose your isolation level and durability guarantees based on the application's tolerance for anomalies versus its latency budget.