Postgres Transaction Isolation: The Anomalies Your App Actually Faces in Production

The balance sheet was off by exactly twelve thousand dollars. No rounding error. A clear gap between accounts payable and the general ledger that appeared only during the monthly reconciliation. The team had reviewed every transaction insert path. Unit tests passed. SQL was clean. The bug lived between transactions, in the gap where two concurrent sessions read the same rows and made decisions based on stale snapshots.

Postgres ships with READ COMMITTED as the default isolation level. That default is fine for most queries and catastrophically wrong for some critical operations. The problem is not that developers choose the wrong level. The problem is they do not know what anomalies can escape each level, or how Postgres implements them differently from MySQL, SQL Server, or Oracle. This post walks through the four anomalies that break real applications (dirty reads, lost updates, phantom reads, write skew), shows the exact SQL that reproduces each one, and gives a decision table for picking the right isolation level without overpaying in lock contention.

The Postgres isolation level map

Postgres implements three of the four ANSI SQL isolation levels. It does not support the ANSI READ UNCOMMITTED level because its implementation of MVCC (Multi-Version Concurrency Control) makes dirty reads impossible even at the lowest setting. If you issue SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED in Postgres, you silently get READ COMMITTED instead.

The levels Postgres actually provides are:

Level	Dirty reads	Non-repeatable reads	Phantom reads	Serialization anomalies
Read Committed	No	Yes	Yes	Yes
Repeatable Read	No	No	Mostly	Yes
Serializable	No	No	No	No

This table is technically accurate for Postgres but dangerously incomplete. “Non-repeatable read” and “phantom read” are labels, not precise specifications of what your application will observe. The anomalies that matter in production are not named in the standard at all: write skew, and the behavior of FOR UPDATE under Repeatable Read. Let us work through each anomaly with concrete SQL.

Dirty reads: the anomaly that does not exist in Postgres

A dirty read is when transaction A reads a row that transaction B has written but not yet committed. If B rolls back, A has read data that never existed.

In MySQL with the InnoDB storage engine, READ UNCOMMITTED genuinely allows dirty reads. In Postgres, dirty reads are impossible at every level because MVCC keeps multiple row versions and a transaction never sees a version written by a transaction that started after it, or one that is still in flight. Every query sees a consistent snapshot of committed data as of the query’s start time.

You can verify this yourself. Open two psql sessions.

Session A:

BEGIN;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
-- do not commit yet

Session B:

SELECT * FROM accounts WHERE id = 1;

Session B will see the old balance, even under the default READ COMMITTED. The row version from the uncommitted update is invisible. Session B blocks only if it tries to acquire a lock on that row (for example, with SELECT ... FOR UPDATE). A plain SELECT never blocks and never dirties.

This is a foundational guarantee you get for free in Postgres, and it means the default level is stronger than the equivalent default in some other databases.

Lost updates: when two writers read, decide, and overwrite

A lost update occurs when two transactions read the same value, modify it, and commit, and one of the modifications silently disappears.

Consider a counter. Two sessions both read the current value, increment by one, and write it back.

Session A:

BEGIN;
SELECT counter FROM hits WHERE page = '/home';
-- reads 42
UPDATE hits SET counter = 43 WHERE page = '/home';
COMMIT;

Session B, interleaved:

BEGIN;
SELECT counter FROM hits WHERE page = '/home';
-- also reads 42, before A commits
UPDATE hits SET counter = 43 WHERE page = '/home';
COMMIT;

The final value is 43. The correct value should be 44. One of the updates was lost.

Under READ COMMITTED, Postgres does not prevent this. Both transactions see 42 because each statement in READ COMMITTED sees a snapshot as of the statement start, and both SELECT statements start before the other’s UPDATE commits. This is not a dirty read or a non-repeatable read in the ANSI sense. It is a lost update, and it is one of the most common causes of silent accounting bugs.

The fix is to use SELECT ... FOR UPDATE in the default level:

BEGIN;
SELECT counter FROM hits WHERE page = '/home' FOR UPDATE;
-- reads 42 and acquires a row lock
UPDATE hits SET counter = 43 WHERE page = '/home';
COMMIT;

Now session B’s SELECT ... FOR UPDATE blocks until A commits. B then reads 43, increments to 44, and the lost update is prevented.

Under REPEATABLE READ, the same two UPDATE statements without FOR UPDATE behave differently. Postgres detects that the row version touched by B was updated between B’s snapshot start and its own UPDATE. B gets a 40001 serialization failure and must retry. This is a key property of Postgres: REPEATABLE READ prevents lost update anomalies by failing the second writer. You do not need explicit locking in your application code, but you do need a retry loop at the framework level.

Non-repeatable reads: the default behavior you probably rely on

A non-repeatable read is when a transaction reads a row, another transaction commits a change to that row, and the first transaction reads the same row again and sees a different value.

Session A:

BEGIN;
SELECT balance FROM accounts WHERE id = 1;
-- reads 1000

Session B (committed):

UPDATE accounts SET balance = 900 WHERE id = 1;
COMMIT;

Session A:

SELECT balance FROM accounts WHERE id = 1;
-- reads 900 under READ COMMITTED
COMMIT;

Under READ COMMITTED, this is expected behavior. Each statement sees the latest committed snapshot as of that statement’s start. Session A’s second SELECT sees B’s committed 900.

This is not necessarily a bug. In many reporting queries, you want fresher data for the second page of a paginated list. In financial transfers, it can be fatal if you read a balance, perform a business-logic check, and make a transfer decision based on the first read while a second read inside the same transaction would have shown an overdraft.

The defense in READ COMMITTED is to use SELECT ... FOR SHARE or SELECT ... FOR UPDATE on the rows you intend to check and modify. If you only read, REPEATABLE READ guarantees the same values for every SELECT in the transaction.

Phantom reads: more subtle than the textbooks say

A phantom read is when a transaction reads a set of rows that match a predicate, another transaction inserts or deletes rows matching that predicate, and the first transaction re-runs the same query and gets a different set.

The classic example:

Session A:

BEGIN ISOLATION LEVEL REPEATABLE READ;
SELECT * FROM accounts WHERE balance > 1000;
-- returns rows {1, 2}

Session B:

INSERT INTO accounts (id, balance) VALUES (3, 1500);
COMMIT;

Session A:

SELECT * FROM accounts WHERE balance > 1000;
-- under REPEATABLE READ, still returns {1, 2}
COMMIT;

In Postgres, REPEATABLE READ already prevents phantom reads for plain SELECT because the entire transaction runs against a single snapshot taken at the first statement. Session A does not see account 3.

But here is the edge case that breaks lessons learned from other databases. In SQL Server, REPEATABLE READ only locks the rows you read, not the range, so phantoms are possible. In MySQL (InnoDB), REPEATABLE READ uses gap locking and prevents phantoms. Postgres uses snapshot isolation, not locking, so the row set is frozen for the transaction. The result is correct, but the mechanism is completely different.

However, REPEATABLE READ in Postgres does not prevent all phantoms when you write. Under snapshot isolation, if A’s first query reads {1, 2} and its second statement is an UPDATE on every row with balance > 1000, and B inserted account 3 before A’s UPDATE, A’s UPDATE will affect only rows visible in A’s snapshot. Row 3 is not updated. The SELECT phantom is prevented, but the write-side effect is “frozen” too. This consistent behavior is usually what you want, but it surprises developers who expect the UPDATE to see the new row.

Write skew: the anomaly that survives Repeatable Read

Write skew is the most dangerous anomaly because it defeats REPEATABLE READ and produces correct-looking but logically inconsistent data. It is also the anomaly that drives teams to SERIALIZABLE.

Imagine a hospital scheduling system with a rule: at least one of the two doctors on call must remain on call at all times.

CREATE TABLE doctors (
  name TEXT PRIMARY KEY,
  on_call BOOLEAN NOT NULL
);

INSERT INTO doctors (name, on_call) VALUES ('Alice', true), ('Bob', true);

Alice and Bob both try to go off call at the same time, each checking that another doctor is still on call.

Session A (Alice’s request):

BEGIN ISOLATION LEVEL REPEATABLE READ;
SELECT * FROM doctors WHERE on_call = true AND name != 'Alice';
-- sees Bob, the check passes
UPDATE doctors SET on_call = false WHERE name = 'Alice';
COMMIT;

Session B (Bob’s request):

BEGIN ISOLATION LEVEL REPEATABLE READ;
SELECT * FROM doctors WHERE on_call = true AND name != 'Bob';
-- sees Alice, the check passes
UPDATE doctors SET on_call = false WHERE name = 'Bob';
COMMIT;

Both transactions commit successfully. Neither transaction saw the other’s uncommitted update because both ran against their own consistent snapshot. The result: both Alice and Bob are off call. The invariant is violated.

Postgres’s REPEATABLE READ does not prevent write skew because each transaction’s predicate (WHERE on_call = true AND name != 'X') was satisfied in the snapshot it read, and the write sets do not overlap. Since A writes only Alice and B writes only Bob, there is no row-level write conflict for Postgres to detect.

This is the classic write skew scenario. It is also the exact case where SELECT ... FOR UPDATE fails to help, because locking the row you update (Alice for A, Bob for B) does not lock the range checked by the predicate. You would need to lock every doctor on call, which is awkward and prone to deadlocks.

The Serializable fix

Under SERIALIZABLE, Postgres uses a mechanism called Serializable Snapshot Isolation (SSI). It tracks dependencies between transactions and retroactively aborts one of them when a cycle would otherwise create a serializability violation.

Run the same two sessions at SERIALIZABLE:

BEGIN ISOLATION LEVEL SERIALIZABLE;
SELECT * FROM doctors WHERE on_call = true AND name != 'Alice';
UPDATE doctors SET on_call = false WHERE name = 'Alice';
COMMIT;

and

BEGIN ISOLATION LEVEL SERIALIZABLE;
SELECT * FROM doctors WHERE on_call = true AND name != 'Bob';
UPDATE doctors SET on_call = false WHERE name = 'Bob';
COMMIT;

One of the two COMMIT statements will fail with:

ERROR:  could not serialize access due to read/write dependencies among transactions
HINT:  The transaction might succeed if retried.

The application must catch 40001 errors and retry. The invariant is preserved because Postgres detected the dependency cycle that would have led to an inconsistent outcome. This is not a row lock conflict. It is a dependency conflict, and it is exactly the class of bug that row locking and snapshot isolation alone cannot prevent.

A practical decision table

Situation	Level	Notes
Simple read-only reporting	READ COMMITTED	Fresh data per statement is usually fine
Read-then-update same row	READ COMMITTED + FOR UPDATE	Explicit pessimistic locking
Complex read where consistency across SELECTs matters	REPEATABLE READ	Guarantees identical row values on re-read
Bulk operations where you need all-or-nothing visibility	REPEATABLE READ	Prevents interleaved visible changes
Multi-row predicate checks followed by writes	SERIALIZABLE	The only way to prevent write skew
Any level that can raise 40001	All except READ COMMITTED	Application must implement retry logic

The retry loop your framework probably does not give you

If you use REPEATABLE READ or SERIALIZABLE, you must handle serialization failures. Here is a dead-simple Node.js retry wrapper:

import { Pool, PoolClient } from 'pg';

const pool = new Pool({ connectionString: process.env.DATABASE_URL });

async function runSerializable<T>(
  fn: (client: PoolClient) => Promise<T>,
  maxRetries = 10
): Promise<T> {
  for (let attempt = 0; attempt < maxRetries; attempt++) {
    const client = await pool.connect();
    try {
      await client.query('BEGIN ISOLATION LEVEL SERIALIZABLE');
      const result = await fn(client);
      await client.query('COMMIT');
      return result;
    } catch (err: any) {
      await client.query('ROLLBACK').catch(() => {});
      if (err.code === '40001') {
        const delay = Math.min(1000, 50 * Math.pow(2, attempt));
        await new Promise(r => setTimeout(r, delay));
        continue;
      }
      throw err;
    } finally {
      client.release();
    }
  }
  throw new Error('Max serialization retries exceeded');
}

// Usage
await runSerializable(async (client) => {
  const { rows } = await client.query(
    'SELECT * FROM doctors WHERE on_call = true AND name != $1',
    ['Alice']
  );
  if (rows.length === 0) throw new Error('No doctor on call');
  await client.query(
    'UPDATE doctors SET on_call = false WHERE name = $1',
    ['Alice']
  );
});

Do not skip the retry loop. SERIALIZABLE is a pessimistic tool: it preserves correctness by aborting transactions and forcing retries. If your application code does not catch 40001, the database layer throws an unhandled exception and the user sees a 500 error. A transaction framework that does not retry is not fully using the level you asked for.

Performance reality check

The common fear is that SERIALIZABLE cripples throughput. It is not free, but the cost is often less than developers assume for two reasons.

First, Postgres SSI is optimistic and aborts only when a conflict is detected, not at query time. Conflicts are rare in many workloads. A reporting dashboard that reads aggregates and never writes will not generate aborts at all, and SERIALIZABLE performs identically to REPEATABLE READ.

Second, explicit row locking (FOR UPDATE, FOR SHARE) is often more expensive in practice because it serializes readers and writers on the exact lock manager queues. SERIALIZABLE avoids many of those explicit locks by allowing reads to proceed without blocking and aborting only at commit when necessary.

Monitor pg_stat_database.conflicts to see your serialization abort rate in real time. If it is above 1-2% of transaction throughput, the workload is too contentious for SERIALIZABLE without restructuring code (for example, by centralizing the contested update through a single queue worker). Below that threshold, the retry overhead is usually negligible compared to the safety gain.

Summary

Postgres does not have dirty reads. READ COMMITTED handles most queries well but allows lost updates, which you prevent with FOR UPDATE or by moving to REPEATABLE READ and accepting retry logic. REPEATABLE READ prevents non-repeatable reads and phantoms for queries, but it does not prevent write skew. Write skew silently corrupts multi-row invariants and is the single best reason to consider SERIALIZABLE.

The isolation level you need is not an architectural decree. It is a per-transaction choice. Use READ COMMITTED for the bulk of your reads, bump to REPEATABLE READ when cross-statement consistency matters, and reserve SERIALIZABLE for the transactions that enforce invariants across multiple rows and predicates. The key is knowing which anomaly can break your invariant, and choosing the level that actually stops it.

A note from Yojji

Database transaction isolation is the kind of topic most teams learn reactively, after a reconciliation or a race-condition bug. Building systems that get it right the first time, where the retry loops and isolation levels are chosen deliberately rather than inherited from defaults, is the difference between a codebase you trust and one you babysit. Yojji’s engineering teams design transactional boundaries alongside schema design, not as an afterthought, when building backend systems for clients across Europe, the US, and the UK.

Yojji is an international custom software development company founded in 2016, specializing in the JavaScript ecosystem (React, Node.js, TypeScript), cloud platforms (AWS, Azure, Google Cloud), and scalable distributed systems architecture.