ACID and How Each Property Is Implemented

In one line

Atomicity: all of a transaction's changes commit or none do (undo via MVCC or rollback logs). Consistency: constraints hold at commit (the database's part) and invariants hold (the application's part). Isolation: concurrent transactions don't see each other's partial work (MVCC and locks). Durability: committed data survives crashes (WAL flushed before COMMIT returns).

0/5 · 0%

Think of it like this

A bank transfer. Money leaves one account and arrives in another (atomic), balances never go negative (consistent), two tellers working at once don't mix up each other's half-finished transfers (isolated), and a power cut after the receipt is printed doesn't undo it (durable).

Key ideas

01
Atomicity in PostgreSQL: new row versions carry the transaction ID; if the transaction aborts, its versions are simply invisible (marked aborted in the commit log pg_xact). No undo is needed; VACUUM cleans them later. InnoDB instead writes undo logs and rolls back changes.
02
Consistency: the database guarantees constraints (PK, FK, CHECK, UNIQUE, exclusion) at statement or commit time. Invariants the schema can't express ("total debits = total credits") are the application's responsibility inside the transaction.
03
Isolation: implemented by snapshots (MVCC, Topic 5.5) for reads and row locks for conflicting writes. The isolation level (Topic 5.2) decides how strict it is.
04
Durability: at COMMIT, the WAL records up to the commit record are fsynced to disk before success is returned (synchronous_commit = on). Data pages are written later by checkpoints; after a crash, WAL replay reconstructs them (Phase 6.3).
05
Savepoints: SAVEPOINT s1; ... ROLLBACK TO s1; undoes part of a transaction. Handy for retrying one step, but each savepoint creates a subtransaction, and heavy use (thousands per transaction) causes performance problems in PostgreSQL.

Code & diagrams

transfer.sqlsql

BEGIN;
UPDATE account SET balance = balance - 500 WHERE id = 1 AND balance >= 500;
-- check 1 row updated in the application; if 0 -> ROLLBACK (insufficient funds)
UPDATE account SET balance = balance + 500 WHERE id = 2;
INSERT INTO ledger_entry (txn_id, account_id, amount) VALUES
  ('t-981', 1, -500), ('t-981', 2, 500);
COMMIT;   -- returns only after the WAL commit record is on disk

-- partial rollback with a savepoint
BEGIN;
INSERT INTO audit_log (msg) VALUES ('import started');
SAVEPOINT row_42;
INSERT INTO item VALUES (42, 'bad data');   -- fails a CHECK
ROLLBACK TO row_42;                          -- the audit row survives
COMMIT;

Interview problem

The problem

Order placement across stock, order and payment

Placing an order must decrement stock, create the order and its lines, and record a pending payment. Stock and orders are in the same PostgreSQL database; the payment provider is an external HTTP API. Define the transaction boundaries.

When it breaks

Network calls inside a database transaction

What you see

Transactions stay open for seconds while holding row locks; connection pools exhaust; long transactions block VACUUM; timeouts leave external side effects without matching database state.

Fix & prevent

Keep transactions short and local; do external calls before (validation) or after (via outbox) with idempotency keys.

synchronous_commit = off set globally for speed

What you see

A crash loses up to ~3× wal_writer_delay (hundreds of ms) of acknowledged commits, e.g. confirmed payments vanish.

Fix & prevent

Use it only per-transaction for data you can lose (SET LOCAL synchronous_commit = off for analytics events).

Explain it without notes

How does PostgreSQL make a transaction atomic without undo logs?

Practice

Explain what happens if the server crashes after UPDATE but before COMMIT in the transfer.

Trade-offs

↔
Bigger transactions give stronger atomicity but hold locks longer; split long work into short transactions with sagas and idempotency.

Done when you can

I can explain how each ACID property is implemented and draw correct transaction boundaries.

Command Palette