Isolation levels in PostgreSQL: which level for your transaction? Intermediate

The problem

An API reads a balance with two SELECTs, then decides whether to allow a transfer. Between the two, another transaction commits. The same code:

• under Read Committed reads two different points in time;
• under Repeatable Read reads one stable point;
• under Serializable may be rejected by PostgreSQL if the result is not equivalent to any serial execution order.

Isolation levels are not a “safer and safer” scale. They decide when the snapshot is taken, which conflicts are detected and which errors the application must handle.

PostgreSQL accepts the four SQL-standard names but implements only three behaviors: READ UNCOMMITTED behaves like READ COMMITTED (the default); REPEATABLE READ is snapshot isolation; SERIALIZABLE is Serializable Snapshot Isolation (SSI).

This article applies to PostgreSQL 17 on the primary; do not carry this behavior table over to MySQL or other systems.

Which level? (summary first)

Situation	Level to use	Accompanying condition
Short CRUD, the SQL puts the condition in the write statement	Read Committed (default)	Write atomic SQL, check row counts, FOR UPDATE when needed
A report reading several tables must see one point in time	Repeatable Read, READ ONLY	Keep queries short enough not to block VACUUM
A multi-row invariant that cannot be expressed with constraints or locks	Serializable	Whole-transaction retry is mandatory, monitor the abort rate

Core concepts

Anomaly matrix by level (PostgreSQL)

Anomaly	Read Committed	Repeatable Read	Serializable
Dirty read	No	No	No
Non-repeatable read	Yes	No	No
Phantom read	Yes	No (stronger than the SQL standard)	No
Lost update (read-modify-write in the app)	Yes	Blocked with error 40001	Blocked with error 40001
Write skew	Yes	Yes	Blocked: one transaction is aborted with 40001

See each anomaly run for real in the article on anomalies.

Three levels, three ways of taking a snapshot

• Read Committed: each statement takes a fresh snapshot. Two SELECTs in the same transaction can see two different states. For an UPDATE of the same row, PostgreSQL waits for the earlier writer, then re-evaluates the WHERE on the new version.
• Repeatable Read: the snapshot is fixed at the first statement that is not a transaction-control command (not at the word BEGIN). If it tries to update a row that another transaction committed after the snapshot, PostgreSQL raises 40001 instead of silently overwriting.
• Serializable: still reads from a snapshot, but SSI tracks read–write dependencies (predicate locks). When it sees a pattern that could violate serializability, one transaction is aborted with 40001. It does not mean transactions queue up and run one by one.

Same schedule, two isolation levels (initial balance = 100)

What session A sees after B commits: Read Committed vs Repeatable Read

◀ Prev▶ PlayNext ▶↺ RestartStep 1/5

Two variants of session A each open a transaction and read balance = 100. Repeatable Read keeps this snapshot until the transaction ends.

Under Serializable, an example where only B writes and A reads produces no error at all: 40001 only appears for schedules at risk of violating serializability.

A higher level does not make the logic correct

If the application computes wrongly (adding 20 instead of 30), Serializable still commits a “serializable” but business-wrong transaction. Constraints are still needed for rules expressible in the schema; idempotency is still needed when the client is unsure whether COMMIT succeeded; transactions still have to be short.

Retrying correctly

Under Repeatable Read and Serializable, 40001 is a signal to rerun the whole decision, including the reads. Retrying only the UPDATE is wrong because the old snapshot is no longer usable. 40P01 (deadlock) should go through the same retry wrapper.

async function withRetry<T>(fn: (c: PoolClient) => Promise<T>, maxAttempts = 5): Promise<T> {
  for (let attempt = 1; ; attempt++) {
    const client = await pool.connect();
    try {
      await client.query('BEGIN ISOLATION LEVEL SERIALIZABLE');
      const result = await fn(client);
      await client.query('COMMIT');            // COMMIT can also throw 40001
      return result;
    } catch (err: any) {
      await client.query('ROLLBACK').catch(() => {});
      const retryable = err.code === '40001' || err.code === '40P01';
      if (!retryable || attempt >= maxAttempts) throw err;
      await sleep(Math.random() * 50 * 2 ** attempt);   // short wait with jitter
    } finally {
      client.release();
    }
  }
}

Notes:

• Only retry classified errors; do not blindly retry every SQL error.
• Cap the number of attempts and the deadline, and track the retry rate as a metric.
• If the transaction sends an email or calls an external API before COMMIT, a retry repeats the side effect. Push side effects through an outbox or an idempotent mechanism after commit.

Operational details

• SET TRANSACTION ISOLATION LEVEL must run before the first query. You cannot read under Read Committed and then switch the same transaction to Repeatable Read to “fix” the snapshot.
• Do not open a transaction and then wait for the user to confirm in the UI: the transaction holds locks, holds an old snapshot and blocks VACUUM.
• Serializable has a cost: SSI uses predicate locks, and a Seq Scan takes a table-level one, which easily causes false positives (unnecessary aborts). Good indexes reduce aborts.
• Serializable cannot be used on a hot standby (replica). Reports on a replica use Repeatable Read.
• BEGIN ISOLATION LEVEL SERIALIZABLE READ ONLY DEFERRABLE for long reports on the primary: waits for a safe snapshot, then runs without ever being aborted with 40001.

Real-world example

Month-end report with mismatched numbers across tables

A financial report runs 5 separate queries under Read Committed. Transactions commit between the queries, so the totals from payments and ledger differ by a few units. Moving the report to BEGIN ISOLATION LEVEL REPEATABLE READ READ ONLY puts every query at the same point in time.

What to watch: a report holding a snapshot for hours blocks VACUUM from reclaiming old versions, so set statement_timeout and run it on a replica if possible. If the report also writes based on aggregates over many rows, it needs Serializable or locks/constraints.

Try it locally with the demo table:

docker compose -f examples/docker-compose.yml up -d postgres
docker compose -f examples/docker-compose.yml exec -T postgres psql -U hub -d hub < examples/src/postgres-isolation/setup.sql

Open two psql terminals on the hub database and run the timeline above:

-- A
BEGIN ISOLATION LEVEL READ COMMITTED;   -- change to REPEATABLE READ on the next try
SELECT balance FROM isolation_account WHERE id = 1;   -- 100

-- B (other terminal)
UPDATE isolation_account SET balance = 70 WHERE id = 1;

-- A
SELECT balance FROM isolation_account WHERE id = 1;   -- 70 (RC) or 100 (RR)
COMMIT;

Rerun setup.sql to reset the balance to 100 before each try. Under Repeatable Read, trying UPDATE ... SET balance = 90 after the second read gets 40001; run ROLLBACK before trying again. This example does not demonstrate write skew; see the anomalies article.

Trade-offs

Choosing isolation by requirement

• Read Committed keeps transactions short, fitting many simple operations when the SQL puts the condition in the write statement.
• Repeatable Read gives several queries one stable view, useful for reports.
• Serializable detects schedules violating serializability that snapshot isolation misses.

Costs to manage

• Repeatable Read and Serializable return 40001; the code must be safely re-runnable.
• Serializable adds tracking overhead and can raise the abort rate under high contention.
• Transactions holding snapshots for long block cleanup of old versions; isolation does not replace timeouts.

🎮 Try it

• Choose lock/isolation: when you need SERIALIZABLE + retry, and when a single UPDATE is enough.

Production checklist

1. Default to Read Committed; write atomic SQL (UPDATE ... SET x = x + 10, WHERE status = 'pending') instead of reading and then writing a constant.
2. Use constraints (UNIQUE, EXCLUDE, CHECK) and FOR UPDATE before thinking about Serializable.
3. All code running under Repeatable Read or Serializable goes through a 40001 and 40P01 retry wrapper.
4. Metrics: retry rate, aborts per endpoint, longest transaction duration.
5. Set idle_in_transaction_session_timeout and statement_timeout so hung transactions do not hold snapshots.
6. Side effects outside the database go through an outbox, not inside a retryable transaction.

Common mistakes

Common pitfall

“Repeatable Read prevents phantoms in every standard and every database.” The SQL standard allows phantoms at this level; PostgreSQL has none under Repeatable Read. That is a PostgreSQL-specific guarantee; do not carry it over to other databases.

Common pitfall

“With Serializable you don’t need retries.” The opposite: PostgreSQL protects serializability by aborting a transaction when needed. Without a retry, users just see an error.

Common pitfall

“Changing the isolation level after a SELECT is enough.” The snapshot has already been taken. Choose the level before the first query, then test with two connections and a controlled commit order.

Summary

Key takeaways

• Read Committed takes a snapshot per statement; Repeatable Read keeps one snapshot per transaction from the first real statement.
• Repeatable Read prevents non-repeatable reads, phantoms and lost updates (via 40001) but still allows write skew.
• Serializable uses SSI to block write skew too; 40001 means rerun the whole transaction.
• Default to Read Committed + atomic SQL + constraints; Repeatable Read READ ONLY for reports; Serializable only when needed, always with retry.

Self-check: (1) A only runs BEGIN, waits for B to commit, then runs its first SELECT under Repeatable Read: which point in time does A see?

(2) Why are two differing SELECT results under Read Committed not a dirty read? (3) On 40001, why is retrying only the UPDATE not enough?

References

• Transaction Isolation — PostgreSQL 17
• Serialization Failure Handling — PostgreSQL 17
• SET TRANSACTION — PostgreSQL 17