BrevFeed
Sign in

49. Transactions and ACID

📖 Reading · 7 min
💡 Most code boxes below are live — edit one and hit Run. Boxes without a Run button are reference-only (they need a real Postgres server).

Why transactions exist: the multi-statement problem

Every write so far in this course has been a single statement. Real operations are often multiple statements that must succeed or fail together — the canonical example: transferring money between two accounts is a debit and a credit, two separate UPDATEs. If the debit succeeds and the credit fails (a crash, a constraint violation, anything), you cannot leave the database in that half-done state — money would simply vanish. A transaction is how SQL guarantees "all of these statements happen, or none of them do."

ACID, precisely

Four guarantees, each solving a specific failure mode:

  • Atomicity — a transaction's statements are treated as one indivisible unit. Either every statement's effects persist, or none do — no partial application, even across a crash mid-transaction.
  • Consistency — a transaction moves the database from one valid state to another, never violating a constraint (Module 2) along the way. This is really a consequence of the other three properties plus your constraints being correctly defined, more than an independent mechanism.
  • Isolation — concurrent transactions don't see each other's uncommitted, in-progress changes (the full subject of the next lesson — isolation has levels, not just an on/off state).
  • Durability — once a transaction commits, its effects survive even a subsequent crash (written to durable storage, not just memory).

BEGIN, COMMIT, ROLLBACK — the mechanism

BEGIN;
UPDATE customer SET email = 'new@example.com' WHERE customer_id = 1;
UPDATE customer SET email = 'other@example.com' WHERE customer_id = 2;
COMMIT;   -- both updates become permanent together

Or, to abandon the whole thing:

BEGIN;
UPDATE customer SET email = 'mistake@example.com' WHERE customer_id = 1;
ROLLBACK;   -- undoes the update entirely, as if it never happened

Every single statement outside an explicit BEGIN runs in its own implicit one-statement transaction, auto-committed immediately — this is why every UPDATE/INSERT/DELETE you've run casually throughout this course "just worked" without you ever typing BEGIN/COMMIT. Explicit transactions matter specifically when you need multiple statements to succeed or fail as one unit.

What "seeing" an uncommitted change means

Within the same session, your own uncommitted changes are visible to your own subsequent statements immediately — this isn't isolation being violated, it's just normal sequential execution:

BEGIN;
UPDATE customer SET email = 'temp@example.com' WHERE customer_id = 1;
SELECT email FROM customer WHERE customer_id = 1;  -- shows 'temp@example.com' already
ROLLBACK;
SELECT email FROM customer WHERE customer_id = 1;  -- back to the original, unchanged

Whether a different session/connection can see your uncommitted change before you COMMIT is the actual subject of isolation (next lesson) — under every standard isolation level, the answer is no, other sessions never see uncommitted data, full stop.

SAVEPOINT: a checkpoint within a transaction

A SAVEPOINT lets you roll back part of a transaction without abandoning the whole thing — useful when a multi-step transaction has one risky step you want to be able to retry or skip without losing the steps before it:

BEGIN;
UPDATE customer SET email = 'ok@example.com' WHERE customer_id = 1;

SAVEPOINT before_risky_step;
UPDATE customer SET email = 'oops@example.com' WHERE customer_id = 999999; -- matches nothing, but imagine it errored
ROLLBACK TO SAVEPOINT before_risky_step;   -- undo just this step, keep the transaction open

UPDATE customer SET email = 'other@example.com' WHERE customer_id = 2;
COMMIT;   -- customer 1 and customer 2 updates persist; the "risky" attempt does not

ROLLBACK TO SAVEPOINT undoes everything back to that named point, but keeps the transaction itself open — you can keep working, and eventually COMMIT or fully ROLLBACK the whole thing. This is distinct from a plain ROLLBACK, which ends the transaction entirely.

A critical, real gotcha: errors abort the whole transaction

In Postgres, once any statement inside a transaction errors, every subsequent statement in that transaction is rejected — even valid ones — until you either ROLLBACK (the whole transaction) or ROLLBACK TO a SAVEPOINT taken before the error:

BEGIN;
UPDATE customer SET email = 'fine@example.com' WHERE customer_id = 1;
SELECT 1/0;   -- an error — division by zero
UPDATE customer SET email = 'also-fine@example.com' WHERE customer_id = 2;
-- ERROR: current transaction is aborted, commands ignored until end of transaction block
COMMIT;   -- this COMMIT does nothing useful — the transaction is already dead, effectively a ROLLBACK

This is a genuinely common source of confusion for people used to other databases (some engines let you continue a transaction after a non-fatal error) — in Postgres, wrap any step you expect might fail in its own SAVEPOINT if you want to recover from it and keep going, rather than assuming the transaction survives an error on its own.

Check yourself

  1. What does "Atomicity" guarantee, specifically, that a sequence of auto-committed individual statements does not?
  2. What's the difference between ROLLBACK and ROLLBACK TO SAVEPOINT name?
  3. A transaction has an error on its third statement, and you don't ROLLBACK or use a SAVEPOINT. What happens to a fourth, otherwise valid statement in that same transaction?
← Previous Next →