PostgreSQL Isolation Levels Explained

Isolation is the "I" in ACID, and it's the one that causes real bugs in production, the kind that only appear under concurrent load and are almost impossible to reproduce locally. Two requests hit the same rows at the same moment and produce a result neither would alone: a double-charged customer, an oversold ticket, a balance that goes negative. Isolation levels are the dial that controls how much concurrent transactions can interfere, and knowing which level you're running is the difference between correct code and a race waiting to happen.

This is part 18 of the PostgreSQL Masterclass, following transactions and ACID.

The anomalies isolation prevents

Isolation levels are defined by which concurrency anomalies they allow. Four matter:

• Dirty read: seeing another transaction's uncommitted changes. Postgres never allows this, even at its lowest level.
• Non-repeatable read: reading a row twice in one transaction and getting different values, because another transaction committed a change in between.
• Phantom read: running the same query twice and getting different rows, because another transaction inserted or deleted matching rows in between.
• Serialization anomaly: the result of committing a group of transactions is inconsistent with running them one at a time, even though each individually looks fine.

Each isolation level draws the line at a different anomaly.

Read Committed: the default

Postgres runs at Read Committed by default. Each statement sees a snapshot of data as of the moment that statement began, including everything committed before it. Within one transaction, two identical queries can return different results if another transaction committed in between, so non-repeatable and phantom reads are both possible.

This is fine for the majority of workloads, and it's why most apps never think about isolation. The trap is read-modify-write logic:

-- Read Committed: this has a race condition
BEGIN;
  SELECT stock FROM products WHERE id = 1;   -- app reads stock = 1
  -- another transaction sells the same item here
  UPDATE products SET stock = stock - 1 WHERE id = 1;  -- now -1
COMMIT;

The SELECT and the decision based on it aren't protected against a concurrent change. Two customers both read "1 in stock," both buy, and you've oversold. Read Committed doesn't stop this, because the two transactions don't touch until the writes, by which point both decisions are made.

Repeatable Read: a stable snapshot

Repeatable Read gives the whole transaction a single snapshot taken at its start. Every query sees the database as it was when the transaction began, ignoring anything committed afterward. Non-repeatable and phantom reads disappear: read a row twice and you get the same value, run a query twice and you get the same rows.

BEGIN ISOLATION LEVEL REPEATABLE READ;
  SELECT ...;   -- consistent snapshot for the whole transaction
  SELECT ...;   -- same view, regardless of concurrent commits
COMMIT;

There's a catch, and it's important. If your Repeatable Read transaction tries to update a row that another transaction changed and committed after your snapshot, Postgres raises a serialization failure rather than silently working on stale data:

ERROR: could not serialize access due to concurrent update

This is a feature, not a bug. It's Postgres refusing to let you overwrite a change you didn't see. Your application must catch this error and retry the transaction. Any code using Repeatable Read or Serializable needs retry logic.

Serializable: as if one at a time

Serializable is the strongest level. It guarantees the outcome is equivalent to running all concurrent transactions in some serial order, one after another. It prevents every anomaly, including serialization anomalies that Repeatable Read allows. Postgres implements this efficiently with Serializable Snapshot Isolation, which detects dangerous patterns among concurrent transactions and aborts one with a serialization error.

The oversell example above, run under Serializable, is safe: one of the two concurrent buyers gets a serialization failure and retries, and the retry sees the updated stock. You pay for this with more serialization failures under contention, so again, retry logic is mandatory.

BEGIN ISOLATION LEVEL SERIALIZABLE;
  -- read and write freely; Postgres ensures a serial-equivalent result
COMMIT;   -- may raise a serialization failure to catch and retry

Choosing a level

The practical guidance:

• Read Committed (default) for most work. Handle read-modify-write races with explicit row locks (SELECT FOR UPDATE, covered soon) rather than raising the isolation level.
• Repeatable Read when a transaction reads many rows and needs them all consistent as of one moment, like a report or a multi-step calculation.
• Serializable when correctness under concurrency is critical and you'd rather retry than reason about every possible interleaving, such as financial operations. Pair it with a retry loop.

The higher levels trade throughput and occasional retries for stronger guarantees. Most systems run Read Committed and reach for explicit locking on the specific operations that need it, which is the subject of the next few articles. But when you have genuinely tricky invariants across multiple rows, Serializable plus retries is often simpler and safer than hand-rolled locking.

The one thing you should never do is assume Read Committed protects a read-modify-write. It doesn't, and that assumption is behind a large share of the "it worked in testing" concurrency bugs that reach production.

Next, we go under the isolation levels to the mechanism that enforces them at the row level: locks, what they block, and how to avoid turning them into contention.