Transactions and MVCC

Isolation levels

READ COMMITTED, REPEATABLE READ and SERIALIZABLE differ in when the snapshot is taken and in what PostgreSQL does when two transactions touch the same data.

In plain words

Two people editing the same spreadsheet at once can get in each other's way. An isolation level is the rule that decides how much one transaction may notice of another one running at the same time.

READ COMMITTED (the default): each statement sees everything committed before it started. Simple and rarely fails, but two identical SELECTs in one transaction can return different answers.
REPEATABLE READ: the whole transaction sees one frozen picture of the data. If it tries to change a row that someone else changed in the meantime, PostgreSQL stops it with an error instead of guessing.
SERIALIZABLE: the result is guaranteed to be the same as if the transactions had run one after another. PostgreSQL watches who read what, and cancels one transaction when the combination might not match any one-by-one order (it errs on the safe side, so some cancellations are false alarms).

The stronger levels do not make ordinary transactions wait longer; instead they make PostgreSQL say "try again" more often. Applications using them must be ready to retry the whole transaction. Pick a scenario and a level below the picture and watch the same two sessions play out.

For DBAs and operators

What each level allows in PostgreSQL

Anomaly	READ COMMITTED	REPEATABLE READ	SERIALIZABLE
Dirty read	no	no	no
Non-repeatable read	possible	no	no
Phantom read	possible	no (stricter than the SQL standard)	no
Lost update	possible for read-modify-write in the app	error 40001	error 40001
Write skew	possible	possible	error 40001

READ UNCOMMITTED is accepted but behaves as READ COMMITTED: PostgreSQL never shows uncommitted data.

READ COMMITTED and the re-check

If an UPDATE or DELETE finds that its target row was changed by a transaction that has since committed, it waits for that transaction and then re-evaluates the WHERE clause on the newest version ("EvalPlanQual") instead of failing. That is why SET stock = stock - 1 works correctly, while reading a value into the application and writing back a computed value does not.

Serialization failures (SQLSTATE 40001)

REPEATABLE READ and SERIALIZABLE refuse to update a row version newer than their snapshot:

ERROR: could not serialize access due to concurrent update

(or concurrent delete). SERIALIZABLE uses Serializable Snapshot Isolation: reads take SIReadLock predicate locks (visible in pg_locks), and when a "dangerous structure" of read/write dependencies appears, one transaction is cancelled:

ERROR: could not serialize access due to read/write dependencies among transactions
DETAIL: Reason code: Canceled on identification as a pivot, during commit attempt.
HINT: The transaction might succeed if retried.

Retry advice for applications

Retry the entire transaction (from BEGIN) on SQLSTATE 40001, and also on 40P01 (deadlock). Re-running only the failed statement is not enough.
Keep serializable transactions short and declare read-only ones READ ONLY; long reports can use SERIALIZABLE READ ONLY DEFERRABLE, which waits for a safe snapshot and then can never fail.
SSI false positives grow when predicate locks are promoted to page or relation granularity: raise max_pred_locks_per_transaction if you see many failures on big scans, and make sure queries use indexes.
Standbys cannot run SERIALIZABLE: ERROR: cannot use serializable mode in a hot standby

BEGIN ISOLATION LEVEL REPEATABLE READ;
SHOW transaction_isolation;

-- per role or database default
ALTER ROLE app SET default_transaction_isolation = 'serializable';

-- predicate locks currently held
SELECT locktype, relation::regclass, page, tuple, pid
  FROM pg_locks WHERE mode = 'SIReadLock';

-- rollbacks per database (serialization failures are included;
-- there is no separate counter for them)
SELECT datname, xact_rollback FROM pg_stat_database;

For developers: inside the source

Where it lives

xact.h: XACT_READ_UNCOMMITTED … XACT_SERIALIZABLE, and the two macros the rest of the code tests: IsolationUsesXactSnapshot() (XactIsoLevel >= XACT_REPEATABLE_READ) and IsolationIsSerializable(). The variables XactIsoLevel, DefaultXactIsoLevel and XactDeferrable live in xact.c. READ UNCOMMITTED never gets separate code paths.
snapmgr.c: GetTransactionSnapshot() takes a new snapshot per statement unless IsolationUsesXactSnapshot() (see Snapshots).
nodeModifyTable.c: ExecUpdate() and ExecDelete() react to TM_Updated/TM_Deleted from the table AM; execMain.c: EvalPlanQual().
predicate.c and predicate_internals.h: SSI.

Concurrent update: the fork in the road

case TM_Updated:
    if (IsolationUsesXactSnapshot())
        ereport(ERROR,
                (errcode(ERRCODE_T_R_SERIALIZATION_FAILURE),
                 errmsg("could not serialize access due to concurrent update")));
    /* READ COMMITTED: lock the newest version, re-check quals */
    table_tuple_lock(..., TUPLE_LOCK_FLAG_FIND_LAST_VERSION, ...);
    epqslot = EvalPlanQual(...);

heap_update() has already waited for the other transaction (XactLockTableWait()) before it reports TM_Updated; READ COMMITTED then re-runs the plan's quals on the newest row version and updates that one, or skips the row if it no longer matches.

SSI internals

Structures: SERIALIZABLEXACT per serializable transaction with inConflicts/outConflicts lists of RWConflictData; PREDICATELOCKTARGET (relation, page or tuple) and PREDICATELOCK, the SIReadLocks in pg_locks.
Reads call PredicateLockTID(), PredicateLockPage() or PredicateLockRelation(); CheckAndPromotePredicateLockRequest() promotes to a coarser target when MaxPredicateChildLocks() (from max_pred_locks_per_relation/_per_page) is exceeded.
Conflicts: CheckForSerializableConflictOut() (reader saw a version written by a concurrent transaction; called through HeapCheckForSerializableConflictOut()) and CheckForSerializableConflictIn() (writer touches a locked target; called from heap and index insert/update/delete) both end in FlagRWConflict() → OnConflict_CheckForSerializationFailure(). PreCommit_CheckForSerializationFailure() runs at commit: the "pivot, during commit attempt" error.
Locking: SerializableXactHashLock, SerializableFinishedListLock, the partitioned predicate lock manager (PredicateLockHashPartitionLock()); old committed transactions are summarized into the pg_serial SLRU (serializable_buffers). GetSafeSnapshot() implements READ ONLY DEFERRABLE; pg_safe_snapshot_blocking_pids() shows who it waits for.

Design and proofs: README-SSI.

Observe and experiment

Isolation specs: simple-write-skew.spec, classroom-scheduling.spec, receipt-report.spec, read-only-anomaly.spec, eval-plan-qual.spec; run with make -C src/test/isolation check.
Wait events SerializableXactHash or PredicateLockManager in pg_stat_activity point at SSI contention.

Parameters that shape this

default_transaction_isolation transaction_isolation default_transaction_deferrable max_pred_locks_per_transaction max_pred_locks_per_relation serializable_buffers deadlock_timeout