Transactions & Isolation
BEGIN/COMMIT/ROLLBACK, savepoints, isolation levels
MVCC explained how snapshots let transactions avoid blocking each other. This lesson covers the transaction itself — how to control where it starts and ends, and how much of that concurrent activity it's allowed to see.
BEGIN, COMMIT, ROLLBACK, SAVEPOINT
Every statement outside an explicit transaction runs in its own
implicit one. Wrapping several statements in BEGIN/COMMIT makes them
atomic — either all of them take effect or none do:
BEGIN;
UPDATE accounts SET balance = balance - 50 WHERE id = 1;
UPDATE accounts SET balance = balance + 50 WHERE id = 2;
COMMIT; -- both changes visible, or...
ROLLBACK; -- ...neither isSAVEPOINT lets you roll back part of a transaction without abandoning the
whole thing — useful for "try this, and if it fails, fall back" logic
inside a single transaction:
BEGIN;
INSERT INTO accounts (balance) VALUES (100);
SAVEPOINT before_risky_insert;
INSERT INTO accounts (balance) VALUES (-1); -- fails a CHECK constraint
ROLLBACK TO SAVEPOINT before_risky_insert;
INSERT INTO accounts (balance) VALUES (100); -- transaction is usable again
COMMIT;Once any statement in a transaction errors, the whole transaction is
aborted and every subsequent statement is rejected with "current
transaction is aborted" — until you either ROLLBACK entirely or roll
back to a savepoint taken before the error.
Isolation levels
The SQL standard defines four isolation levels, each permitting fewer anomalies than the last. Postgres implements three of them distinctly — Read Uncommitted behaves exactly like Read Committed, because Postgres's MVCC never allows a dirty read in the first place.
| Level | Dirty read | Non-repeatable read | Phantom read | Serialization anomaly |
|---|---|---|---|---|
| Read Uncommitted | never (same as RC) | possible | possible | possible |
| Read Committed (default) | never | possible | possible | possible |
| Repeatable Read | never | never | never* | possible |
| Serializable | never | never | never | never |
Postgres defaults to Read Committed, not Repeatable Read like some other engines. Under Read Committed, each statement gets a fresh snapshot — a long transaction can see a different value for the same row each time it queries, if another transaction committed a change in between.
Repeatable Read takes one snapshot for the whole transaction — every query sees the data exactly as it stood when the transaction began. Postgres's implementation also blocks phantom reads (Postgres's Repeatable Read is actually closer to the standard's Snapshot Isolation than the bare minimum the spec requires).
Serializable goes further: it detects when concurrent transactions would have produced a result impossible under any serial (one-at-a-time) execution order, and aborts one of them with a serialization error your application must be ready to retry.
BEGIN ISOLATION LEVEL SERIALIZABLE;
-- ...
COMMIT;
-- may fail with:
-- ERROR: could not serialize access due to read/write dependenciesLocking a row on purpose
SELECT ... FOR UPDATE takes an explicit row lock as part of a read,
blocking other transactions from locking or updating the same rows until
you commit — the classic "read a balance, then update it" pattern:
BEGIN;
SELECT balance FROM accounts WHERE id = 1 FOR UPDATE;
-- other transactions trying to UPDATE or FOR UPDATE this row now wait
UPDATE accounts SET balance = balance - 50 WHERE id = 1;
COMMIT;This is a bridge into explicit locking — the full set of lock modes, what conflicts with what, and how deadlocks get resolved, is next in Locks & Deadlocks.