Transaction Isolation Levels

PostgreSQL supports three isolation levels: Read Committed, Repeatable Read, and Serializable. Each trades performance for correctness guarantees. Most applications never need to change the default.

Each statement within a transaction sees a snapshot of the database as of the statement's start. A second SELECT in the same transaction can see rows committed by other transactions between the two statements.

-- Session A
BEGIN;
SELECT balance FROM accounts WHERE id = 1;
-- Returns 1000

-- Session B (concurrent)
UPDATE accounts SET balance = 500 WHERE id = 1;
COMMIT;

-- Session A (same transaction, new statement)
SELECT balance FROM accounts WHERE id = 1;
-- Returns 500 — the committed change is visible
COMMIT;

Guarantees: No dirty reads (you never see uncommitted data).

Does not guarantee: Repeatable reads. The same query can return different results within a single transaction.

Repeatable Read

The transaction sees a snapshot taken at the start of the first statement (not each statement). All reads within the transaction return the same data, regardless of concurrent commits.

-- Session A
BEGIN ISOLATION LEVEL REPEATABLE READ;
SELECT balance FROM accounts WHERE id = 1;
-- Returns 1000

-- Session B (concurrent)
UPDATE accounts SET balance = 500 WHERE id = 1;
COMMIT;

-- Session A (same transaction)
SELECT balance FROM accounts WHERE id = 1;
-- Still returns 1000 — snapshot from start of transaction
COMMIT;

Guarantees: No dirty reads. No non-repeatable reads. No phantom reads (new rows inserted by other transactions are also invisible).

Caveat: If Session A tries to UPDATE a row that Session B already changed and committed, PostgreSQL will raise a serialization error:

ERROR: could not serialize access due to concurrent update

Your application must catch this error and retry the transaction.

Serializable

The strongest level. PostgreSQL guarantees that the result of concurrent transactions is the same as if they had run one after another in some serial order.

BEGIN ISOLATION LEVEL SERIALIZABLE;

-- Check total deposits
SELECT sum(balance) FROM accounts WHERE type = 'deposit';

-- Insert based on the result
INSERT INTO reports (total_deposits) VALUES (12345.00);

COMMIT;

If a concurrent transaction modified accounts in a way that would change the sum, PostgreSQL detects this and aborts one of the transactions:

ERROR: could not serialize access due to read/write dependencies among transactions
DETAIL: Reason code: Canceled on identification as a pivot, during commit attempt.
HINT: The transaction might succeed if retried.

Guarantees: Full serializability. No anomalies of any kind.

Cost: More frequent serialization failures that require retries. PostgreSQL tracks read and write dependencies using predicate locks, which adds overhead.

How PostgreSQL Implements Each Level

PostgreSQL uses MVCC (Multi-Version Concurrency Control) for all isolation levels. It does not use read locks that block writers.

Read Committed: Each statement gets a fresh snapshot. Cheap — no extra tracking.
Repeatable Read: One snapshot per transaction. Slightly more memory for long transactions, but the main cost is retry logic for serialization errors.
Serializable: Builds on Repeatable Read, adding Serializable Snapshot Isolation (SSI). Tracks predicate locks (SIRead locks) to detect read-write conflicts. More CPU and memory overhead.

When to Use Each Level

Read Committed: Most Applications

The default works for the vast majority of applications. Web apps, CRUD APIs, and typical business logic rarely need stronger isolation. If your transaction reads data and then writes based on it, use explicit locking (SELECT FOR UPDATE) instead of changing the isolation level.

-- Read Committed with explicit locking
BEGIN;
SELECT balance FROM accounts WHERE id = 1 FOR UPDATE;
-- Row is now locked — no other transaction can update it
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
COMMIT;

Repeatable Read: Consistent Reports

When you need a transaction to see a stable view of the data — generating a report that reads from multiple tables and needs consistent totals.

BEGIN ISOLATION LEVEL REPEATABLE READ;

SELECT count(*) FROM orders WHERE status = 'completed';
SELECT sum(amount) FROM orders WHERE status = 'completed';
-- Both queries see the same snapshot — totals are consistent

COMMIT;

Serializable: Financial Correctness

When your application has complex invariants that span multiple rows or tables and you cannot easily express them with explicit locks. Classic example: enforcing that total account balances across a set of accounts remain constant.

BEGIN ISOLATION LEVEL SERIALIZABLE;

-- Transfer between accounts
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;

-- A concurrent transaction doing the same won't create money out of thin air
COMMIT;

The Performance Cost

Setting up a quick comparison:

-- Read Committed: baseline cost
-- No additional tracking, no retries needed

-- Repeatable Read: ~5-10% overhead
-- One snapshot per transaction, occasional serialization errors

-- Serializable: ~10-30% overhead
-- SSI predicate lock tracking, more frequent retries

The overhead depends heavily on workload. Read-heavy workloads see minimal difference. Write-heavy workloads with conflicts see more retries at higher isolation levels.

Retry Logic Is Required

For Repeatable Read and Serializable, your application must handle serialization errors:

-- Pseudocode for retry logic
-- max_retries = 3
-- for attempt in range(max_retries):
--     try:
--         BEGIN ISOLATION LEVEL SERIALIZABLE;
--         ... do work ...
--         COMMIT;
--         break;
--     except SerializationError:
--         ROLLBACK;
--         continue;

Without retry logic, serialization errors become application errors seen by users.

Setting Isolation Levels

-- Per-transaction
BEGIN ISOLATION LEVEL REPEATABLE READ;

-- Default for the session
SET default_transaction_isolation = 'repeatable read';

-- Default for the entire database
ALTER DATABASE mydb SET default_transaction_isolation = 'serializable';

Note: PostgreSQL does not support Read Uncommitted. If you set it, PostgreSQL silently upgrades to Read Committed.

Checking the Current Level

SHOW default_transaction_isolation;
SHOW transaction_isolation;  -- within a transaction

Common Pitfalls

Using Serializable without retry logic. Serialization failures are expected, not bugs. Your application must retry.
Using Repeatable Read thinking it prevents write conflicts. It detects them and raises an error — it does not prevent them. You still need retry logic.
Changing the default isolation level globally without understanding the impact. Setting the database default to Serializable means every simple SELECT pays the SSI overhead.
Long-running transactions at Repeatable Read or Serializable. The snapshot must be maintained for the entire transaction, which can cause MVCC bloat and prevent vacuum from cleaning up old row versions.
Thinking Read Committed is unsafe. For most operations it is perfectly fine. Use explicit locking (FOR UPDATE) for read-then-write patterns instead of raising the isolation level.
Confusing PostgreSQL's Repeatable Read with the SQL standard's. PostgreSQL's implementation prevents phantom reads, which the standard only guarantees at Serializable.

Key Takeaways

Read Committed is the default and is correct for most applications.
Repeatable Read gives you a consistent snapshot for the entire transaction. Use it for reports and batch operations that need stable data.
Serializable guarantees true serializability but requires retry logic and has higher overhead.
PostgreSQL never uses read locks that block writers. All isolation levels use MVCC.
Explicit locking (SELECT FOR UPDATE) at Read Committed is often simpler and cheaper than raising the isolation level.
Any isolation level above Read Committed requires application-side retry logic for serialization errors.