ACID in practice

What each letter actually buys you

If you’ve ever debugged a half-applied batch or wondered why “wrapping it in a transaction” still caused corruption, the answer almost always lives in which letter you assumed versus which one you actually got.

ACID is four guarantees, and engineers conflate the one they don’t get with the ones they do.

• Atomicity — every statement between BEGIN and COMMIT lands together, or none does. Any error, or a ROLLBACK, throws the whole batch away. The half-finished state is never visible and never persisted.
• Consistency — a committed transaction leaves the database satisfying every constraint (foreign keys, CHECK, unique, NOT NULL). If a statement would violate one, the transaction errors and you must roll back. Postgres enforces the rules; your invariants (“balance never goes negative”) you encode as constraints or they aren’t guaranteed.
• Isolation — concurrent transactions don’t see each other’s uncommitted work. Crucially, isolation has levels: the default, READ COMMITTED, is a weak promise, not full serializability. This is the letter people assume gives them more than it does.
• Durability — once COMMIT returns, the change survives a crash or power loss. Postgres guarantees this by flushing the write-ahead log (WAL) to disk before acknowledging the commit.

-- Atomic money transfer: both rows move, or neither does.
BEGIN;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;
COMMIT;   -- only now is the transfer durable and visible to others

If the second UPDATE errors (say account 2 was deleted), the transaction enters an aborted state; the first UPDATE is discarded on ROLLBACK. No money leaks.

BEGIN, COMMIT, ROLLBACK, and the implicit transaction

Every statement you run is already in a transaction. Outside an explicit BEGIN, Postgres wraps each single statement in its own transaction that commits automatically (autocommit). That is why one UPDATE accounts SET balance = balance * 1.05 is itself atomic — all matched rows change or none do, even without BEGIN.

You write BEGIN only when you need multiple statements to be one atomic unit. After an error inside a transaction block, Postgres rejects every further statement with current transaction is aborted until you ROLLBACK (or COMMIT, which Postgres turns into a rollback). There is no “ignore the error and keep going” — unless you use a savepoint.

SAVEPOINT: partial rollback inside one transaction

A SAVEPOINT is a named marker you can roll back to without abandoning the whole transaction. It lets one transaction tolerate a recoverable error in part of its work.

BEGIN;
INSERT INTO jobs (id, status) VALUES (501, 'queued');

SAVEPOINT try_dedupe;
-- this might violate a unique index if the job already exists:
INSERT INTO jobs (id, status) VALUES (501, 'queued');
-- it errors; instead of losing the whole transaction, undo just to the savepoint:
ROLLBACK TO SAVEPOINT try_dedupe;

-- the first INSERT is still alive; continue and commit it:
COMMIT;

This is exactly what most drivers do under the hood for “nested transactions” — there are no real nested transactions in Postgres, only savepoints. The cost is real, though: each savepoint consumes a subtransaction ID, and a transaction that opens tens of thousands of them can trigger the notorious subtransaction (subxid) overflow — once a backend exceeds 64 live subtransactions, others must consult pg_subtrans on disk, and read latency on a busy primary can jump from microseconds to milliseconds. Savepoints are a scalpel, not a loop body.

What a transaction does NOT give you

A transaction by default is not a mutex and not serializable. Under READ COMMITTED (Postgres’s default) each statement sees a fresh snapshot taken at statement start, so two concurrent read-modify-write sequences can race. You get atomicity and durability for free; you do not get protection from lost updates, write skew, or phantom rows unless you raise the isolation level or take explicit row locks. Knowing this boundary is the whole reason the rest of this unit exists.