Chapter 17: ACID — The Four Guarantees

ACID is the set of properties that make database transactions reliable. Without ACID, your data would be corrupted by crashes, concurrent access, and partial failures.

What is a Transaction?

A transaction is a group of operations that must succeed or fail as a unit:

BEGIN;                                          -- start transaction
  UPDATE accounts SET balance = balance - 100 WHERE id = 1;  -- debit
  UPDATE accounts SET balance = balance + 100 WHERE id = 2;  -- credit
  INSERT INTO transfers (from_id, to_id, amount) VALUES (1, 2, 100);
COMMIT;                                         -- make permanent

-- If ANYTHING fails between BEGIN and COMMIT:
ROLLBACK;                                       -- undo everything

A — Atomicity

"All or nothing." Either all operations in a transaction complete, or none do.

How it works: Write-Ahead Log (WAL). Before modifying data, the database writes the intended change to a log. On crash, it replays or undoes the log to restore consistency.

C — Consistency

"Data always satisfies all rules." A transaction moves the database from one valid state to another. Constraints (NOT NULL, UNIQUE, CHECK, FK) are never violated.

BEGIN;
  UPDATE accounts SET balance = balance - 1000 WHERE id = 1;
  -- If CHECK (balance >= 0) exists and balance would go negative:
  -- → ERROR! Transaction is rolled back. Constraint preserved.
COMMIT;

I — Isolation

"Concurrent transactions don't interfere." Each transaction sees a consistent snapshot, as if it were the only one running.

Isolation levels (covered in next chapter) control how much isolation you get vs. performance.

D — Durability

"Once committed, data survives any failure." Even if the server crashes, loses power, or the disk fails (with replication), committed data is never lost.

How it works:

• WAL is flushed to disk (fsync) before COMMIT returns
• Even if data pages haven't been written yet, the WAL has the changes
• On restart, replay WAL to recover committed transactions

Transaction Lifecycle

Implicit vs Explicit Transactions

-- Explicit transaction (you control boundaries)
BEGIN;
  INSERT INTO users (name) VALUES ('Alice');
  INSERT INTO profiles (user_id, bio) VALUES (currval('users_id_seq'), 'Hello');
COMMIT;

-- Implicit transaction (each statement is its own transaction)
INSERT INTO users (name) VALUES ('Bob');
-- This is equivalent to: BEGIN; INSERT...; COMMIT;