Transaction Isolation Levels: READ UNCOMMITTED to SERIALIZABLE

Every database connection that runs concurrent queries shares the same data. Transaction isolation levels control how concurrent transactions interact. Choosing the right level is a trade-off between correctness and performance.

Introduction

Every concurrent database system has to decide how transactions interact. Do you see changes from other transactions immediately? Can two transactions modify the same row without corrupting data? Transaction isolation levels define the answer to these questions — and choosing the wrong level is how you get mysterious vanished updates, phantom reads, and data that contradicts itself despite constraints.

The SQL standard defines four isolation levels from READ UNCOMMITTED to SERIALIZABLE. Most databases default to READ COMMITTED or REPEATABLE READ, but you can change the level per transaction. This guide explains what each level actually guarantees, which anomalies it prevents, and the performance cost of tightening the guarantees.

The Four Standard Isolation Levels

The SQL standard defines four isolation levels, from least strict to most strict:

READ UNCOMMITTED

The lowest isolation level. A transaction can see uncommitted changes from other transactions.

PostgreSQL doesn’t actually implement READ UNCOMMITTED. If you set it, you get READ COMMITTED behavior instead. This is per the SQL standard — implementations are allowed to implement a level higher than specified.

-- Transaction 1
BEGIN;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;

-- Transaction 2 (in another session)
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
SELECT balance FROM accounts WHERE id = 1;
-- Might see: 900 (uncommitted value)

-- Transaction 1
ROLLBACK;  -- Balance is back to 1000

READ COMMITTED

Each query sees only data committed before that query started. This is PostgreSQL’s default.

-- Transaction 1
BEGIN;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;

-- Transaction 2 (in another session)
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
SELECT balance FROM accounts WHERE id = 1;
-- Sees: 1000 (waits for Transaction 1 to commit or rollback)

SELECT balance FROM accounts WHERE id = 1;
-- Sees: 900 (after Transaction 1 commits)

REPEATABLE READ

The transaction sees a snapshot as of the first query in the transaction. Reads are consistent within the transaction regardless of when they occur.

-- Transaction 2
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
SELECT balance FROM accounts WHERE id = 1;
-- Sees: 1000

-- Transaction 1 (in another session)
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
COMMIT;

SELECT balance FROM accounts WHERE id = 1;
-- Still sees: 1000 (snapshot from transaction start)
-- Even though the value in the database is now 900

PostgreSQL implements REPEATABLE READ using MVCC (Multi-Version Concurrency Control). Each transaction sees a consistent snapshot of the database.

SERIALIZABLE

The highest isolation level. Transactions appear to run sequentially, even if they run concurrently. Serializable is the only level that guarantees no anomalies.

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;

PostgreSQL implements SERIALIZABLE using a form of MVCC plus serialization conflict detection. If two concurrent transactions try to modify the same data, one will be rolled back with a serialization error.

-- Transaction 1
BEGIN;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;

-- Transaction 2 (concurrent, also SERIALIZABLE)
BEGIN;
UPDATE accounts SET balance = balance + 100 WHERE id = 1;
COMMIT;

-- Transaction 1 tries to COMMIT
-- ERROR: could not serialize access due to concurrent update

How MVCC Snapshot Behavior Changes Per Level

sequenceDiagram
    participant T1 as Transaction 1
    participant DB as PostgreSQL (MVCC)
    participant T2 as Transaction 2
    participant T3 as Transaction 3

    Note over T1,DB: T1: READ COMMITTED
    T1->>DB: SELECT balance (snapshot S1)
    DB-->>T1: balance = 1000

    T2->>DB: BEGIN (snapshot S2 created)
    DB-->>T2: ok
    T2->>DB: UPDATE balance = 900
    T2->>DB: COMMIT

    T3->>DB: BEGIN (snapshot S3 created)
    DB-->>T3: ok
    T3->>DB: SELECT balance (snapshot S3 = T2 committed)
    DB-->>T3: balance = 900

    T1->>DB: SELECT balance (new snapshot S1')
    DB-->>T1: balance = 900 (sees T2's commit!)

sequenceDiagram
    participant T1 as Transaction 1
    participant DB as PostgreSQL (MVCC)
    participant T2 as Transaction 2

    Note over T1,DB: T1: REPEATABLE READ
    T1->>DB: BEGIN (snapshot S1 frozen)
    DB-->>T1: ok
    T1->>DB: SELECT balance (snapshot S1)
    DB-->>T1: balance = 1000

    T2->>DB: BEGIN
    DB-->>T2: ok
    T2->>DB: UPDATE balance = 900
    T2->>DB: COMMIT

    T1->>DB: SELECT balance (still snapshot S1)
    DB-->>T1: balance = 1000 (T2's change invisible!)

    Note over T1,DB: Snapshot stays frozen for entire transaction

With READ COMMITTED, each statement gets a fresh snapshot. With REPEATABLE READ (or SERIALIZABLE), the snapshot is taken at transaction start and held for the duration. This is why the same query returns different results at different isolation levels.

Read vs Write Anomalies

Isolation levels prevent specific types of anomalies.

Dirty Read

Reading uncommitted data from another transaction. This is prevented by READ COMMITTED and above.

Non-Repeatable Read

The same row is read twice within a transaction, but returns different values because another transaction modified and committed it.

-- Transaction A
SELECT balance FROM accounts WHERE id = 1;
-- Returns: 1000

-- Transaction B
UPDATE accounts SET balance = 900 WHERE id = 1;
COMMIT;

-- Transaction A
SELECT balance FROM accounts WHERE id = 1;
-- Returns: 900 (different!)

Prevented by REPEATABLE READ and SERIALIZABLE.

Phantom Read

A transaction re-executes a query returning rows that satisfy a search condition, but receives additional rows due to another transaction inserting.

-- Transaction A
SELECT COUNT(*) FROM orders WHERE status = 'pending';
-- Returns: 50

-- Transaction B
INSERT INTO orders (status, ...) VALUES ('pending', ...);
COMMIT;

-- Transaction A
SELECT COUNT(*) FROM orders WHERE status = 'pending';
-- Returns: 51

Prevented by SERIALIZABLE.

Lost Update

Two transactions read and update the same row, and one update overwrites the other.

-- Transaction A
SELECT balance FROM accounts WHERE id = 1;
-- balance = 1000

-- Transaction B
SELECT balance FROM accounts WHERE id = 1;
-- balance = 1000

-- Transaction A
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
-- balance = 900

-- Transaction B
UPDATE accounts SET balance = balance + 100 WHERE id = 1;
-- balance = 1100, but Transaction A's update is lost!

Prevented by SERIALIZABLE in PostgreSQL.

SET TRANSACTION Syntax

Setting Isolation Level

-- At transaction start
BEGIN ISOLATION LEVEL SERIALIZABLE;
-- or
START TRANSACTION ISOLATION LEVEL REPEATABLE READ;

-- Within a transaction (must be first statement)
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;

Setting Other Transaction Characteristics

BEGIN;
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
SET TRANSACTION READ ONLY;  -- or READ WRITE
SET TRANSACTION DEFERRABLE;  -- waits for lock to avoid serialization failures
COMMIT;

DEFERRABLE is useful for long-running read-only transactions that you want to run without getting serialization errors.

Default Isolation Levels by Database

PostgreSQL’s READ COMMITTED

PostgreSQL’s default is READ COMMITTED. Each statement sees data committed before that statement runs, which means statements within a transaction can see different data, and long-running transactions are more prone to interference.

MySQL’s REPEATABLE READ

MySQL (with InnoDB) defaults to REPEATABLE READ. The transaction sees a consistent snapshot from the first query, other transactions’ uncommitted changes are invisible, and phantom reads can still occur (though InnoDB’s gap locking reduces this in practice).

Practical Implications

When to Use SERIALIZABLE

Use it when correctness is critical and you can tolerate some performance reduction. Financial transactions, inventory updates, and booking systems often need SERIALIZABLE to prevent lost updates.

BEGIN;
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;

-- Critical section
SELECT balance FROM accounts WHERE id = 1 FOR UPDATE;
-- Check business rules
UPDATE accounts SET balance = balance - 100 WHERE id = 1;

COMMIT;

The FOR UPDATE lock ensures exclusive access. The serialization level ensures the balance didn’t change between your SELECT and UPDATE.

When to Use READ COMMITTED

READ COMMITTED is fine for most OLTP workloads. It provides a good balance between consistency and performance.

Be aware of what it doesn’t prevent: non-repeatable reads and phantoms within a transaction. If you need consistent reads across multiple queries, you’ll need a higher isolation level or explicit locking.

When to Avoid SERIALIZABLE

Serializable has real costs. It can cause more transaction rollbacks under high concurrency, and serialization conflict detection adds overhead.

If your application has enough concurrency that serialization errors become frequent, you might be better off with REPEATABLE READ plus explicit locking, or handling conflicts in application code with retries.

Isolation Level Trade-offs

Capacity Estimation: MVCC Version Bloat

MVCC keeps multiple versions of rows to support concurrent reads without blocking writers. Each UPDATE creates a new row version (tuple) while the old one stays until vacuum removes it. Under READ COMMITTED with long-running transactions, this accumulation is measurable.

Storage overhead per UPDATE: PostgreSQL writes a new tuple version approximately equal to the row size. For a 500-byte row updated 10 times, that is roughly 5 KB of dead tuple storage before VACUUM runs. On a table with 100 million rows and 10% updated daily, you accumulate around 50 GB of dead tuples before autovacuum can catch up.

The practical consequence is bloat and degraded index scan performance. Each index entry pointing to dead tuple versions adds I/O overhead to queries. If autovacuum falls behind due to a long-running READ COMMITTED transaction holding back vacuum, dead tuples pile up faster than they are cleaned. Tables with high UPDATE rates in READ COMMITTED mode need aggressive autovacuum tuning — specifically, lower autovacuum_vacuum_cost_delay and higher autovacuum_vacuum_scale_factor for tables with large row widths.

Visibility and bloat observability in pg_stat_activity: Long-running transactions prevent autovacuum from reclaiming dead tuples. You can detect this by querying pg_stat_activity for transactions that have been idle in transaction for an abnormally long time:

SELECT pid, usename, state, query_start, backend_xmin
FROM pg_stat_activity
WHERE state != 'active'
  AND query_start < NOW() - INTERVAL '10 minutes'
  AND backend_xmin IS NOT NULL;

The backend_xmin field shows the oldest transaction snapshot the backend is holding. Autovacuum cannot remove tuples killed by transactions older than backend_xmin. If you see backend_xmin values that are stale, those are your bloat blockers. Killing the blocking process or waiting for it to finish allows autovacuum to resume cleanup.

Under REPEATABLE READ and SERIALIZABLE, the snapshot is held from transaction start, so a long-running transaction at these levels holds references to all tuple versions created during its execution. If your reporting queries run at REPEATABLE READ and take 2 hours, all rows those queries touch accumulate dead versions for 2 hours before they become visible to autovacuum. Plan for this when setting up autovacuum thresholds on tables queried by long-running analytical transactions.

Common Production Failures

Serialization errors spiking under load: You deploy SERIALIZABLE on a high-concurrency write path. Suddenly 5% of transactions start failing with serialization errors, rolling back work and filling your error logs. The fix is to either switch to REPEATABLE READ with explicit FOR UPDATE locks, or add retry logic in your application for serialization failures.

Long-running READ COMMITTED transactions seeing stale data: A reporting query runs inside a transaction that takes 30 minutes. With READ COMMITTED, each statement sees only data committed when that statement ran — not when the transaction started. If nightly batch jobs commit while your report is running, different parts of your report will reflect different points in time.

REPEATABLE READ not actually preventing phantoms in PostgreSQL: PostgreSQL’s REPEATABLE READ uses MVCC but does not prevent phantom reads for INSERT operations — it only prevents non-repeatable reads of existing rows. The standard says REPEATABLE READ should prevent phantoms, but PostgreSQL diverges here. If you need true phantom prevention, use SERIALIZABLE.

Implicit assumption of default isolation: Most developers never set isolation level and rely on the database default. In PostgreSQL this is READ COMMITTED, which means two queries in the same transaction can see different data. If your logic assumes consistent reads across statements within a transaction, you need REPEATABLE READ or SERIALIZABLE explicitly.

Lost updates at READ COMMITTED: At READ COMMITTED, two concurrent transactions can read the same balance, compute new values, and write — losing each other’s updates. This is not prevented by READ COMMITTED. Use SELECT ... FOR UPDATE or SERIALIZABLE to prevent lost updates.

Quick Recap Checklist

• READ COMMITTED: snapshot per statement, sees committed data before each statement
• REPEATABLE READ: snapshot at transaction start, consistent reads within txn
• SERIALIZABLE: snapshot at transaction start, detects write-write conflicts
• PostgreSQL does not implement READ UNCOMMITTED — defaults to READ COMMITTED
• Non-repeatable read: same row read twice within a transaction returns different values
• Phantom read: re-running a query returns additional rows from concurrent inserts
• Lost update: two transactions read, compute, and write — one overwrites the other
• SERIALIZABLE prevents lost updates but causes serialization failures under high contention
• Long-running READ COMMITTED transactions can see different data in different statements
• backend_xmin in pg_stat_activity shows oldest held snapshot — blocks autovacuum
• Use FOR UPDATE or SERIALIZABLE to prevent lost updates at READ COMMITTED

Interview Questions

Further Reading

• PostgreSQL MVCC documentation — how PostgreSQL implements concurrency with multi-version tuples
• Transaction Isolation documentation — official reference for isolation levels
• PostgreSQL Serializable Snapshot Isolation — how SERIALIZABLE detects and prevents serialization anomalies
• Autovacuum tuning guide — configuring vacuum to prevent bloat from long-running transactions
• pg_stat_activity and backend_xmin — monitoring active queries and snapshot holders

Conclusion

Transaction isolation levels trade correctness against performance. READ COMMITTED is fine for most applications. REPEATABLE READ gives you consistent reads within a transaction. SERIALIZABLE prevents all anomalies but at a performance cost. PostgreSQL’s MVCC implementation makes these levels efficient, but you should still choose deliberately rather than accepting defaults without understanding the implications.

For more on concurrent data access, see Locking and Concurrency and Relational Databases.