Concurrencyread committedrepeatable readserializablesnapshot isolationssi

Isolation Levels

Read Uncommitted, Read Committed, Repeatable Read and Serializable are four points on a dial between throughput and anomalies; PostgreSQL implements three of them, stronger than the standard requires, and the right one depends on which anomaly your code can survive.

▶ Interactive Interview question See how this works internally →

Progress

The levels

Read Uncommitted: may see uncommitted data. PostgreSQL treats it as Read Committed. Read Committed: every statement sees a snapshot of committed data as of its start; the default. Non-repeatable reads, phantoms and lost updates are all possible across statements. Repeatable Read: the whole transaction sees one snapshot as of its first statement. In PostgreSQL this is full snapshot isolation — no non-repeatable reads, no phantoms; a write conflict with a concurrent committed write raises a serialization failure. Write skew is still possible. Serializable: snapshot isolation plus tracking of read/write dependencies (SSI); any interleaving that has no equivalent serial order aborts one transaction with SQLSTATE 40001. The only level that prevents write skew.

What each level still allows in PostgreSQL

Anomaly	Read Committed	Repeatable Read	Serializable
Dirty read	prevented	prevented	prevented
Non-repeatable read	possible	prevented	prevented
Phantom read	possible	prevented (std: possible)	prevented
Lost update	possible	prevented (aborts)	prevented (aborts)
Write skew	possible	possible	prevented (aborts)

Choosing

Stay at Read Committed for ordinary request handling, and make each statement self-contained: SET x = x + 1 rather than read-then-write; INSERT … ON CONFLICT rather than check-then-insert; FOR UPDATE when you must read first. This is the highest-throughput level and most application code lives here without knowing it.

Use Repeatable Read for anything that reads the same data more than once and needs it consistent: reports, exports, multi-step calculations. Use Serializable when correctness depends on a condition you checked but did not write — booking a slot if none is booked, going off call if someone else is on. And only if you have implemented the retry: at Serializable, 40001 is normal operation.

The retry loop Serializable requires

1def run_serializable(fn, attempts=5):
2    for i in range(attempts):
3        try:
4            with db.transaction(isolation="SERIALIZABLE"):
5                return fn()
6        except SerializationFailure:      # SQLSTATE 40001
7            sleep(0.01 * 2 ** i)          # back off, then run the whole transaction again
8    raise TooMuchContention()

What the standard says versus what you get

The SQL standard defines the levels by which anomalies they forbid, and permits phantoms at Repeatable Read. PostgreSQL implements Repeatable Read as snapshot isolation, which happens to forbid phantoms too. Other engines differ: MySQL InnoDB’s Repeatable Read uses gap locks and behaves differently again; Oracle has no Repeatable Read at all and its Serializable is snapshot isolation, which does *not* prevent write skew. "Serializable" is not a portable promise. Know your engine.

Key points

Read Committed: snapshot per statement. Repeatable Read: snapshot per transaction. Serializable: snapshot plus dependency tracking.
PostgreSQL’s Repeatable Read prevents phantoms; its Serializable prevents write skew. Both can abort with 40001.
Default to Read Committed with self-contained statements; raise the level for a reason, and implement retries.
Isolation level names are not portable across engines.

Isolation levels matrix

Isolation levels and what each one still allows

A tick means the anomaly can still happen at that level. Pick a level and an anomaly to see the combination.

Anomaly	Read Uncommitted	Read Committed	Repeatable Read	Serializable
Dirty read	possible	prevented	prevented	prevented
Lost update	possible	possible	prevented	prevented
Non-repeatable read	possible	possible	prevented	prevented
Phantom read	possible	possible	prevented	prevented
Write skew	possible	possible	possible	prevented

BEGIN ISOLATION LEVEL READ COMMITTED;

In PostgreSQL: The default. A new snapshot per *statement*, so two identical queries in one transaction can disagree. Readers never block writers and writers never block readers.

Lost update at Read Committed: still possible. Read-modify-write from two sessions. At Read Committed you must lock explicitly; at Repeatable Read the second writer is aborted.

How to choose: stay at Read Committed for ordinary request handling and make each statement self-contained (SET x = x + 1, not read-then-write). Use Repeatable Read for reports and any transaction that reads the same data twice. Use Serializable when correctness depends on a condition you checked but did not write — and only if you have implemented the retry loop, because at Serializable a transaction failing with 40001 is normal operation, not an error.