ACID in Plain English

The ATM transfer

You move $500 from checking to savings at an ATM. The machine debits your checking account. Then the power dies. Without ACID, your $500 has vanished into a half-applied transaction — debited from one account, never credited to the other. ACID is the four-letter promise that this cannot happen.

Atomicity — all or nothing

A transaction is one indivisible unit. If any step fails, every step rolls back as if it never started. The ATM's job is to wrap "debit checking" and "credit savings" inside BEGIN ... COMMIT. PostgreSQL guarantees the rest.

Consistency — rules always hold

Constraints (NOT NULL, CHECK, foreign keys, unique indexes) are enforced at every commit. No transaction can leave the database in a state that violates a defined rule. If your "balance must be ≥ 0" CHECK exists, no transaction can sneak past it.

Isolation — concurrent transactions don't interfere

Two transactions running at once behave as if they ran one after the other. PostgreSQL achieves this with MVCC (multi-version concurrency control) — readers don't block writers, writers don't block readers, and each transaction sees a consistent snapshot.

Durability — committed = saved forever

Once COMMIT returns, the data is on disk. PostgreSQL's write-ahead log (WAL) is flushed before the COMMIT acknowledges. If the server crashes a millisecond later, recovery replays the WAL and your committed data is intact.

Code

Atomic transfer·sql

BEGIN;
UPDATE accounts SET balance = balance - 500 WHERE id = 1;
UPDATE accounts SET balance = balance + 500 WHERE id = 2;
COMMIT;
-- Either both rows change, or neither does. There is no partial state.

Constraint protects consistency·sql

ALTER TABLE accounts
ADD CONSTRAINT positive_balance CHECK (balance >= 0);

-- Now this transfer is rejected at COMMIT if it would overdraw:
BEGIN;
UPDATE accounts SET balance = balance - 1000 WHERE id = 1;  -- balance = -500
UPDATE accounts SET balance = balance + 1000 WHERE id = 2;
COMMIT;
-- ERROR: new row violates check constraint "positive_balance"

Isolation makes concurrent reads sane·sql

-- Session A                          | -- Session B
BEGIN;                                | BEGIN;
SELECT SUM(balance) FROM accounts;    | UPDATE accounts SET balance = balance + 100
                                      |   WHERE id = 7;
                                      | COMMIT;
SELECT SUM(balance) FROM accounts;    |
-- Session A sees the same total both times, even though B committed
-- in between. That's MVCC giving each transaction a consistent snapshot.
COMMIT;

The ATM transfer

Atomicity — all or nothing

Consistency — rules always hold

Isolation — concurrent transactions don't interfere

Durability — committed = saved forever

Code

External links

Exercise

Progress

Comments 0