Database Isolation Levels: Read Uncommitted, Read Committed, Repeatable Read, Serializable

Database Isolation Levels: Read Uncommitted, Read Committed, Repeatable Read, Serializable

Isolation levels define how much one transaction can see the uncommitted changes of another concurrent transaction. They balance two competing needs: data consistency and concurrency performance. Higher isolation provides stronger guarantees but reduces the number of transactions that can run simultaneously. Lower isolation increases concurrency but risks various data anomalies.

Choosing the right isolation level is critical for application correctness. Too low, and you risk reading inconsistent data. Too high, and you risk deadlocks and poor performance. To understand isolation levels properly, it is helpful to be familiar with database transactions, database locking, and SQL optimization.

Isolation Level          | Dirty Read | Non-Repeatable Read | Phantom Read
─────────────────────────┼────────────┼─────────────────────┼─────────────
Read Uncommitted         | ✓ Possible | ✓ Possible          | ✓ Possible
Read Committed           | ✗ Prevented| ✓ Possible          | ✓ Possible
Repeatable Read          | ✗ Prevented| ✗ Prevented         | ✓ Possible*
Serializable             | ✗ Prevented| ✗ Prevented         | ✗ Prevented

* Some databases prevent phantom reads at Repeatable Read (e.g., PostgreSQL)

What Are Isolation Levels

Isolation levels are a setting that determines how a database handles concurrent transactions. They control the visibility of changes made by one transaction to other transactions before those changes are committed. The SQL standard defines four isolation levels, each with different guarantees about what data anomalies are prevented.

• Read Uncommitted: Lowest isolation, highest concurrency. Can see uncommitted changes.
• Read Committed: Default in many databases (PostgreSQL, SQL Server, Oracle). Only sees committed changes.
• Repeatable Read: Ensures consistent reads within a transaction. Prevents changes to read data.
• Serializable: Highest isolation, lowest concurrency. Transactions appear to run sequentially.

Why Isolation Levels Matter

Without proper isolation, concurrent transactions can interfere with each other, leading to data corruption, incorrect query results, and application bugs that are extremely difficult to reproduce and debug.

• Data Correctness: Prevents reading partial or inconsistent data from concurrent transactions.
• Application Logic Integrity: Ensures that business logic that reads data multiple times sees consistent values.
• Performance Tuning: Lower isolation levels reduce locking overhead and improve throughput.
• Deadlock Reduction: Lower isolation levels hold fewer locks, reducing deadlock probability.
• Compliance Requirements: Some applications require serializable isolation for financial or regulatory reasons.

Data Anomalies (Concurrency Problems)

Dirty Read

A dirty read occurs when a transaction reads data that has been written by another transaction that has not yet committed. If the other transaction later rolls back, the first transaction has read data that never actually existed.

Transaction A                    Transaction B
BEGIN;                           BEGIN;
    UPDATE accounts SET balance = balance - 100 WHERE id = 1;
                                     SELECT balance FROM accounts WHERE id = 1;  -- Reads $900 (uncommitted!)
    -- Transaction A rolls back
ROLLBACK;
-- Transaction B has read $900, but actual balance is still $1000

Isolation level that prevents: Read Committed or higher

Non-Repeatable Read

A non-repeatable read occurs when a transaction reads the same row twice and gets different values because another transaction modified and committed the row between the two reads.

Transaction A                    Transaction B
BEGIN;                           BEGIN;
    SELECT balance FROM accounts WHERE id = 1;  -- $1000
                                     UPDATE accounts SET balance = balance - 100 WHERE id = 1;
                                     COMMIT;
    SELECT balance FROM accounts WHERE id = 1;  -- $900 (different!)
COMMIT;

Isolation level that prevents: Repeatable Read or higher

Phantom Read

A phantom read occurs when a transaction executes the same query twice and gets a different set of rows because another transaction inserted or deleted rows that satisfy the query condition between the two executions.

Transaction A                    Transaction B
BEGIN;                           BEGIN;
    SELECT COUNT(*) FROM orders WHERE status = 'pending';  -- 5 rows
                                     INSERT INTO orders (status) VALUES ('pending');
                                     COMMIT;
    SELECT COUNT(*) FROM orders WHERE status = 'pending';  -- 6 rows (phantom!)
COMMIT;

Isolation level that prevents: Serializable

Lost Update

A lost update occurs when two transactions read the same value, both modify it based on what they read, and the second write overwrites the first without incorporating the first's change.

Transaction A                    Transaction B
BEGIN;                           BEGIN;
    SELECT balance FROM accounts WHERE id = 1;  -- $1000
                                     SELECT balance FROM accounts WHERE id = 1;  -- $1000
    UPDATE accounts SET balance = $1000 - 100;  -- Sets to $900
                                     UPDATE accounts SET balance = $1000 - 50;   -- Sets to $950 (overwrites!)
COMMIT;                          COMMIT;
-- Final balance: $950 (Transaction A's $100 debit was lost)

Isolation level that prevents: Repeatable Read or higher (with locking) or use SELECT FOR UPDATE

Isolation Levels in Detail

1. Read Uncommitted

Read Uncommitted is the lowest isolation level. A transaction can see uncommitted changes from other transactions. This allows dirty reads, non-repeatable reads, and phantom reads. It is rarely used in production because of the risk of reading invalid data.

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
BEGIN;
    SELECT * FROM accounts;  -- Can see uncommitted changes from other transactions
COMMIT;

Pros: Highest concurrency, no locking
Cons: Dirty reads, non-repeatable reads, phantom reads
Use cases: Read-only reporting where absolute accuracy is not required

2. Read Committed

Read Committed is the default isolation level in PostgreSQL, SQL Server, and Oracle. A transaction can only see changes that have been committed. Dirty reads are prevented, but non-repeatable reads and phantom reads can still occur.

SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
BEGIN;
    SELECT * FROM accounts WHERE id = 1;  -- Only sees committed data
    -- Another transaction could modify this row and commit
    SELECT * FROM accounts WHERE id = 1;  -- May see different value
COMMIT;

Pros: Good concurrency, prevents dirty reads
Cons: Non-repeatable reads possible
Use cases: Most web applications, where read consistency per query is sufficient

3. Repeatable Read

Repeatable Read ensures that if a transaction reads a row, subsequent reads of that row within the same transaction will return the same values. Other transactions cannot modify rows that have been read (using shared locks or snapshot isolation). However, phantom reads may still occur in some databases.

SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
BEGIN;
    SELECT * FROM accounts WHERE id = 1;  -- $1000
    -- Other transactions cannot modify this row until this transaction ends
    SELECT * FROM accounts WHERE id = 1;  -- Still $1000
COMMIT;

Pros: Prevents non-repeatable reads and lost updates
Cons: More locking, potential deadlocks
Use cases: Applications that read the same data multiple times within a transaction

4. Serializable

Serializable is the highest isolation level. Transactions appear to execute sequentially, one after another. It prevents all data anomalies: dirty reads, non-repeatable reads, phantom reads, and lost updates.

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
BEGIN;
    SELECT COUNT(*) FROM orders WHERE status = 'pending';  -- 5 rows
    -- Other transactions cannot insert, update, or delete rows that would affect this query
    SELECT COUNT(*) FROM orders WHERE status = 'pending';  -- Still 5 rows
COMMIT;

Pros: Complete consistency, no anomalies
Cons: Lowest concurrency, highest lock contention, more deadlocks
Use cases: Financial transactions, banking, any operation requiring perfect consistency

Default Isolation Levels by Database

Setting Isolation Levels

-- Transaction level
BEGIN;
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
SELECT * FROM accounts;
COMMIT;

-- Session level
SET DEFAULT_TRANSACTION_ISOLATION TO 'repeatable read';

-- Global level (postgresql.conf)
default_transaction_isolation = 'repeatable read'

-- Transaction level
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
BEGIN;
SELECT * FROM accounts;
COMMIT;

-- Session level
SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;

-- Global level
SET GLOBAL TRANSACTION ISOLATION LEVEL REPEATABLE READ;

-- Transaction level
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
BEGIN TRANSACTION;
SELECT * FROM accounts;
COMMIT TRANSACTION;

-- Enable snapshot isolation
ALTER DATABASE MyDatabase SET ALLOW_SNAPSHOT_ISOLATION ON;
SET TRANSACTION ISOLATION LEVEL SNAPSHOT;

How Isolation Levels Are Implemented

Locking-Based Isolation

Traditional isolation uses locks to prevent concurrent access. Read locks (shared locks) prevent writes. Write locks (exclusive locks) prevent both reads and writes.

Read Committed:
- Takes read locks only during each read, releases immediately
- Write locks held until commit

Repeatable Read:
- Takes read locks on all rows read, holds until commit
- Write locks held until commit
- May use range locks to prevent phantoms (MySQL)

Serializable:
- Takes range locks on all rows that satisfy query conditions
- Highest lock contention

Multi-Version Concurrency Control (MVCC)

MVCC, used by PostgreSQL and Oracle, maintains multiple versions of each row. Readers see a snapshot of the database at the time of their transaction, without taking read locks.

MVCC approach:
- Writers create new versions of rows
- Readers see the version that was current when their transaction started
- No read locks needed
- Higher concurrency than locking-based isolation

Snapshot Isolation (PostgreSQL Repeatable Read):
- Each transaction sees a consistent snapshot
- Prevents dirty reads, non-repeatable reads, and phantoms
- Lost updates still possible (requires explicit locking)

Choosing the Right Isolation Level

Common Isolation Level Mistakes to Avoid

• Using Serializable by Default: Unnecessary for most applications and hurts performance significantly.
• Assuming Repeatable Read Prevents Phantoms in All Databases: MySQL does, PostgreSQL does not (it uses snapshot isolation). Know your database.
• Not Handling Retries: Serializable transactions fail more often with serialization errors. Always implement retry logic.
• Long-Running Transactions at High Isolation: Hold locks for too long, causing deadlocks and contention.
• Ignoring Lost Updates: Even at Repeatable Read, lost updates can occur. Use SELECT FOR UPDATE when needed.
• Not Testing Under Load: Concurrency issues often appear only under realistic load. Test your isolation level choice with production-like concurrency.

-- Always lock rows before updating based on current values
BEGIN;
    SELECT balance INTO @balance FROM accounts WHERE id = 1 FOR UPDATE;
    -- Now other transactions cannot modify this row
    UPDATE accounts SET balance = @balance - 100 WHERE id = 1;
COMMIT;

-- This pattern prevents lost updates regardless of isolation level

Isolation Level Best Practices

• Start with Read Committed: It is sufficient for the vast majority of applications. Only increase if you have proven problems.
• Use SELECT FOR UPDATE for Critical Updates: Explicit locking is often better than raising the isolation level.
• Keep Transactions Short: No matter the isolation level, long transactions cause problems.
• Test Under Concurrency: Simulate realistic concurrent workloads to validate your isolation level choice.
• Understand Your Database's Implementation: Different databases implement isolation levels differently (especially Repeatable Read vs Snapshot Isolation).
• Monitor for Deadlocks and Serialization Failures: Set up alerts to detect when isolation levels are causing problems.

Frequently Asked Questions

1. What is the difference between Repeatable Read and Serializable?
   Repeatable Read prevents changes to rows that have been read. Serializable also prevents new rows from appearing (phantoms). Serializable is stricter and provides complete transaction isolation.
2. Does Read Committed prevent dirty reads?
   Yes. Read Committed ensures that a transaction only sees committed data. Dirty reads are impossible at Read Committed or higher.
3. What is Snapshot Isolation?
   Snapshot Isolation is an implementation of Repeatable Read used by PostgreSQL and SQL Server. Each transaction sees a consistent snapshot of the database at the time the transaction started. It prevents dirty reads, non-repeatable reads, and phantoms, but does not prevent lost updates.
4. Should I use Serializable for financial transactions?
   Often yes, but consider using explicit locking (SELECT FOR UPDATE) at Read Committed instead. Serializable can cause more deadlocks and serialization failures, requiring retry logic.
5. What is the performance difference between isolation levels?
   Serializable can be 2-10x slower than Read Committed under high concurrency due to increased locking and lock contention. The difference grows with more concurrent transactions.
6. What should I learn next after isolation levels?
   After mastering isolation levels, explore database locking mechanisms, deadlock detection and prevention, concurrency control, and distributed transactions for advanced transaction management.