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

A dirty read happens when Transaction A reads a row that Transaction B modified but has not yet committed. If Transaction B later rolls back, Transaction A has already acted on data that never existed. This is the most dangerous anomaly because it leads to decisions based on data that gets discarded.

Picture a funds transfer: Transaction B debits an account, Transaction A reads the new balance to decide whether to approve a loan. If Transaction B rolls back due to a constraint violation, Transaction A has already made a credit decision against a balance that was never real. The database has let Transaction A see and react to uncommitted state.

Not all databases allow this. PostgreSQL does not implement READ UNCOMMITTED and always behaves as READ COMMITTED. Oracle also skips dirty reads. SQL Server allows them in rare edge cases, but most developers never encounter dirty reads because the default isolation levels in all major engines prevent them.

Dirty reads are prevented by READ COMMITTED and all higher isolation levels. If you need dirty reads intentionally, say to see partial results of a long-running batch job, you would need to lower the isolation level or use explicit read-uncommitted query hints where the database supports them.

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

You can set the isolation level at transaction start or immediately after beginning a transaction. The catch is that SET TRANSACTION ISOLATION LEVEL must be the first statement in the transaction — it fails if any query has already run. You cannot change the isolation level mid-transaction without rolling back and restarting.

-- 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;

BEGIN ... ISOLATION LEVEL and SET TRANSACTION ISOLATION LEVEL as a separate statement after BEGIN are functionally equivalent. BEGIN ISOLATION LEVEL is more compact for new transactions. SET TRANSACTION is useful when you want to set multiple characteristics at once — isolation level, read/write mode, and deferrable status in one block.

Setting Other Transaction Characteristics

Beyond isolation level, PostgreSQL lets you control two other transaction characteristics: access mode (READ ONLY or READ WRITE) and deferrability (DEFERRABLE or NOT DEFERRABLE). These must also come before any query executes in the transaction.

READ ONLY prevents any write operation — no INSERT, UPDATE, DELETE, or creation of temporary tables. The database can apply optimizations when it knows a transaction will not modify data, and some replication setups route read-only transactions to replicas. In PostgreSQL the performance benefit is small, but it prevents accidental writes in reporting queries.

READ WRITE is the default and allows normal read-write operations. There is rarely a reason to set this explicitly unless you want to override a connection-level default.

DEFERRABLE is the more nuanced option. A deferrable transaction waits for locks instead of failing with a serialization error. It only works with SERIALIZABLE isolation. The trade-off is that it may take longer because it yields to concurrent writers rather than rolling back. Use it for long-running batch jobs and reports where you prioritize completion over speed.

BEGIN;
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
SET TRANSACTION READ ONLY;
SET TRANSACTION DEFERRABLE;
COMMIT;

This combination works well for overnight reports. It holds a consistent snapshot from transaction start, avoids serialization errors, and waits for conflicting writers to finish. Without DEFERRABLE, the same transaction would fail if concurrent writes generated too many serialization conflicts.

Default Isolation Levels by Database

PostgreSQL’s READ COMMITTED

PostgreSQL uses READ COMMITTED as its default and does not let you lower this to READ UNCOMMITTED. Every query within a transaction sees only data committed before that query started, not before the transaction began. This means two SELECT statements in the same transaction can return different results if another transaction commits between them.

This has practical consequences for any logic that spans multiple statements. Consider a transfer: you SELECT the balance, check it is above zero, then UPDATE to subtract the amount. At READ COMMITTED, another transaction could commit a withdrawal between your SELECT and your UPDATE — your balance check passes on stale data, and you could overdraw. Use SELECT ... FOR UPDATE to lock the row, or raise the isolation level.

Long-running transactions take the biggest hit here. If your transaction runs for 30 minutes with multiple statements, each sees whatever has been committed by the time it runs. Different stages see different states of the database. This makes READ COMMITTED a poor fit for reporting or analytics workflows that expect a consistent snapshot across statements.

MySQL’s REPEATABLE READ

MySQL with InnoDB defaults to REPEATABLE READ, unlike PostgreSQL which uses READ COMMITTED. REPEATABLE READ freezes a consistent snapshot at the first query in the transaction — all subsequent reads within the same transaction return the same data, even if other transactions have committed changes in the meantime. This eliminates the cross-statement inconsistency that READ COMMITTED allows.

InnoDB implements this through MVCC with an undo log. When a row is modified, the previous version is copied to the undo log. A transaction reading old versions reads from the undo log rather than the current row, using the transaction ID to determine visibility.

Where InnoDB differs from the SQL standard is phantom prevention. The standard requires REPEATABLE READ to prevent phantoms — new rows appearing on re-execution of a range query. InnoDB’s REPEATABLE READ uses next-key locking, which reduces phantoms in practice but does not fully eliminate them. A transaction inserting a row outside the locked range can still create a phantom. Only SERIALIZABLE, which locks the entire range, truly prevents phantoms in InnoDB.

The practical difference for developers coming from PostgreSQL: REPEATABLE READ in MySQL prevents non-repeatable reads within a transaction by default, while PostgreSQL’s READ COMMITTED does not. MySQL transactions feel more consistent out of the box, but range queries still do not have complete isolation without SERIALIZABLE.

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 fits most standard OLTP workloads: web applications, APIs, and microservices where each request runs one transaction with a small number of statements. User authentication, session updates, order placement with a single INSERT, and inventory deduction that fits in one UPDATE all fall here.

The performance edge is the main reason to pick it. READ COMMITTED produces the lowest lock contention because writers only block other writers on the exact same row. Readers do not block writers and writers do not block readers. For a high-concurrency application handling thousands of short transactions per second, this adds up. You scale write throughput close to what the hardware can sustain, without the serialization failure overhead that SERIALIZABLE introduces under contention.

The catch: multi-statement transactions are not consistent across their own statements. If your application logic reads a value, computes something, then writes based on that value in the same transaction, READ COMMITTED can return stale data between statements. Use SELECT ... FOR UPDATE to lock the row before the compute step, or raise the isolation level. Single-statement CRUD operations are never a problem — the statement is atomic by itself. The risk only appears when you chain statements with inter-statement dependencies.

-- Typical web app pattern: safe at READ COMMITTED
BEGIN;
INSERT INTO orders (user_id, total) VALUES ($1, $2);
UPDATE inventory SET stock = stock - 1 WHERE product_id = $3;
COMMIT;

-- Dangerous at READ COMMITTED: inter-statement dependency
BEGIN;
SELECT balance FROM accounts WHERE id = $1;  -- T1 sees committed data
-- Another transaction commits a withdrawal here
UPDATE accounts SET balance = balance - $2 WHERE id = $1;  -- T1 acts on stale data
COMMIT;

For the dangerous pattern, use SELECT ... FOR UPDATE or switch to REPEATABLE READ.

When to Avoid SERIALIZABLE

Avoid SERIALIZABLE when your write path runs at high concurrency against the same rows. Serialization conflict detection kicks in whenever two transactions read the same row version and both try to write an update. Under moderate contention, say 20 or 30 concurrent transactions hitting the same balance row, failure rates climb until most transactions fail and retry repeatedly. The retries add latency and CPU overhead that outweighs explicit locking.

A practical threshold: if your hot row receives more than roughly 50 to 100 concurrent write transactions per second, SERIALIZABLE spends more resources on conflict detection and retry logic than REPEATABLE READ with SELECT ... FOR UPDATE. The locking approach gives you direct control over which transaction waits and which proceeds, and it does not roll back work. It queues the second writer behind the first.

The rollback cost can be lopsided too. If your transaction does significant work before the conflict is detected, say it reads 50 rows, computes a result, then writes, a serialization failure wastes all of that computation. A SELECT ... FOR UPDATE lock would have caused the second transaction to wait instead, preserving the work. For transactions doing non-trivial computation, explicit locking is more efficient than the rollback-and-retry model that SERIALIZABLE uses.

Correctness-first workloads are the exception. Financial transfers, seat reservations, and inventory decrements where the business logic is simple work well with SERIALIZABLE because the transactions are short and the cost of a retry is low. Once a SERIALIZABLE transaction spans multiple statements and complex computation, a serialization failure becomes expensive. Switch to REPEATABLE READ with explicit row locks instead.

-- Retry loop pattern for SERIALIZABLE failures
let attempt = 0;
while (attempt < MAX_RETRIES) {
    try {
        runTransactionSerializable();
        break;
    } catch (SerializationFailure) {
        attempt++;
        sleep(attempt * BASE_DELAY_MS);  -- exponential backoff
    }
}

If your error logs show serialization failures above 1 to 2 percent of transactions, evaluate REPEATABLE READ plus FOR UPDATE as an alternative. Profile where the conflicts are happening, lock those rows explicitly, and handle the queueing behavior in your application rather than paying the rollback cost.

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.