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.

The Four Standard Isolation Levels

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

Level	Dirty Reads	Non-Repeatable Reads	Phantom Reads
READ UNCOMMITTED	Possible	Possible	Possible
READ COMMITTED	Prevented	Possible	Possible
REPEATABLE READ	Prevented	Prevented	Possible
SERIALIZABLE	Prevented	Prevented	Prevented

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

Database	Default Isolation
PostgreSQL	READ COMMITTED
MySQL (InnoDB)	REPEATABLE READ
Oracle	READ COMMITTED
SQL Server	READ COMMITTED
SQLite	SERIALIZABLE

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

Isolation Level	Latency impact	Throughput	Consistency guarantee	Best for
READ COMMITTED	Lowest	Highest	Sees only committed data per statement	Most OLTP, high-concurrency workloads
REPEATABLE READ	Moderate	Moderate	Same row values within a transaction	Reporting, consistent financial reads
SERIALIZABLE	Highest	Lowest	No anomalies possible	Financial transfers, inventory, booking
READ UNCOMMITTED	(Not implemented in PostgreSQL)	—	—	—

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.

Interview Questions

Q: A reporting query runs inside a transaction and takes 45 minutes. During those 45 minutes, a batch job updates 5% of the rows in the table. When the report finishes, it shows inconsistent results across different sections. What is happening?

With READ COMMITTED (PostgreSQL’s default), each statement in your transaction sees only data committed when that statement ran — not when the transaction began. So the first query in your report sees data as of 9:00 AM, the second sees data as of 9:15 AM when the batch job committed, and so on. Different parts of the same report reflect different points in time. This is sometimes called a “temporal anomaly” and is not prevented by READ COMMITTED. Fix it by running the report at REPEATABLE READ or SERIALIZABLE, or by taking a consistent snapshot before the report starts.

Q: You enable REPEATABLE READ to prevent non-repeatable reads, but users still complain about phantom inserts appearing in their results. Shouldn’t REPEATABLE READ prevent that?

PostgreSQL’s REPEATABLE READ prevents non-repeatable reads of existing rows — it freezes a snapshot of committed data at transaction start. But it does not prevent new rows from being inserted by other transactions. Those new rows are invisible to your REPEATABLE READ transaction because your snapshot does not include them, but when you re-run the same query, you get different row counts. This is a phantom read, and PostgreSQL’s REPEATABLE READ allows it (unlike the SQL standard which requires REPEATABLE READ to prevent phantoms). If you need true phantom prevention, use SERIALIZABLE.

Q: A developer says “we use SERIALIZABLE so we never have to worry about lost updates.” How do you respond?

Serializable prevents lost updates by detecting write-write conflicts and rolling back one transaction, but it only works if all writes go through SERIALIZABLE transactions. If any part of your write path uses READ COMMITTED or REPEATABLE READ, the guarantee is broken. More importantly, serialization failures increase under contention — if you have 100 concurrent transactions all trying to update the same balance, most will fail and retry. At that point, SERIALIZABLE plus retry logic is often slower than REPEATABLE READ with explicit SELECT ... FOR UPDATE locking, because FOR UPDATE gives you control over which transaction waits and which proceeds.

Q: You see backend_xmin is NOT NULL in a query against pg_stat_activity and you are investigating bloat on a heavily-written table. What does that tell you?

backend_xmin is the transaction ID of the snapshot being held by a backend. When it is NOT NULL, that backend is holding a transaction snapshot that prevents autovacuum from removing dead tuple versions. The older the backend_xmin, the longer the snapshot has been held and the more dead tuples have accumulated behind it. Look for backends in pg_stat_activity that are in an idle state (state != ‘active’) but have been running for a long time — these are often the culprit. Long-running analytical queries at REPEATABLE READ are a common source.

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.