Phantom Reads

What is a Phantom Read?

A phantom read occurs when a transaction executes the same query multiple times and sees different results due to other transactions inserting or deleting rows that match the query’s WHERE clause.

Example Scenario:

Assume the sales table initially has:

amounts: 100, 200, 300

Transaction 1 (T1):

BEGIN;
SELECT COUNT(*) FROM sales WHERE amount > 150; -- Returns 2

Meanwhile, Transaction 2 (T2):

BEGIN;
INSERT INTO sales (amount) VALUES (250);
COMMIT;

Back to T1:

BEGIN;
SELECT COUNT(*) FROM sales WHERE amount > 150; -- Returns 3 (phantom row!)
COMMIT;

T1 sees different results between two reads — a classic phantom read.

---

Visualization with Mermaid

Sequence Diagram

sequenceDiagram
    participant T1 as Transaction 1<br/>(Read Committed)
    participant DB as Database
    participant T2 as Transaction 2
    
    Note over T1,T2: Initial state: sales table contains<br/>amounts: 100, 200, 300
    
    T1->>DB: BEGIN
    T1->>DB: SELECT COUNT(*)<br/>FROM sales WHERE amount > 150
    DB-->>T1: Returns count: 2
    
    T2->>DB: BEGIN
    T2->>DB: INSERT INTO sales<br/>(amount) VALUES (250)
    T2->>DB: COMMIT
    
    T1->>DB: SELECT COUNT(*)<br/>FROM sales WHERE amount > 150
    DB-->>T1: Returns count: 3<br/><i>(Phantom row appears)</i>
    
    T1->>DB: COMMIT

---

Handling Phantom Reads in PostgreSQL

Isolation Levels Overview

flowchart LR
    subgraph "PostgreSQL Isolation Levels"
        RC["Read Committed<br/><i>(Default)</i>"] --> RR["Repeatable Read"] --> SER["Serializable"]
        
        RC --> RC_desc["• Allows phantom reads<br/>• Fastest performance<br/>• Good for simple reads"]
        RR --> RR_desc["• Prevents phantom reads<br/>• Strong consistency<br/>• May need retries"]
        SER --> SER_desc["• Maximum isolation<br/>• Full consistency<br/>• Highest overhead"]
    end
    
    style RC fill:#ffcccc,stroke:#ff9999,color:#000000
    style RR fill:#ffffcc,stroke:#ffcc99,color:#000000
    style SER fill:#ccffcc,stroke:#99ff99,color:#000000

---

PostgreSQL Implementation

Using Serializable Isolation (Prevents Phantom Reads)

-- Transaction 1
BEGIN TRANSACTION ISOLATION LEVEL SERIALIZABLE;
SELECT COUNT(*) FROM sales WHERE amount > 150; -- Returns 2
 
-- Transaction 2 (in another session)
BEGIN TRANSACTION ISOLATION LEVEL SERIALIZABLE;
INSERT INTO sales (amount) VALUES (250);
COMMIT;
 
-- Back to Transaction 1
SELECT COUNT(*) FROM sales WHERE amount > 150; -- Still returns 2 (reads its snapshot, no phantom)
COMMIT; -- Both transactions commit: a read-only T1 doesn't conflict

• Prevents phantom reads
• T1 just keeps seeing its snapshot; no error here. A serialization error only happens when the concurrent transactions form a read/write dependency cycle (e.g. T1 also writes based on the count it read)
• Highest consistency
• May fail with serialization errors (needs retry)

---

Using Read Committed (Default)

-- Transaction 1
BEGIN;
SELECT COUNT(*) FROM sales WHERE amount > 150; -- Returns 2
 
-- Transaction 2 (in another session)
BEGIN;
INSERT INTO sales (amount) VALUES (250);
COMMIT;
 
-- Back to Transaction 1
SELECT COUNT(*) FROM sales WHERE amount > 150; -- Returns 3 (phantom read!)
COMMIT;

• Fast and simple
• Allows phantom reads
• Not ideal for critical operations

---

Preventing Phantom Reads in PostgreSQL

1. Use SERIALIZABLE isolation level
2. Use REPEATABLE READ (in Postgres it is snapshot isolation, so phantoms don’t appear)
3. Apply explicit locks Example:

LOCK TABLE sales IN SHARE MODE; -- blocks concurrent INSERT/UPDATE/DELETE until commit

• Note: SELECT ... FOR SHARE only locks the rows that already exist; Postgres has no gap/range locks, so it does not stop other transactions inserting new matching rows.

4. Implement application-level locking mechanisms

---

Trade-offs

Isolation Level	Phantom Read	Performance	Use Case
Read Committed	Yes	Fast	Simple reads, non-critical logic
Repeatable Read	No	Medium	Consistency-focused apps
Serializable	No	Slower	Mission-critical, consistent reads

---

Conclusion

Phantom reads are a significant concurrency issue in databases that affect consistency during concurrent transactions. Choosing the right isolation level and concurrency control mechanism is essential based on the application’s consistency and performance requirements.

NOTE

• Postgres’s default level, Read Committed, allows phantom reads (see the example above). Postgres prevents them at Repeatable Read (snapshot) and Serializable (SSI) – stricter than the SQL standard, which only requires it at Serializable.