PostgreSQL Transaction Isolation Levels Explained

If you've ever had a subtle data inconsistency bug in production and traced it back to "we're in a transaction, so the data should be consistent" -- you've run into a transaction isolation misunderstanding. PostgreSQL's Read Committed default is perfectly correct, but it doesn't do what most developers think it does. And switching to a higher level introduces a completely different class of problems. Let's unpack all three levels and when to actually use each one.

What PostgreSQL Actually Supports

The SQL standard defines four isolation levels: Read Uncommitted, Read Committed, Repeatable Read, and Serializable. PostgreSQL accepts all four in syntax, but Read Uncommitted behaves identically to Read Committed -- PostgreSQL's MVCC architecture never exposes uncommitted data. So in practice, you have three distinct behaviors.

Read Committed (The Default)

Each statement within a transaction sees a snapshot as of the start of that statement, not the start of the transaction. If another transaction commits between your two SELECTs, the second one sees the committed changes.

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

-- Another transaction commits: UPDATE accounts SET balance = 500 WHERE account_id = 1;

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

This is the right choice for most workloads. Non-repeatable reads and phantom reads are only a problem if your application logic depends on seeing the same data across multiple queries within a single transaction -- and most transactions execute a single query or a short sequence of independent queries.

Repeatable Read (Snapshot Isolation)

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

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

-- Another transaction commits: UPDATE accounts SET balance = 500 WHERE account_id = 1;

SELECT balance FROM accounts WHERE account_id = 1;  -- still sees 1000 (snapshot isolation)

-- But trying to update the same row fails:
UPDATE accounts SET balance = balance - 100 WHERE account_id = 1;
-- ERROR: could not serialize access due to concurrent update
ROLLBACK;  -- must retry the entire transaction

The catch: if your transaction tries to UPDATE a row that another committed transaction modified after the snapshot was taken, PostgreSQL aborts with a serialization error. Your application must catch this and retry the entire transaction.

Serializable (SSI)

The strongest level. PostgreSQL uses Serializable Snapshot Isolation (SSI) to detect read/write dependency cycles and abort transactions that would produce non-serializable results.

-- Transaction A:
BEGIN ISOLATION LEVEL SERIALIZABLE;
SELECT sum(balance) FROM accounts WHERE branch = 'east';
INSERT INTO accounts (branch, balance) VALUES ('west', 100);
COMMIT;

-- Transaction B (concurrent):
BEGIN ISOLATION LEVEL SERIALIZABLE;
SELECT sum(balance) FROM accounts WHERE branch = 'west';
INSERT INTO accounts (branch, balance) VALUES ('east', 200);
COMMIT;
-- One of these will fail with a serialization error

This catches more anomalies than Repeatable Read but has a higher abort rate. PostgreSQL tracks predicate locks (SIRead locks) which consume memory and CPU.

Detecting Isolation Problems

Check the server-wide default:

SHOW default_transaction_isolation;

Monitor for sessions holding long-running snapshots (common with Repeatable Read/Serializable):

SELECT
    pid,
    usename,
    datname,
    backend_xid,
    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;

Sessions with very old backend_xmin values are holding snapshots that prevent vacuum from cleaning dead tuples -- a major bloat risk.

Check for lock contention and deadlocks:

SELECT
    datname AS database_name,
    deadlocks,
    conflicts
FROM pg_stat_database
WHERE datname = current_database();

Implementing Retry Logic

This is non-negotiable for Repeatable Read and Serializable. The retry must re-execute the entire transaction, not just the failed statement:

import psycopg2
import time

def execute_with_retry(connection_pool, transaction_fn, max_retries=3):
    """Execute a transaction function with automatic retry on serialization failure."""
    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
            # Exponential backoff with jitter
            time.sleep(0.01 * (2 ** attempt))
        finally:
            connection_pool.putconn(conn)

Which Level When?

Read Committed (default): most web applications, CRUD operations, independent queries within a transaction. The non-repeatable read phenomenon is only a problem if your logic depends on reading the same data twice.

Repeatable Read: read-heavy reporting that needs a consistent point-in-time snapshot. Financial reports, balance calculations, audit queries. Keep these transactions short to avoid holding snapshots that prevent vacuum.

Serializable: write-heavy transactions with complex invariants where application-level locking is impractical. Double-booking prevention, inventory allocation, accounting entries with zero-sum constraints.

Set isolation per-transaction, not globally:

-- Per-transaction (recommended)
BEGIN ISOLATION LEVEL REPEATABLE READ;
-- ... queries ...
COMMIT;

-- Kill long-idle transactions
ALTER SYSTEM SET idle_in_transaction_session_timeout = '5min';
SELECT pg_reload_conf();

Prevention Strategy

Default to Read Committed. Upgrade per-transaction only when you have a documented reason.

Monitor idle in transaction sessions regardless of isolation level. At Read Committed it's wasteful; at Repeatable Read or Serializable it actively prevents vacuum from reclaiming dead tuples.

Design schemas to minimize serialization conflicts. Two transactions that always update the same counter row will always conflict under Serializable. Restructure to per-user or per-partition counters to reduce contention.

Under high concurrency at Serializable level, expect 5-20% serialization failures. If your retry rate is higher, the access patterns may need restructuring more than the isolation level needs raising.

---

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