Transactions & Isolation Levels

ACID, briefly

Property	Guarantees
Atomicity	a transaction's statements all succeed or all roll back — no partial application
Consistency	a transaction moves the database from one valid state to another, respecting constraints
Isolation	concurrent transactions don't see each other's uncommitted intermediate state (to a degree set by the isolation level — see below)
Durability	once committed, a transaction survives a crash

BEGIN, COMMIT, ROLLBACK

BEGIN;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;
COMMIT;
-- both updates happen together, or (on error, or an explicit ROLLBACK) neither does

Outside an explicit BEGIN, PostgreSQL runs every individual statement as its own implicit transaction. The example above is the canonical case for needing an explicit one: a transfer that debits one account and credits another must never be observed or persisted half-done.

the anomalies isolation levels prevent

Anomaly	What happens
Dirty read	reading another transaction's uncommitted changes
Non-repeatable read	re-reading the same row within a transaction gives a different result, because another transaction committed a change in between
Phantom read	re-running the same filtered query within a transaction returns different rows, because another transaction inserted/deleted a matching row in between

PostgreSQL's isolation levels

Level	Prevents	Notes
Read Committed (default)	dirty reads only	each statement sees a fresh snapshot as of when it starts
Repeatable Read	dirty + non-repeatable reads (and, in Postgres specifically, phantom reads too)	the whole transaction sees one snapshot, taken at its first statement
Serializable	all of the above, fully	behaves as if transactions ran one at a time in some order — enforced by detecting conflicts and forcing a retry, not by literally serializing execution

BEGIN ISOLATION LEVEL SERIALIZABLE;
-- ... statements ...
COMMIT;
-- may fail with a serialization_failure error under contention — application code
-- using SERIALIZABLE must be prepared to catch that and retry the transaction

PostgreSQL's Repeatable Read is stricter than the SQL standard requires (the standard allows phantom reads at that level; Postgres's snapshot-based implementation happens to prevent them too) — worth knowing if you're used to another engine's exact terminology.

MVCC, in one paragraph

PostgreSQL never overwrites a row in place on UPDATE — it writes a new row version and marks the old one as superseded, keeping both around until nothing could still need the old version (then VACUUM reclaims the space). This is what lets readers avoid blocking writers and vice versa: a reader's snapshot simply ignores row versions created after it started, and a writer doesn't need to wait for readers to finish. It's also why an update-heavy table needs regular VACUUM — dead row versions otherwise accumulate.

where to go from here

Locking & Concurrency — the explicit locking tools that sit alongside MVCC and isolation levels.