Transaction Isolation

Transaction isolation defines how concurrent transactions can affect one another’s observations and outcomes. In PostgreSQL, ordinary read-committed SELECT statements each receive a fresh snapshot. Repeatable read keeps the snapshot established by the first non-transaction-control statement. Both also see their own earlier writes; a stable snapshot is not a rule that hides a transaction’s own changes.

A changed value on rereading a row is a non-repeatable read; a changed set matching a predicate is a phantom. PostgreSQL repeatable read prevents both but can still permit write skew. Two on-call doctors may each see the other available and update different rows to go off duty, leaving nobody available after both commit.

When the participating transactions use serializable isolation and correctly check the invariant, committed results must be equivalent to some serial ordering. The database may reject one transaction. Retry the entire transaction, including the reads and decision, rather than only its final UPDATE. Serializable does not repair incorrect business logic or safely repeat external side effects by itself. Same-named levels in another engine require checking that engine’s rules.

Further reading

• PostgreSQL Documentation, Transaction Isolation

See the worked examples in Relational Database Internals.