Isolation Levels in Databases: Read Committed, Repeatable Read, Snapshot, and Serializable Explained

Scenario context

Isolation levels control which concurrency anomalies a transaction can see. Read Committed (PostgreSQL and Oracle's default) prevents dirty reads but still silently allows non-repeatable reads, write skew, and lost updates. Repeatable Read adds snapshot consistency but does not always prevent write skew. Serializable eliminates every known anomaly at the cost of occasional transaction retries. Pick your isolation le…

This walkthrough focuses on Why Isolation Exists: The Concurrency Problem at the Heart of Every Database.

Before making a design choice, clarify:

• Atomicity guarantees that a transaction is all-or-nothing.
• Durability guarantees that committed data survives crashes.
• Consistency guarantees that the database moves from one valid state to another.

Concepts in play: database-isolation-levels-anomalies, caching, consistency-replication, transaction-isolation-distributed-acid

Stage 1 — The Four Concurrency Anomalies That Isolation Levels Are Designed to Prevent

• Before picking an isolation level, you need a precise vocabulary for what can go wrong.
• Every isolation level is defined by which anomalies it eliminates.
• If you skip this section and jump straight to the level names, you'll misapply them.
• Dirty Read: Transaction A reads a row that Transaction B has modified but not yet committed.

Observable outcome: Dirty Read: Transaction A reads a row that Transaction B has modified but not yet committed.

Stage 2 — How the Four ANSI SQL Isolation Levels Close the Anomaly Gap

• The ANSI SQL standard defines four isolation levels, each more restrictive than the last.
• They are not just theoretical labels — every major database ships with configurable support for at least three of them, and each has a specific implementation mechanism behind it.
• Read Uncommitted: The Weakest Floor Read Uncommitted permits dirty reads.
• A transaction can see rows written by other transactions that have not yet committed.

-- Set for the current transaction (works on MySQL, SQL Server, Oracle)
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
BEGIN;
SELECT count(*) FROM active_sessions;
COMMIT;

-- Transaction A (Read Committed, PostgreSQL default)
BEGIN;
SELECT balance FROM accounts WHERE id = 1;  -- returns 100

-- Transaction B runs and commits: UPDATE accounts SET balance = 50 WHERE id = 1;

SELECT balance FROM accounts WHERE id = 1;  -- returns 50 now
-- Same transaction, two different values for the same row.
COMMIT;

Observable outcome: A transaction can see rows written by other transactions that have not yet committed.

Stage 3 — Visualizing the Anomalies: Non-Repeatable Reads and Write Skew in Sequence

• Two scenarios cause the most confusion in practice because they can't be seen by inspecting the data after the fact — only by tracing the interleaved execution of concurrent transactions.
• Scenario 1: Non-Repeatable Read under Read Committed Under Read Committed, each SELECT gets a new snapshot.
• Transaction 1 reads a balance, Transaction 2 commits an update, and Transaction 1's second read of the same row returns the new value.
• The diagram below shows the exact interleaving that produces this anomaly.

sequenceDiagram
    participant T1 as Transaction 1
    participant DB as Database
    participant T2 as Transaction 2

    T1->>DB: BEGIN (Read Committed)
    T1->>DB: SELECT balance WHERE id=1
    DB-->>T1: 100
    T2->>DB: BEGIN
    T2->>DB: UPDATE accounts SET balance=50 WHERE id=1
    T2->>DB: COMMIT
    T1->>DB: SELECT balance WHERE id=1
    DB-->>T1: 50
    Note over T1,DB: Same transaction sees two values for the same row
    T1->>DB: COMMIT

sequenceDiagram
    participant DA as Doctor A Tx
    participant DB as Database
    participant DrB as Doctor B Tx

    DA->>DB: BEGIN (Repeatable Read)
    DrB->>DB: BEGIN (Repeatable Read)
    DA->>DB: SELECT count(*) FROM on_call WHERE shift=today
    DB-->>DA: count = 2
    DrB->>DB: SELECT count(*) FROM on_call WHERE shift=today
    DB-->>DrB: count = 2
    DA->>DB: UPDATE on_call SET status=off WHERE doctor=Alice
    DrB->>DB: UPDATE on_call SET status=off WHERE doctor=Bob
    DA->>DB: COMMIT
    DrB->>DB: COMMIT
    Note over DA,DrB: Zero doctors on call. Constraint is violated.

Observable outcome: The diagram below shows the exact interleaving that produces this anomaly.

Stage 4 — How Isolation Levels Differ Across MySQL, PostgreSQL, Oracle, and Modern Distributed Databases

• Not all databases implement the same levels with the same mechanisms, and some labels are misleading.
• Oracle calls its highest level "Serializable" but actually implements Snapshot Isolation, meaning write skew is still possible.
• Understanding the real behaviour of your specific database matters more than knowing the label.
• The most important takeaway from this table: Oracle's "Serializable" is not ANSI Serializable .

Observable outcome: The most important takeaway from this table: Oracle's "Serializable" is not ANSI Serializable .

Stage 5 — How to Choose the Right Isolation Level for Your Workload

• The decision is not "use the strongest level everywhere." That would make Serializable the universal default, but Serializable with SSI still introduces serialization failures, and Serializable with 2PL causes more bloc…
• The right approach is to identify the specific anomaly you are protecting against and choose the minimum level that prevents it.
• This flowchart guides you from the anomaly down to the mechanism.
• The branching logic is: - Write skew?

flowchart TD
    A[What anomaly are you protecting against?] --> B{Write skew risk - logic reads a set and writes based on it}
    B -->|Yes| C[Serializable - only level that prevents write skew]
    B -->|No| D{Need consistent reads within one transaction?}
    D -->|Yes| E[Repeatable Read - snapshot taken at transaction start]
    D -->|No| F{High throughput, per-row locking acceptable?}
    F -->|Yes| G[Read Committed plus SELECT FOR UPDATE on hot rows]
    F -->|No| H{Approximate reads acceptable - analytics only?}
    H -->|Yes| I[Read Uncommitted or Read Committed with no FOR UPDATE]
    H -->|No| G
    C --> J[Add retry loop for serialization failures SQLSTATE 40001]
    E --> K[Monitor for table bloat on long-running transactions]

Observable outcome: The branching logic is: - Write skew?

Stage 6 — PostgreSQL Serializable Snapshot Isolation in Practice

• PostgreSQL implemented SSI in version 9.1 and it is the safest way to run complex transactional logic without introducing application-level locking patterns.
• Here is how to use it effectively.
• Enabling SSI per transaction: Handling serialization failures in Java with JDBC: When SSI aborts a transaction due to a detected cycle, it raises SQLState 40001 .
• Any correct application using Serializable must catch this and retry: Monitoring serialization conflicts: PostgreSQL exposes serialization activity through pg stat database : A rising conflicts count is normal under SSI.

-- Set at the connection level for all transactions
SET default_transaction_isolation = 'serializable';

-- Or set per transaction (preferred — isolate only high-risk transactions)
BEGIN TRANSACTION ISOLATION LEVEL SERIALIZABLE;
SELECT count(*) FROM on_call WHERE shift = current_date AND status = 'active';
-- If count > 1, go off call
UPDATE on_call SET status = 'off' WHERE doctor_id = $1 AND shift = current_date;
COMMIT;

int maxRetries = 5;
for (int attempt = 0; attempt < maxRetries; attempt++) {
    try (Connection conn = dataSource.getConnection()) {
        conn.setTransactionIsolation(Connection.TRANSACTION_SERIALIZABLE);
        conn.setAutoCommit(false);
        try {
            // application logic
            performOnCallUpdate(conn);
            conn.commit();
            return; // success
        } catch (SQLException e) {
            conn.rollback();
            if ("40001".equals(e.getSQLState())) {
                // Serialization failure — safe to retry
                long backoff = (long) Math.pow(2, attempt) * 100;
                Thread.sleep(backoff);
            } else {
                throw e; // non-retryable error
            }
        }
    }
}
throw new RuntimeException("Transaction failed after " + maxRetries + " retries");

Observable outcome: Any correct application using Serializable must catch this and retry: Monitoring serialization conflicts: PostgreSQL exposes serialization activity through pg stat database : A rising conflicts count is normal under SSI.

End-to-end outcome

After working through the stages, you should be able to explain:

• The Read Committed + check-then-act trap: The most common production isolation bug is application code that reads a value, applies business logic, and writes back — all under Read Committed, with no SELECT FOR UPDATE .
• Under low load, transactions don't overlap.

Alternatives considered

The source focuses on one primary design. A second architecture is not compared, so no alternative is invented here.

Failure and degraded modes

The Consistency vs. Throughput Trade-off Every Isolation Level Forces You to Accept

• Detection: Every isolation level is a trade-off contract, not just a safety level you can ratchet up for free.
• User or operator impact: Every isolation level is a trade-off contract, not just a safety level you can ratchet up for free.