PostgreSQL Transaction Isolation Levels Explained

2026-04-17

PostgreSQL supports three transaction isolation levels: Read Committed (the default), Repeatable Read, and Serializable. Each level controls what data a transaction can see when other transactions modify rows concurrently, trading off between consistency guarantees and the possibility of serialization failures that require retry logic.

The Problem

Transaction isolation is one of the most misunderstood aspects of PostgreSQL. The SQL standard defines four levels (Read Uncommitted, Read Committed, Repeatable Read, Serializable), but PostgreSQL's MVCC architecture means the actual behavior differs from what developers expect based on textbook descriptions or experience with other databases.

The default level — Read Committed — is the right choice for most workloads, but it allows phenomena that surprise developers who have not thought about concurrent access. A transaction reading the same row twice can see different values if another transaction committed a change in between (non-repeatable read). A query with a WHERE clause can see rows that were inserted by another transaction after the first query started (phantom read). These are not bugs — they are the defined behavior of Read Committed — but they cause real application bugs when developers assume "I'm in a transaction, so my view of the data is stable."

Switching to Repeatable Read or Serializable solves these consistency problems but introduces a different class of errors: serialization failures. When PostgreSQL detects that two concurrent transactions would produce different results depending on execution order, it aborts one with ERROR: could not serialize access due to concurrent update (Repeatable Read) or ERROR: could not serialize access due to read/write dependencies (Serializable). Applications must catch these errors and retry the transaction — but most application code does not have retry logic, and adding it after the fact is painful.

The performance implications are real too. Serializable isolation requires PostgreSQL to track predicate locks (SIRead locks), which consume memory and CPU. Under high concurrency, the retry rate can be significant — 5-20% of transactions may need to be retried, effectively reducing throughput. Choosing the wrong isolation level can either compromise data consistency or tank performance, and the symptoms do not appear until you have concurrent load.

How to Detect It

Check the current default isolation level and any transaction-level overrides:

-- Check the server-wide default isolation level
SHOW default_transaction_isolation;

-- Check for sessions using non-default isolation levels
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;

The backend_xmin column shows the oldest transaction ID that a session's snapshot can see. Sessions with a very old backend_xmin relative to other sessions are holding long-running snapshots — common with Repeatable Read or Serializable transactions that stay open too long. These long snapshots prevent vacuum from cleaning dead tuples visible to the snapshot, causing table bloat.

Monitor serialization failures if you are using Repeatable Read or Serializable:

-- Check for serialization failure errors in pg_stat_statements
-- (requires pg_stat_statements extension)
SELECT
    substring(query, 1, 100) AS query_preview,
    calls,
    round(mean_exec_time::numeric, 1) AS avg_ms,
    rows
FROM pg_stat_statements
WHERE query ILIKE '%serializ%'
    OR query ILIKE '%could not serialize%'
ORDER BY calls DESC;

-- Check for lock waits and deadlocks that may indicate isolation conflicts
SELECT
    datname AS database_name,
    deadlocks,
    conflicts
FROM pg_stat_database
WHERE datname = current_database();

Identify transactions that are holding locks and blocking others:

-- Find blocking and blocked transactions (lock contention from isolation behavior)
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,
    blocked_locks.mode AS blocked_lock_mode
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.page IS NOT DISTINCT FROM blocked_locks.page
    AND blocking_locks.tuple IS NOT DISTINCT FROM blocked_locks.tuple
    AND blocking_locks.transactionid IS NOT DISTINCT FROM blocked_locks.transactionid
    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;

How myDBA.dev Shows It

myDBA.dev's Locks page provides real-time visibility into lock contention, including the lock chains that result from isolation-level conflicts. When a Serializable transaction holds predicate locks that block other transactions, the lock chain visualization shows exactly which sessions are waiting on which locks, and for how long.

Locks page showing lock chain visualization with blocking and waiting transactions

The Wait Events analysis shows serialization-related wait events (SIReadLock, transactionid, tuple) correlated with query performance. When serialization failures spike, you can see the corresponding increase in wait events and identify the specific queries and tables involved. This context is critical for deciding whether to keep Serializable isolation and optimize the conflicting access patterns, or step down to Repeatable Read or Read Committed.

Wait Events showing serialization-related waits and their correlation with transaction throughput

The Activity Monitor shows idle in transaction sessions that may be holding snapshots longer than necessary — a common performance problem with Repeatable Read and Serializable isolation levels where developers open a transaction, perform application logic, and delay the COMMIT.

How to Fix It

Understand what each level actually does in PostgreSQL:

Read Committed (the default): Each statement within a transaction sees a snapshot of the database as of the start of that statement, not the start of the transaction. If another transaction commits between your two SELECT statements, the second SELECT sees the committed changes. This is the most permissive level and produces the fewest conflicts.

-- Read Committed example: each statement gets a fresh snapshot
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;

Repeatable Read: The transaction sees a snapshot as of the start of the transaction (specifically, the first non-transaction-control statement). All queries within the transaction see the same consistent snapshot, regardless of concurrent commits. However, if the transaction tries to UPDATE a row that was modified by another committed transaction after the snapshot was taken, it fails with a serialization error.

-- Repeatable Read example: snapshot is frozen at transaction start
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

Serializable: The strongest level. PostgreSQL uses Serializable Snapshot Isolation (SSI) to detect read/write dependencies between concurrent transactions and abort one if the result would differ from serial execution. This catches more anomalies than Repeatable Read but has a higher abort rate.

-- Serializable example: catches read/write dependency cycles
-- Transaction A:
BEGIN ISOLATION LEVEL SERIALIZABLE;
SELECT sum(balance) FROM accounts WHERE branch = 'east';  -- reads east accounts
INSERT INTO accounts (branch, balance) VALUES ('west', 100);  -- writes to west
COMMIT;

-- Transaction B (concurrent):
BEGIN ISOLATION LEVEL SERIALIZABLE;
SELECT sum(balance) FROM accounts WHERE branch = 'west';  -- reads west accounts
INSERT INTO accounts (branch, balance) VALUES ('east', 200);  -- writes to east
COMMIT;
-- One of these will fail: could not serialize access due to read/write dependencies

Set the isolation level at the transaction level (preferred) or server-wide:

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

-- Or within a running transaction
BEGIN;
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
-- ... your queries ...
COMMIT;

-- Server-wide default (rarely needed — keep Read Committed as default)
ALTER SYSTEM SET default_transaction_isolation = 'read committed';
SELECT pg_reload_conf();

Implement retry logic for Repeatable Read and Serializable transactions:

# Python retry pattern for serialization failures
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)

The retry must re-execute the entire transaction from the beginning, not just the failed statement. PostgreSQL rolls back the whole transaction on a serialization failure — there is no way to resume from the point of failure.

How to Prevent It

Default to Read Committed unless you have a specific, documented reason to use a higher level. Most web application workloads run correctly at Read Committed. The non-repeatable read and phantom read phenomena it allows are only a problem if your application logic depends on seeing the same data across multiple queries within a single transaction — and most application transactions execute a single query or a short sequence of independent queries.

Use Repeatable Read for read-heavy reporting transactions that need a consistent point-in-time snapshot. Financial reports, balance calculations, and audit queries benefit from Repeatable Read because every query within the transaction sees the same data. Keep these transactions short to avoid holding old snapshots that prevent vacuum.

Reserve Serializable for write-heavy transactions with complex invariants — situations where application-level locking is impractical and you need PostgreSQL to enforce that concurrent transactions produce the same result as serial execution. Classic examples include double-booking prevention, inventory allocation, and accounting entries that must maintain a zero-sum constraint.

Monitor idle in transaction sessions regardless of isolation level. A transaction that stays open while waiting for user input or external API calls holds its snapshot for the entire duration. At Read Committed this is wasteful; at Repeatable Read or Serializable it actively prevents vacuum from reclaiming dead tuples, causing bloat. Set idle_in_transaction_session_timeout to kill these sessions automatically:

-- Kill transactions idle for more than 5 minutes
ALTER SYSTEM SET idle_in_transaction_session_timeout = '5min';
SELECT pg_reload_conf();

Design your schema to minimize serialization conflicts. Transactions that touch the same rows are more likely to conflict. If two transactions always update the same counter row, they will conflict under Serializable isolation. Restructure the schema to use per-user or per-partition counters, reducing contention and serialization failure rates.

FAQ

What are the 4 transaction isolation levels in PostgreSQL?

The SQL standard defines four 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 (dirty reads), so the distinction is meaningless. In practice, PostgreSQL has three distinct behaviors: Read Committed, Repeatable Read (snapshot isolation), and Serializable (serializable snapshot isolation with predicate locking).

What is the default transaction isolation level in PostgreSQL?

Read Committed. This means each SQL statement within a transaction sees a snapshot of the database as of the start of that statement, not the start of the transaction. Other transactions' committed changes become visible between statements. This is the most permissive level and the right default for most workloads. You can check your current default with SHOW default_transaction_isolation;.

Where do I set the transaction isolation level?

You can set it at three scopes: (1) Per-transaction: BEGIN ISOLATION LEVEL SERIALIZABLE; or SET TRANSACTION ISOLATION LEVEL SERIALIZABLE; within a transaction block. (2) Per-session: SET SESSION CHARACTERISTICS AS TRANSACTION ISOLATION LEVEL REPEATABLE READ;. (3) Server-wide: ALTER SYSTEM SET default_transaction_isolation = 'serializable'; followed by SELECT pg_reload_conf();. Per-transaction is almost always the right approach — different operations in the same application may need different levels.

What is the most restrictive isolation level in PostgreSQL?

Serializable is the most restrictive level. It uses Serializable Snapshot Isolation (SSI) to guarantee that any set of concurrent serializable transactions produces the same result as if they ran one at a time in some serial order. PostgreSQL tracks read/write dependencies using predicate locks (SIRead locks) and aborts transactions when it detects a dependency cycle that could produce non-serializable results. The cost is higher memory usage for predicate lock tracking and a higher rate of serialization failures that require application-level retry logic.