Non-Repeatable Read vs Phantom Read?

Non-Repeatable Read vs. Phantom Read: Understanding Database Isolation Anomalies

Dive deep into two common database isolation anomalies, Non-Repeatable Read and Phantom Read, to understand their differences, causes, and how various isolation levels prevent them.

In the world of concurrent database operations, maintaining data consistency and integrity is paramount. Transaction isolation levels are designed to manage how transactions interact with each other, preventing various anomalies. Among these, Non-Repeatable Read and Phantom Read are two distinct, yet often confused, phenomena that can occur when transactions are not sufficiently isolated. Understanding their nuances is crucial for designing robust and reliable database systems.

What is a Non-Repeatable Read?

A Non-Repeatable Read occurs when a transaction reads the same row multiple times, but gets different values each time. This happens because another concurrent transaction modifies (updates or deletes) that row between the reads of the first transaction. The first transaction is unaware of the change, leading to inconsistent results within its own scope.

sequenceDiagram
    participant T1 as Transaction 1
    participant T2 as Transaction 2

    T1->>Database: SELECT balance FROM accounts WHERE id = 1
    Database-->>T1: balance = 100
    T2->>Database: UPDATE accounts SET balance = 50 WHERE id = 1
    Database-->>T2: Row updated
    T2->>Database: COMMIT
    T1->>Database: SELECT balance FROM accounts WHERE id = 1
    Database-->>T1: balance = 50
    T1->>T1: Inconsistent read: 100 then 50

Sequence diagram illustrating a Non-Repeatable Read anomaly.

Consider a scenario where a transaction calculates a user's total balance by reading their account balance twice. If another transaction updates that account's balance between the two reads, the first transaction will see two different balances for the same account, leading to an incorrect total. This anomaly is typically prevented by isolation levels like REPEATABLE READ or higher, which ensure that any row read by a transaction remains stable for the duration of that transaction.

What is a Phantom Read?

A Phantom Read occurs when a transaction executes a query that retrieves a set of rows, and then later executes the same query, but finds that the set of rows has changed. This change is not due to modifications of existing rows (that would be a Non-Repeatable Read), but rather due to other transactions inserting or deleting rows that match the query's WHERE clause. The 'phantom' refers to rows that appear or disappear from the result set.

sequenceDiagram
    participant T1 as Transaction 1
    participant T2 as Transaction 2

    T1->>Database: SELECT COUNT(*) FROM orders WHERE status = 'pending'
    Database-->>T1: Count = 5
    T2->>Database: INSERT INTO orders (status, amount) VALUES ('pending', 200)
    Database-->>T2: Row inserted
    T2->>Database: COMMIT
    T1->>Database: SELECT COUNT(*) FROM orders WHERE status = 'pending'
    Database-->>T1: Count = 6
    T1->>T1: Inconsistent count: 5 then 6

Sequence diagram illustrating a Phantom Read anomaly.

Imagine a transaction that counts the number of 'pending' orders. If another transaction inserts a new 'pending' order before the first transaction re-executes the count, the first transaction will see a different count. Similarly, if a row is deleted, it will 'disappear' from the result set. Phantom Reads are a more subtle form of inconsistency than Non-Repeatable Reads and require higher isolation levels, typically SERIALIZABLE, to prevent them. REPEATABLE READ usually prevents Non-Repeatable Reads but not Phantom Reads, as it only locks existing rows, not the 'gaps' where new rows could be inserted.

Key Differences and Prevention

The fundamental difference lies in what changes: a Non-Repeatable Read involves changes to existing rows, while a Phantom Read involves changes to the set of rows (insertions or deletions). Both lead to inconsistent views of data within a single transaction, but they are addressed by different mechanisms and isolation levels.

Comparison of Non-Repeatable Read vs. Phantom Read

Database Isolation Levels and Anomalies

Different SQL isolation levels prevent these anomalies to varying degrees:

graph TD
    A[READ UNCOMMITTED] --> B{Dirty Read}
    B --> C{Non-Repeatable Read}
    C --> D{Phantom Read}

    E[READ COMMITTED] --> F{Non-Repeatable Read}
    F --> G{Phantom Read}

    H[REPEATABLE READ] --> I{Phantom Read}

    J[SERIALIZABLE]

    style A fill:#f9f,stroke:#333,stroke-width:2px
    style E fill:#f9f,stroke:#333,stroke-width:2px
    style H fill:#f9f,stroke:#333,stroke-width:2px
    style J fill:#f9f,stroke:#333,stroke-width:2px
    style B fill:#ccc,stroke:#333,stroke-width:1px
    style C fill:#ccc,stroke:#333,stroke-width:1px
    style D fill:#ccc,stroke:#333,stroke-width:1px
    style F fill:#ccc,stroke:#333,stroke-width:1px
    style G fill:#ccc,stroke:#333,stroke-width:1px
    style I fill:#ccc,stroke:#333,stroke-width:1px

Anomalies prevented by different SQL Isolation Levels (arrows indicate anomalies that can occur at that level or lower).

As shown in the diagram:

• READ UNCOMMITTED: Allows all three anomalies: Dirty Reads, Non-Repeatable Reads, and Phantom Reads.
• READ COMMITTED: Prevents Dirty Reads but still allows Non-Repeatable Reads and Phantom Reads.
• REPEATABLE READ: Prevents Dirty Reads and Non-Repeatable Reads, but Phantom Reads can still occur.
• SERIALIZABLE: Prevents all three anomalies, offering the highest level of isolation and consistency.