PostgreSQL Transaction Isolation Levels Explained

A team I worked with spent two weeks debugging an intermittent bug where account balances were occasionally wrong. The application code looked correct -- it read a balance, computed a new value, and wrote it back, all within a transaction. The problem? Two concurrent transactions could both read the old balance (Read Committed gives each statement a fresh snapshot), compute their updates independently, and overwrite each other. Classic lost update, caused by an incorrect assumption about what "being in a transaction" guarantees. Understanding what each isolation level actually does -- and doesn't do -- prevents an entire class of bugs.

PostgreSQL's Three Isolation Levels

The SQL standard defines four levels, but PostgreSQL's MVCC architecture means Read Uncommitted behaves identically to Read Committed (dirty reads never happen). You have three real choices.

Read Committed (The Default)

Each SQL statement sees a snapshot of the database as of the start of that statement -- not the transaction. Between statements, committed changes from other transactions become visible.

BEGIN;
SELECT balance FROM accounts WHERE account_id = 1;  -- sees 1000

-- Concurrent transaction commits: UPDATE accounts SET balance = 500 ...

SELECT balance FROM accounts WHERE account_id = 1;  -- sees 500 (non-repeatable read)
COMMIT;

This is the right default for most workloads. Non-repeatable reads are only a problem when your logic depends on seeing the same data across multiple queries within a single transaction.

Repeatable Read (Snapshot Isolation)

The transaction sees a frozen snapshot from its first non-transaction-control statement. All queries within the transaction see the same consistent data, regardless of concurrent commits.

BEGIN ISOLATION LEVEL REPEATABLE READ;
SELECT balance FROM accounts WHERE account_id = 1;  -- sees 1000

-- Concurrent transaction commits the same row change

SELECT balance FROM accounts WHERE account_id = 1;  -- still sees 1000

UPDATE accounts SET balance = balance - 100 WHERE account_id = 1;
-- ERROR: could not serialize access due to concurrent update
ROLLBACK;

The trade-off: if your transaction tries to update a row modified by another committed transaction after your snapshot, PostgreSQL aborts with a serialization error. Your application must retry the entire transaction.

Serializable (SSI)

The strongest guarantee. PostgreSQL tracks read/write dependencies using predicate locks (SIRead locks) and aborts transactions when it detects a dependency cycle that would produce non-serializable results.

-- Transaction A reads east, writes west
-- Transaction B reads west, writes east (concurrently)
-- PostgreSQL detects the read/write cycle and aborts one

This catches anomalies that Repeatable Read misses, but the abort rate is higher. Under high concurrency, 5-20% of transactions may need retrying. Predicate lock tracking also consumes memory and CPU.

Detecting Isolation Issues

Check the current default:

SHOW default_transaction_isolation;

Find sessions holding old snapshots (which block vacuum):

SELECT
    pid,
    usename,
    datname,
    backend_xmin,
    state,
    substring(query, 1, 80) AS query_preview
FROM pg_stat_activity
WHERE backend_type = 'client backend'
    AND backend_xmin IS NOT NULL
ORDER BY age(backend_xmin) DESC;

A very old backend_xmin relative to other sessions means that session is holding a long-running snapshot. At Repeatable Read or Serializable, this prevents vacuum from reclaiming dead tuples, causing table bloat.

Find blocking transactions:

SELECT
    blocked_locks.pid AS blocked_pid,
    blocked_activity.usename AS blocked_user,
    blocking_locks.pid AS blocking_pid,
    blocking_activity.usename AS blocking_user,
    blocked_activity.query AS blocked_query,
    blocking_activity.query AS blocking_query
FROM pg_catalog.pg_locks blocked_locks
JOIN pg_catalog.pg_stat_activity blocked_activity
    ON blocked_activity.pid = blocked_locks.pid
JOIN pg_catalog.pg_locks blocking_locks
    ON blocking_locks.locktype = blocked_locks.locktype
    AND blocking_locks.database IS NOT DISTINCT FROM blocked_locks.database
    AND blocking_locks.relation IS NOT DISTINCT FROM blocked_locks.relation
    AND blocking_locks.pid != blocked_locks.pid
JOIN pg_catalog.pg_stat_activity blocking_activity
    ON blocking_activity.pid = blocking_locks.pid
WHERE NOT blocked_locks.granted;

Retry Logic Is Non-Negotiable

For Repeatable Read and Serializable, you must implement application-level retry with exponential backoff. The retry must re-execute the entire transaction -- PostgreSQL rolls back everything on a serialization failure.

import psycopg2
import time

def execute_with_retry(connection_pool, transaction_fn, max_retries=3):
    for attempt in range(max_retries):
        conn = connection_pool.getconn()
        try:
            conn.set_isolation_level(
                psycopg2.extensions.ISOLATION_LEVEL_SERIALIZABLE
            )
            result = transaction_fn(conn)
            conn.commit()
            return result
        except psycopg2.errors.SerializationFailure:
            conn.rollback()
            if attempt == max_retries - 1:
                raise
            time.sleep(0.01 * (2 ** attempt))
        finally:
            connection_pool.putconn(conn)

Choosing the Right Level

Set isolation per-transaction:

BEGIN ISOLATION LEVEL REPEATABLE READ;
-- ... queries ...
COMMIT;

And always protect against stale transactions:

ALTER SYSTEM SET idle_in_transaction_session_timeout = '5min';
SELECT pg_reload_conf();

Prevention

Default to Read Committed unless you have a specific, documented reason for a higher level. Different operations in the same application may need different levels -- that's normal and expected.

Monitor idle in transaction sessions. At any isolation level, transactions that stay open while waiting for external input waste resources. At Repeatable Read or Serializable, they actively cause bloat.

Design schemas to minimize serialization conflicts. If two transactions always update the same counter row, they will always conflict under Serializable. Use per-user or per-partition counters instead.

---

Originally published at mydba.dev/blog/postgres-transaction-isolation-levels