PostgreSQL Transactions

A transaction groups multiple statements so they succeed or fail together — either every change in it takes effect, or none of them do. This is critical whenever several related changes must stay consistent with each other, like transferring money between two accounts.

BEGIN, COMMIT, ROLLBACK

BEGIN;

UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;

COMMIT;

If anything goes wrong partway through, ROLLBACK undoes every change made since BEGIN:

BEGIN;

UPDATE accounts SET balance = balance - 100 WHERE id = 1;
-- something looks wrong here...

ROLLBACK;
-- the UPDATE above never happened, as if it was never run

Without a transaction, if the second UPDATE failed after the first one succeeded, account 1 would lose $100 that never arrived anywhere — the transaction guarantees that can't happen.

Implicit Auto-Commit

Every individual statement outside an explicit BEGIN/COMMIT block runs in its own single-statement transaction automatically — that's why a lone UPDATE or DELETE takes effect immediately.

SAVEPOINT — Partial Rollback

Lets you undo part of a transaction without discarding everything.

BEGIN;

UPDATE accounts SET balance = balance - 100 WHERE id = 1;

SAVEPOINT before_bonus;
UPDATE accounts SET balance = balance + 500 WHERE id = 1; -- oops, wrong amount

ROLLBACK TO before_bonus;
-- only the bonus update is undone; the first UPDATE still stands

COMMIT;

ACID — What Transactions Guarantee

• Atomicity — all statements in the transaction succeed, or none do.
• Consistency — the database moves from one valid state to another, never a broken in-between state.
• Isolation — concurrent transactions don't see each other's uncommitted changes (see the Isolation Levels lesson).
• Durability — once committed, the change survives even a crash immediately afterward.

Common Errors & Troubleshooting

• ERROR: current transaction is aborted, commands ignored until end of transaction block — once any statement inside a transaction errors, every subsequent statement is refused until you ROLLBACK (or roll back to a SAVEPOINT) — you can't just "continue" past the error.
• Forgetting to COMMIT and losing changes on disconnect — if your client disconnects (or the session times out) before COMMIT, an open transaction's changes are automatically rolled back.
• A transaction left open, blocking other queries — a forgotten BEGIN with no matching COMMIT/ROLLBACK can hold locks indefinitely; check pg_stat_activity for long-idle transactions if something seems stuck (see the Locking lesson).

Key Takeaways

• BEGIN starts a transaction; COMMIT makes its changes permanent; ROLLBACK undoes all of them.
• Every statement you run outside an explicit transaction is auto-committed as its own tiny transaction.
• SAVEPOINT lets you roll back just part of a longer transaction instead of the whole thing.