Developer/ConsistencyConcurrency/IsolationLevels/Fundamentals/postgresql-transaction-isolation-levels

PostgreSQL Transaction Isolation Levels

Concept

The I in ACID promises that concurrent transactions behave as if they ran one at a time, but full isolation is expensive — it means locking or re-checking everything a transaction touched, which serializes work that could otherwise run in parallel. Every real database gives applications a dial between "fully isolated but slow" and "fast but exposed to specific race conditions," and the isolation level is that dial. PostgreSQL implements three of the SQL standard's four levels (it silently upgrades READ UNCOMMITTED to READ COMMITTED, since PostgreSQL's MVCC architecture never actually produces dirty reads):

Read Committed (the default) — each individual statement within a transaction sees a fresh snapshot of the database taken at the moment that statement starts. Two SELECTs in the same transaction, several seconds apart, can return different results if another transaction committed in between.
Repeatable Read — the whole transaction uses one snapshot, taken at its first statement. Every SELECT in that transaction sees the same data no matter how much other transactions commit in the meantime, and if the transaction tries to modify a row that changed since its snapshot, it fails with a serialization error rather than silently overwriting.
Serializable — the strongest level. PostgreSQL uses Serializable Snapshot Isolation (SSI) — it runs transactions concurrently (no extra locking overhead like traditional 2PL-based serializability) but tracks read/write dependencies between concurrent transactions and aborts one with a serialization error if committing both would produce a result impossible from any one-at-a-time ordering.

The levels form a strict hierarchy of what anomalies they prevent. Read Committed prevents dirty reads (never seeing another transaction's uncommitted writes) but allows non-repeatable reads (a row you read once can have a different value if you read it again) and phantom reads (a range query can return different rows on a second run). Repeatable Read additionally prevents both of those, but still allows serialization anomalies — cases where each transaction individually looks fine, but the combined result of running them concurrently couldn't have happened in any serial ordering. Only Serializable closes that last gap.

Tradeoffs

Level	Guarantees	Allows	Cost
Read Committed (default)	No dirty reads	Non-repeatable reads, phantom reads, serialization anomalies	Lowest — no extra locking or retry logic needed by the app
Repeatable Read	+ no non-repeatable/phantom reads within a transaction	Serialization anomalies (rare, but real — see scenario below)	Moderate — the app must handle serialization-failure retries on write conflicts
Serializable	+ no serialization anomalies — fully equivalent to some one-at-a-time ordering	Nothing (by definition)	Highest — more transactions abort under contention and must be retried; SSI's dependency tracking adds bookkeeping overhead

The real tradeoff isn't raw throughput (PostgreSQL's SSI is notably cheaper than classic lock-based serializability) — it's retry complexity. Every level above Read Committed can fail a transaction with a serialization error that the application must catch and retry; Read Committed never does, which is exactly why it's the default most ORMs and frameworks assume without discussion.

When to use / when not to

Read Committed is correct for the overwhelming majority of application code — most business logic doesn't actually depend on multiple statements in one transaction seeing a perfectly consistent snapshot of the whole database, and it's the only level that never requires retry logic.
Repeatable Read earns its cost when a transaction runs multiple reads that must be mutually consistent with each other (e.g., a report that reads several related tables and must not see a partial update landing mid-transaction) — but the app needs to retry on serialization failure for any write in that transaction.
Serializable is for the specific case where a lost update or write skew anomaly would cause real business damage and can't be prevented by a targeted lock instead — see the scenario below. It's rarely the default for a whole application; it's usually applied selectively to the handful of transactions where correctness genuinely can't tolerate the gap.
Don't reach for Serializable as a blanket fix for "concurrency bugs" — it adds retry-on-abort complexity everywhere it's used, and most races people worry about are actually solvable more cheaply with SELECT ... FOR UPDATE (an explicit row lock) scoped to just the rows that matter, without paying Serializable's cost application-wide.
Don't assume Repeatable Read is "safe enough" for anything involving a check-then-write across more than one row or table — that's exactly the write-skew anomaly it doesn't prevent.

Common pitfall

Assuming Read Committed's per-statement fresh snapshot means "a SELECT followed by an UPDATE in the same transaction is safe" — it isn't, for the classic lost update: transaction A reads a row (available_slots = 1), transaction B reads the same row concurrently (also sees 1), both decide the slot is available and both UPDATE, and the second commit silently overwrites the first — the row ends at whatever the second writer computed, with no error, no warning, and two bookings for one slot. Read Committed doesn't protect against this because each statement gets its own fresh snapshot, and neither transaction's UPDATE conflicted with a snapshot it had already taken — the read and the eventual write are two separate statements, and the anomaly lives in the gap between them.

A concrete walked-through scenario: an events app lets users book the last available seat. Under Read Committed, both requests SELECT slots_remaining (get 1), both then UPDATE ... SET slots_remaining = slots_remaining - 1, and the row ends at 0 — from one booking, with the other's decrement silently absorbed. Two users believe they have a confirmed seat. The fix isn't necessarily jumping to Serializable — SELECT slots_remaining FROM seats WHERE id = ? FOR UPDATE forces the second transaction's read to block until the first commits, so it sees the updated value (0) and can correctly reject the second booking. This is cheaper than making the whole transaction Serializable, because it locks exactly the row that matters instead of adding dependency-tracking overhead to every table the transaction touches.

Engineering Lens

The isolation-level decision is a case where "what's the default" and "what's correct for this specific transaction" are genuinely different questions, and conflating them is the actual pitfall — not picking the wrong level in the abstract, but never asking the question at all because the framework's default is invisible until an incident surfaces it. The strong answer in a design review isn't "we use Postgres, so it's ACID and we're covered" — it's naming the specific write path where a lost update or write-skew anomaly would cause real damage (double-booking, double-spend, an inventory count going negative) and showing that path was deliberately protected, whether by FOR UPDATE, Repeatable Read with retry logic, or full Serializable — not left on the Read Committed default by omission. This is the same underlying discipline as circuit breaker tuning or thread pool sizing: a default that works for 95% of cases hides the fact that the other 5% needs a deliberate, tested decision, not a guess.

ACID Transactions and the ACID vs BASE Tradeoff — isolation levels are the concrete, tunable implementation of the "I" this note names in the abstract.
Shopify: Replacing Redis with MySQL for Inventory Reservations — an optimistic-concurrency alternative to FOR UPDATE's pessimistic locking for guarding the same class of lost-update race described above.