Transaction Isolation Levels: READ UNCOMMITTED to SERIALIZABLE

Introduction

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.

Consider two shoppers buying the last item in stock. If both requests read stock as one before either writes, the application needs a transaction strategy that prevents both orders from claiming it. This article explains how the four standard isolation levels handle concurrent changes, where PostgreSQL and MySQL differ, and how to choose between snapshots, locks, and retries.

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 with MVCC plus serialization conflict detection. It tracks read/write dependencies and aborts a transaction when the concurrent result cannot be explained by a serial order. Applications must be prepared to retry the full transaction after a serialization failure.

-- 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 permits dirty reads when a transaction uses READ UNCOMMITTED or a NOLOCK hint. Many applications avoid them by using a stricter isolation level, but the behavior depends on the configured level.

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.

-- Both transactions read 1000 and compute their new values in the application.
-- Transaction A
UPDATE accounts SET balance = 900 WHERE id = 1;

-- Transaction B, using its stale calculation
UPDATE accounts SET balance = 1100 WHERE id = 1;
-- Transaction A's update is overwritten.

A lost update can occur when both transactions calculate new values from the same earlier read and write those stale values back. In PostgreSQL, use SELECT ... FOR UPDATE, an atomic conditional update, or SERIALIZABLE with retry handling.

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. DEFERRABLE applies to a SERIALIZABLE READ ONLY transaction. PostgreSQL may delay the transaction before its first query until it can obtain a snapshot known to be safe from serialization anomalies. After that wait, the transaction can read without risking a serialization failure. This is useful for reports or backups that need a consistent view and can tolerate startup delay.

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

This combination can work for reports that need a consistent snapshot. The transaction may wait before its first query for a safe snapshot; DEFERRABLE does not make arbitrary read-write transactions wait for locks or guarantee faster completion.

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.

InnoDB’s ordinary consistent reads at REPEATABLE READ use the same snapshot, so repeated plain SELECTs do not reveal newly inserted rows. Locking reads and writes use record or next-key locks over the scanned index range to control concurrent changes. The exact protection depends on the query and index; do not assume that a plain consistent read and a locking read see the same state.

The practical difference for developers is snapshot scope: InnoDB REPEATABLE READ gives ordinary reads one snapshot per transaction, while PostgreSQL READ COMMITTED gives each statement a fresh snapshot. InnoDB’s locking reads and writes can also block inserts into scanned ranges with next-key locks; plain consistent reads use the transaction snapshot.

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 row lock prevents another transaction from changing this account between the read and update. SERIALIZABLE also checks whether the transaction can be ordered consistently with concurrent work; if it cannot, PostgreSQL aborts it and the application must retry the full transaction.

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 one reason to pick it. In PostgreSQL, ordinary MVCC reads do not block writers, and writers generally contend on rows they both modify. READ COMMITTED avoids keeping one transaction-wide snapshot across statements. 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 tracks read/write dependencies, including cases where concurrent reads and writes across different rows would produce a result that cannot be explained by any serial order. Under contention, serialization failures can increase and retries add latency and database work. Measure the failure rate and end-to-end latency for your workload before choosing SERIALIZABLE or explicit row locks.

There is no universal transactions-per-second threshold at which one approach becomes cheaper. A row lock makes conflicting writers wait on that row; SERIALIZABLE may abort work when concurrent dependencies cannot be ordered safely. Benchmark both approaches with your transaction shape and contention pattern.

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 serialization retries become a material source of latency or failed requests, inspect the conflicting transactions and compare SERIALIZABLE with narrower locking or atomic updates. Keep retries bounded and retry the whole transaction from fresh reads.

Isolation Level Trade-offs

Security and Compliance Notes

Isolation protects consistency, not authorization. Database roles and application checks must still decide who can act. For balances, inventory, or entitlements, keep the check and update in one transaction, using a conditional write or SELECT ... FOR UPDATE; otherwise another transaction can change the value between those steps. On a SERIALIZABLE failure, retry the whole transaction from fresh reads. Make external effects such as payments or notifications idempotent, or record them in an outbox in the same transaction.

An audit trail should identify the user or service, the request or transaction, the action, and its result. Write audit records consistently with the data change, protect them from unauthorized edits, and avoid storing credentials or raw query values that may contain personal data. Set access and retention rules for the data and jurisdiction; isolation levels alone do not satisfy compliance obligations.

Capacity Estimation: MVCC Version Bloat

MVCC keeps row versions so readers and writers can often proceed without blocking each other. Each UPDATE creates a new tuple version; vacuum can remove obsolete versions once no active snapshot needs them. A long-running statement or a transaction using a transaction-wide snapshot can delay that cleanup.

As a rough estimate, a 500-byte row updated 10 times can leave about 5 KB of obsolete tuple data before vacuum reclaims it. If 10 million rows of that size each receive 10 updates, that is about 50 GB of tuple data, before accounting for indexes, alignment, and TOAST storage.

The practical consequence is bloat and degraded index scan performance. Each index entry pointing to dead tuple versions adds I/O overhead to queries. Long-running snapshots can hold back cleanup; a READ COMMITTED transaction does not retain one snapshot across all its statements, though an active long-running statement can hold its statement snapshot. For tables that need more frequent vacuuming, lower autovacuum_vacuum_scale_factor; lowering autovacuum_vacuum_cost_delay lets vacuum use its cost budget more aggressively.

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, xact_start, backend_xmin
FROM pg_stat_activity
WHERE xact_start < NOW() - INTERVAL '10 minutes'
  AND backend_xmin IS NOT NULL;

The backend_xmin field can indicate the oldest transaction horizon held by a backend. Old horizons may prevent vacuum from removing dead tuples. Investigate the session and its transaction state before canceling it; once the snapshot is released, vacuum can advance cleanup.

Under REPEATABLE READ and SERIALIZABLE, a transaction keeps its snapshot for its duration. If a report runs for two hours at either level, that old snapshot can prevent vacuum from removing versions that may still be visible to it. Plan for this when setting up autovacuum thresholds on tables queried by long-running analytical transactions.

Observability Checklist

• Logs: Record transaction or request ID, isolation level, duration, commit or rollback outcome, SQLSTATE, and retry attempt. Use normalized query names; leave bind values out of logs.
• Metrics: Track transaction volume and duration by isolation level, serialization failures, deadlocks, retry rate, lock-wait time, and long-running or idle transactions. Watch dead-tuple growth and autovacuum progress as well.
• Traces: Propagate trace context into database spans and capture connection-pool wait, statement duration, lock wait, and retries. Record query identifiers rather than SQL parameters or returned rows.
• Alerts: Alert on sustained increases in serialization failures, deadlocks, lock waits, retry exhaustion, and old transaction snapshots that hold back vacuum.

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.

A long report mixes database states: A report runs multiple statements at READ COMMITTED while a batch job commits updates. Each statement gets a fresh snapshot, so different sections can reflect different points in time. Use a transaction-wide snapshot when the report needs a consistent view, and account for the vacuum impact of keeping that snapshot open.

REPEATABLE READ and phantom reads: PostgreSQL’s REPEATABLE READ keeps a stable snapshot, so later inserts are not visible to repeated reads in that transaction. It does not provide SERIALIZABLE’s protection against every anomaly across interacting transactions. Use SERIALIZABLE when the business rule requires serial execution semantics, and retry serialization failures.

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: Two transactions can read the same balance, calculate new values in the application, then overwrite each other with stale values. An arithmetic UPDATE done entirely in SQL avoids this particular race, but READ COMMITTED does not protect application-side calculations. Use SELECT ... FOR UPDATE, a conditional update, or SERIALIZABLE with retry handling.

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: transaction snapshot plus checks for concurrent read/write dependencies
• 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 can abort transactions that cannot be ordered consistently; retry the full transaction
• READ COMMITTED statements can see different committed data as each gets a fresh snapshot
• Long-running statements and transaction-wide snapshots can delay vacuum cleanup; inspect backend_xmin
• 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
• MySQL InnoDB transaction isolation — snapshot and locking behavior by level
• Locking and Concurrency — row locks and concurrent updates

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.