Transactions & savepoints

All-or-nothing changes, and partial rollback.

All or nothing

A transaction groups statements so they all commit together or not at all — the heart of data integrity (the classic example: a bank transfer must debit and credit, never just one):

BEGIN;
  UPDATE accounts SET balance = balance - 100 WHERE id = 1;
  UPDATE accounts SET balance = balance + 100 WHERE id = 2;
COMMIT;   -- or ROLLBACK to undo everything

Inside a transaction, a savepoint is a checkpoint you can roll back to without abandoning the whole transaction:

SAVEPOINT sp1;
...risky change...
ROLLBACK TO SAVEPOINT sp1;   -- undo just that part

(The exercises below run inside a managed transaction, so you'll use savepoints to see rollback in action safely.)

ROLLBACK TO SAVEPOINT undoes the work after sp1 and nothing before it — the transaction is still open and can carry on to COMMIT. A plain ROLLBACK would have thrown away the whole transaction instead.

Atomicity is the guarantee that the middle case — debit applied, credit lost — has no way to exist, including when the server dies between the two UPDATEs.