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).

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.

Level	Dirty read	Non-repeatable read	Phantoms	Write skew
READ COMMITTED (default)	no	yes	yes	yes
REPEATABLE READ	no	no	no	yes
SERIALIZABLE	no	no	no	no

Important: PostgreSQL implements REPEATABLE READ through snapshot isolation, which is stricter than the SQL standard — phantom reads are excluded too, even though the standard doesn't require it.

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. RC is enough for the vast majority of queries.

When RC is enough:

• ordinary CRUD;
• API endpoints where a row is read once;
• "read — modify — write" operations on a single row with SELECT FOR UPDATE.

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. Without it, two concurrent transactions can read the same value and both write over it.

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 over several tables where the consistency of the slice matters;
• pg_dump uses exactly this level;
• complex reassembly of data from several tables in one transaction.

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

-- TX1 (REPEATABLE READ): read price = 100
-- TX2 (READ COMMITTED): changed price to 120, committed
-- TX1 tries to change price to 110 — gets 40001

The application must catch this error and retry the transaction. 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.

When SERIALIZABLE is justified:

• complex invariants over several rows that can't be expressed via a CHECK constraint or SELECT FOR UPDATE;
• financial calculations with multiple conditions.

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.

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

READ COMMITTED is the PostgreSQL default; there's no need to specify it explicitly.

Retry on error 40001

Under REPEATABLE READ and SERIALIZABLE, the application must retry the transaction on error 40001. Without this, the system is unreliable.

Java (spring-retry):

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

Go:

for attempt := 0; attempt < 3; attempt++ {
    err := pgx.BeginTxFunc(ctx, pool, opts, func(tx pgx.Tx) error {
        // logic
        return nil
    })
    if err == nil {
        return nil
    }
    if pgErr, ok := err.(*pgconn.PgError); ok && pgErr.Code == "40001" {
        time.Sleep(time.Duration(50*(attempt+1)) * time.Millisecond)
        continue
    }
    return err
}

The strategy is simple: caught 40001 → waited a bit → tried again. Usually 3 attempts with an increasing pause are enough.

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 (usually cheaper and more reliable than 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

This value kills a transaction that does nothing for longer than 30 seconds. A long unclosed transaction almost always means a bug in the code — it's better to abort it than to wait.

Common mistakes

Setting SERIALIZABLE on all operations — pointless. Most operations are fine with READ COMMITTED, while SERIALIZABLE adds overhead. Choose the minimum sufficient level.

Using REPEATABLE READ without retry — a transaction can end with 40001 at any moment. Without handling this error, the user sees an unexplained failure.

Raising the isolation level where a CHECK constraint is what's needed — if an invariant can be expressed at the schema level, do it that way. The database will enforce it itself without the overhead of predicate locks.

Explicitly specifying READ COMMITTED in code — it's the default; there's no point duplicating it.

In short

• PostgreSQL supports three working levels: READ COMMITTED, REPEATABLE READ, SERIALIZABLE. READ UNCOMMITTED behaves like RC.
• MVCC in PostgreSQL excludes dirty reads at all levels — this is a guarantee of the database itself.
• READ COMMITTED is the default and the norm for OLTP. Non-repeatable and phantom reads are not a problem in most cases.
• REPEATABLE READ fixes a snapshot of the data at the start 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, requires retry.
• Error 40001 under REPEATABLE READ and SERIALIZABLE is a normal situation. The application must retry the transaction.
• idle_in_transaction_session_timeout = 30s in production — mandatory.
• Unsure about the level — stay on READ COMMITTED.

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.