Transaction Isolation Levels in PostgreSQL

← Back to the section

When several transactions work with the database at the same time, they can interfere with each other. The isolation level determines how strictly a transaction is "fenced off" from other transactions' changes. Let's start from scratch: what problems can arise at all, which level solves what, and how to avoid non-obvious errors.

What can go wrong with concurrent transactions

Imagine two users changing the same data at the same time. Without isolation, the classic anomalies appear:

Dirty read — one transaction sees uncommitted data from another. If that other transaction rolls back, the first one read "thin air". In PostgreSQL this is impossible by design — MVCC always protects against dirty reads.

Non-repeatable read — the same row is read twice within a transaction, but the values differ: between the reads someone managed to change it and commit.

Phantom read — a query with the same condition returns a different number of rows within a transaction: between the calls someone inserted or deleted rows.

Write skew (a write anomaly) — the subtlest case. Each transaction individually reads data, checks an invariant, and makes a write. Everything looks correct. But together they violate the very invariant they checked. The classic example is "at least one doctor on shift" (we'll look at it below).

Lost update

The most common anomaly in application code is the lost update: two transactions read one value, each computes the new one in the application and writes the result; the second write overwrites the first.

-- both transactions at once, READ COMMITTED
SELECT balance FROM account WHERE id = 1;      -- both see 100
UPDATE account SET balance = 70 WHERE id = 1;  -- both write 100 - 30

The result is 70 instead of 40, and the database reports nothing: from its point of view both transactions are correct. The same mechanics without a database — two threads read one value and write what they computed, and next to it the same code under a row lock.

live example

import java.util.concurrent.CyclicBarrier;

public class LostUpdate {
    static int plain = 100;
    static int locked = 100;
    static final CyclicBarrier bothRead = new CyclicBarrier(2);
    static final Object row = new Object();

    public static void main(String[] args) throws InterruptedException {
        twice(() -> {
            int seen = plain;
            waitForBoth();
            plain = seen - 30;
        });
        twice(() -> {
            synchronized (row) {
                int seen = locked;
                locked = seen - 30;
            }
        });
        System.out.println("read and written by the application: " + plain);
        System.out.println("read under a row lock:              " + locked);
    }

    static void twice(Runnable body) throws InterruptedException {
        Thread first = new Thread(body);
        Thread second = new Thread(body);
        first.start();
        second.start();
        first.join();
        second.join();
    }

    static void waitForBoth() {
        try {
            bothRead.await();
        } catch (Exception e) {
            throw new IllegalStateException(e);
        }
    }
}

The first run prints 70: both threads managed to read 100 before anyone wrote. The second prints 40 — under synchronized the second thread waits for the first and subtracts from 70, exactly like under SELECT … FOR UPDATE. There are three cures. Compute in the database rather than in the application: UPDATE account SET balance = balance - 30 WHERE id = 1 AND balance >= 30 — the second UPDATE waits for the first and already sees 70. Lock the row up front: SELECT … FOR UPDATE — the second transaction waits. Or raise the level to REPEATABLE READ: the second transaction gets error 40001 and repeats its work from the start.

Three levels in PostgreSQL

PostgreSQL supports three real levels. Formally, the SQL standard also has READ UNCOMMITTED, but in PostgreSQL it behaves the same as READ COMMITTED — dirty reads are simply not implemented.

Important: PostgreSQL implements REPEATABLE READ through snapshot isolation, which is stricter than the SQL standard: phantom reads are excluded too.

READ COMMITTED — the default level

Each SELECT in a transaction sees the data committed at the moment that specific query started. Not at the moment the transaction began, but at the moment of the query.

-- TX1 began a transaction
BEGIN;
SELECT price FROM product WHERE id = 1;   -- 100

-- meanwhile TX2 changed the price and committed
-- UPDATE product SET price = 120 WHERE id = 1; COMMIT;

SELECT price FROM product WHERE id = 1;   -- 120! (non-repeatable read)
COMMIT;

This sounds scary, but in most CRUD operations a row is read once — the problem doesn't arise.

When SELECT FOR UPDATE is mandatory under RC: if the "read → check → write" logic must be atomic, FOR UPDATE locks the row until the end of the transaction — the same cure for a lost update as above.

REPEATABLE READ — a snapshot of the data

At REPEATABLE READ, PostgreSQL takes a snapshot of the data at the moment of the first query in the transaction. All subsequent reads in the same transaction see this snapshot — as if the data were "frozen".

BEGIN ISOLATION LEVEL REPEATABLE READ;

SELECT count(*) FROM orders WHERE status = 'NEW';   -- 100

-- another transaction inserted 5 new orders and committed

SELECT count(*) FROM orders WHERE status = 'NEW';   -- still 100
COMMIT;

When you need it:

• a long report or a reassembly of data over several tables, where the consistency of the slice matters;
• pg_dump uses exactly this level.

Error 40001 under REPEATABLE READ

Here comes the catch. If TX1 reads a row and TX2 manages to change it and commit — and then TX1 tries to change the same row, PostgreSQL can't "merge" the changes. It rolls back TX1 with an error:

ERROR: could not serialize access due to concurrent update
SQLSTATE: 40001

Without retry logic, REPEATABLE READ cannot be used in production.

SERIALIZABLE — full isolation

SERIALIZABLE guarantees that the result of concurrent transactions will be the same as if they had executed strictly one at a time. PostgreSQL uses the SSI algorithm (Serializable Snapshot Isolation) — it tracks dependencies between transactions through predicate locks.

A write skew example

Invariant: there must always be at least one doctor on shift. Two are on duty.

-- TX1 (REPEATABLE READ): checks the number of doctors on duty
SELECT count(*) FROM doctors WHERE on_call = true;   -- 2
-- "ok, I can leave, 1 will remain"
UPDATE doctors SET on_call = false WHERE id = 1;

-- TX2 (REPEATABLE READ) does the same thing concurrently:
SELECT count(*) FROM doctors WHERE on_call = true;   -- also 2
UPDATE doctors SET on_call = false WHERE id = 2;
COMMIT;

-- TX1 commits — the invariant is violated: 0 doctors on shift

REPEATABLE READ doesn't help: each transaction saw a correct snapshot and wrote to different rows. Only SERIALIZABLE will catch such a conflict — one of the transactions will get 40001 and roll back.

The price to pay: predicate locks create load, and the rollback rate grows under load. For most OLTP applications SERIALIZABLE is overkill. It's often cheaper to stay on RC and replace a complex invariant with SELECT FOR UPDATE plus an explicit check in the code, or with a CHECK constraint.

How to set the level in code

The isolation level is set at the transaction level, not at the connection level. READ COMMITTED is the PostgreSQL default and is not spelled out explicitly.

Java / Spring:

@Transactional(isolation = Isolation.SERIALIZABLE)
public void releaseDoctorFromShift(long doctorId) {
    int onCallCount = doctorRepository.countByOnCallTrue();
    if (onCallCount <= 1) {
        throw new LastDoctorOnShiftException();
    }
    doctorRepository.setOnCallFalse(doctorId);
}

Go (pgx):

opts := pgx.TxOptions{IsoLevel: pgx.Serializable}
err := pgx.BeginTxFunc(ctx, pool, opts, func(tx pgx.Tx) error {
    // transaction logic
    return nil
})

Node.js (pg):

await client.query('BEGIN ISOLATION LEVEL SERIALIZABLE');
// queries
await client.query('COMMIT');

Python (psycopg3):

async with pool.connection() as conn:
    await conn.set_isolation_level(psycopg.IsolationLevel.SERIALIZABLE)
    async with conn.transaction():
        # transaction logic
        pass

Retry on error 40001

Under REPEATABLE READ and SERIALIZABLE, the application must retry the transaction on error 40001.

Java (spring-retry):

@Retryable(
    retryFor = ConcurrencyFailureException.class,
    maxAttempts = 3,
    backoff = @Backoff(delay = 50, multiplier = 2)
)
@Transactional(isolation = Isolation.SERIALIZABLE)
public void doWork() { ... }

Usually 3 attempts with an increasing pause are enough. Without a framework it is the same loop by hand: in Go you unwrap the error with errors.As down to *pgconn.PgError and compare pgErr.Code with "40001" — comparing the message text is not an option, it depends on the server locale.

Two conditions without which a retry does not work. Repeat the whole transaction from outside: after 40001 the current one is already marked for rollback, and new queries inside it fail. And catch ConcurrencyFailureException — the common ancestor of all concurrency errors in the DataAccessException hierarchy; the narrow CannotSerializeTransactionException is deprecated since Spring Framework 6.0.3, and the translator returns a different class now.

How to choose a level

A practical algorithm:

1. Simple CRUD or read-modify-write of a single row → READ COMMITTED + SELECT FOR UPDATE where atomicity is needed.
2. A long report over several tables with a consistent slice → REPEATABLE READ.
3. A complex invariant over several rows that can't be expressed via FOR UPDATE → SERIALIZABLE + retry.
4. Financial operations → READ COMMITTED + SELECT FOR UPDATE with ordered locks when the rows are known in advance (the account debited, the account credited). But when the rule is checked over a result set and the dangerous row does not exist yet ("no more than five active orders"), there is nothing to lock — then SERIALIZABLE.

When in doubt — stay on READ COMMITTED. Raising the isolation level without understanding the specific anomaly is extra load with no safety guarantee.

A timeout for stuck transactions

An open transaction holds resources and interferes with autovacuum. In production you always configure:

idle_in_transaction_session_timeout = 30000   -- 30 seconds

The server terminates the session whose open transaction has been idle longer than 30 seconds: the connection is closed and the transaction rolls back. A long unclosed transaction almost always means a bug in the code — it's better to abort it than to wait.

In short

• There are three working levels: READ COMMITTED, REPEATABLE READ, SERIALIZABLE; READ UNCOMMITTED behaves like the first. MVCC excludes dirty reads at all of them.
• READ COMMITTED is the default and the norm for OLTP: every query sees fresh committed data, and for a single read of a row that is enough.
• REPEATABLE READ fixes a snapshot at the first query of the transaction; phantoms are excluded. Needed for consistent reports and long operations.
• SERIALIZABLE is the only level that protects against write skew. Rarely needed, and it costs predicate locks and rollbacks.
• Error 40001 is a normal situation at both upper levels: the application must repeat the whole transaction from outside.
• A lost update is cured not by the level but by computing inside the UPDATE or by SELECT … FOR UPDATE; idle_in_transaction_session_timeout = 30s is mandatory in production.

What to read next

• Locks in PostgreSQL — SELECT FOR UPDATE and other kinds of locks.
• Spring @Transactional — how the isolation level is set through Spring.
• Connection pool — routing read-only transactions to a replica.
• ACID and isolation levels — where isolation comes from: MVCC, WAL and the rest of the ACID letters.