Phantom Reads in SQL

Introduction

A phantom read occurs when a transaction repeats a predicate query and receives a different set of rows because another transaction committed an insert, delete, or qualifying update between the two statements. The rows that appear or disappear are called phantoms.

This anomaly concerns a changing result set rather than one known row changing value. Preventing it can require transaction snapshots, range locking, next-key locking, predicate dependency tracking, or a redesigned business constraint. This tutorial develops those mechanisms through two-session examples and product-specific behavior.

What Is a Phantom Read?

Suppose Transaction A queries all employees earning more than 50,000 and receives four rows. Transaction B inserts a fifth qualifying employee and commits. If A repeats the same query and now receives five rows, the new row is a phantom.

The inserted employee is committed and valid. The anomaly exists because A expected the predicate result to remain stable for its transaction.

The Predicate Defines the Protected Set

A phantom cannot be analyzed without writing the exact search condition. The set might be all open orders for one customer, all available seats in one event, or all payments posted within a date range. Equality, range, join, and subquery predicates describe different logical sets and can lead the optimizer to different indexes and locking plans. Saying that a transaction read the orders table is too broad; the useful statement is that it read rows satisfying customer_id = 42 AND status = 'OPEN'.

This precision also explains why an update can create a phantom. An order already exists, but changing its status from PENDING to OPEN moves it into the protected set. Conversely, changing an open order to CANCELLED removes it. The physical row is not new, yet its membership relative to the predicate is new. Tests and production diagnostics should therefore record the exact SQL, bound values, index plan, and transaction boundary rather than discussing phantoms only in terms of inserted rows.

Why Phantom Reads Matter

Business logic often depends on counts, absence checks, capacity limits, and membership in a set. A department may allow at most ten active employees, an event may have limited seats, or a promotion may apply only while fewer than one hundred orders qualify.

If concurrent inserts change the set after the check, independent transactions can each make locally valid decisions that violate the shared rule. Phantom protection is therefore important when a predicate influences a write or external action.

Stable Reading vs Enforcing a Business Invariant

Two goals are often confused. A report may need every query to reflect one consistent point in time. A transaction-level snapshot usually satisfies that read-only requirement because later commits remain outside the reader's view. A reservation workflow has a stronger requirement: after checking that capacity exists, its insert must remain valid in combination with every concurrent transaction. Merely hiding another transaction's insert does not reserve capacity or prevent both writers from acting on the same earlier snapshot.

For write decisions, ask whether all successful transactions can be placed in a serial order that respects the rule. Serializable isolation may provide that guarantee by blocking a conflicting change or aborting one participant. A schema can sometimes do better: pre-created seat rows with a unique owner, a guarded inventory counter, or a unique constraint can turn a broad predicate rule into a direct write conflict. The best solution is not always a stronger read; it is often a data model that lets the database reject an invalid final state atomically.

How a Phantom Happens

  1. Transaction A begins and executes a predicate query.
  2. Transaction A remains open.
  3. Transaction B inserts, deletes, or updates a row affecting that predicate.
  4. Transaction B commits.
  5. Transaction A repeats the same predicate query.
  6. The returned row set or count differs.

Both queries can read only committed data. Phantom reads therefore do not require dirty visibility.

Phantom Read Timeline

TimeTransaction ATransaction B
T1BeginsNot started
T2Counts four qualifying employeesNot started
T3Remains openInserts a qualifying employee
T4Remains openCommits
T5Counts five qualifying employeesComplete

SQL Setup

CREATE TABLE employees (
    employee_id INTEGER PRIMARY KEY,
    employee_name VARCHAR(100) NOT NULL,
    salary DECIMAL(12, 2) NOT NULL
);

INSERT INTO employees (employee_id, employee_name, salary)
VALUES
    (1, 'Asha', 48000.00),
    (2, 'Daniel', 52000.00),
    (3, 'Meera', 61000.00);

Commit the setup before opening two independent sessions. The initial predicate salary > 50000 returns two rows.

Two-Session Phantom Example

-- Session A
BEGIN;
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;

SELECT employee_id, employee_name, salary
FROM employees
WHERE salary > 50000;
-- Returns two rows.
-- Session B
BEGIN;
INSERT INTO employees (employee_id, employee_name, salary)
VALUES (4, 'Luis', 60000.00);
COMMIT;
-- Session A
SELECT employee_id, employee_name, salary
FROM employees
WHERE salary > 50000;
-- May now return three rows.

COMMIT;

Common Read Committed implementations allow the second statement to observe B's commit. Repeat at stronger levels to discover the engine's actual behavior.

Making the Test Deterministic

Pause Session A after its first query, commit the Session B insert, and only then repeat A's query. Automated tests should coordinate sessions with barriers or latches rather than relying on arbitrary sleeps.

Both A queries must use the same explicit transaction and connection. With autocommit, they are separate transactions, so changing results do not demonstrate a phantom within one transaction.

Operations That Cause Phantoms

An insert can add a matching row, a delete can remove one, and an update can move a row into or out of the predicate. For WHERE status = 'OPEN', changing a status from PENDING to OPEN creates a phantom even though no new row was inserted.

The defining feature is changing predicate membership, not the SQL command type.

Isolation Level Overview

LevelStandard phantom expectationPractical note
Read UncommittedPossibleMay also expose uncommitted rows
Read CommittedPossibleFresh statement views commonly change
Repeatable ReadMay be possible by standard modelSeveral MVCC products prevent snapshot phantoms
SerializablePreventedMay block or abort unsafe transactions

Read Uncommitted

Read Uncommitted can expose both committed and uncommitted changes to the result set on supporting products. A row might appear temporarily and disappear when the writer rolls back, combining phantom-like membership change with dirty visibility.

This level is unsuitable for capacity, eligibility, financial, or allocation decisions.

Read Committed

Read Committed prevents ordinary dirty reads, but each statement commonly receives a fresh committed view. A repeated predicate can therefore include rows committed after the first query.

This is expected behavior rather than a defect. If stable membership matters, the application needs a stronger snapshot, range protection, or a write-boundary constraint.

Repeatable Read

The SQL standard permits phantoms at Repeatable Read while preventing known rows from changing under the transaction. Real databases often provide stronger behavior. Snapshot-based implementations may keep the complete query result stable because later commits are outside the snapshot.

Locking implementations can stabilize existing rows without locking the gaps where new matches could be inserted. The database's documented semantics decide the result.

Serializable

Serializable prevents committed outcomes that cannot be arranged into a valid serial order. A lock-based engine may protect index ranges, while a serializable snapshot engine can detect dangerous dependencies and abort one transaction.

Applications must handle blocking, deadlocks, or serialization failures. Retry the complete transaction from a fresh snapshot with bounded backoff and idempotency.

PostgreSQL Repeatable Read

PostgreSQL Repeatable Read uses one transaction snapshot and does not expose rows committed after that snapshot. Repeating the same predicate therefore does not produce the standard phantom result.

However, snapshot isolation can still permit write skew, where transactions update different rows after reading a shared condition. PostgreSQL Serializable adds dependency tracking to reject unsafe schedules.

PostgreSQL Behavior

At Read Committed, each PostgreSQL command receives a new snapshot, so the example can show a phantom. At Repeatable Read, the later insert remains invisible to A. At Serializable, Serializable Snapshot Isolation can abort a transaction when needed for serial behavior.

PostgreSQL's predicate locks used by Serializable are nonblocking markers for dependency detection, not conventional locks that necessarily stop an insert immediately.

MySQL InnoDB Behavior

InnoDB's common Repeatable Read mode gives consistent nonlocking reads a transaction snapshot, preventing later committed rows from appearing in repeated snapshot queries. Locking reads and modifications use current-read semantics and can acquire next-key locks.

At Read Committed, each consistent read gets a fresh snapshot and can see phantoms. Index choice and query form strongly influence locking behavior.

SQL Server Behavior

SQL Server Read Committed and Repeatable Read can permit phantoms. Repeatable Read holds locks on rows already read but does not necessarily protect the key ranges where new qualifying rows can appear.

Serializable uses key-range locks when suitable indexes support the predicate. Snapshot isolation provides a stable versioned view but has different write-conflict and write-skew considerations.

Oracle Behavior

Oracle Read Committed uses statement-level read consistency, so repeated queries can see newly committed matching rows. Serializable and Read Only transactions use a transaction-level snapshot and maintain a stable result view.

Serializable updates may fail when relevant data changed after the transaction began. The application must handle the documented conflict rather than assuming the snapshot reserves the rows.

SQLite Behavior

An explicit SQLite read transaction observes a snapshot. In WAL mode another connection can commit while the reader continues seeing its original view. In rollback-journal mode lock interactions may prevent the writer from committing until readers finish.

Separate autocommit queries are separate transactions and can return different sets. That is changing database state between transactions, not a phantom inside one transaction.

Database Comparison

DatabaseRead CommittedStable predicate option
PostgreSQLStatement snapshots can changeRepeatable Read or Serializable
MySQL InnoDBFresh consistent-read snapshotsRepeatable Read snapshot or locking strategy
SQL ServerStatement locks or versionsSerializable, Snapshot, or explicit range design
OracleStatement-level read consistencySerializable or Read Only
SQLiteExplicit transaction snapshot semanticsTransaction and journal-mode dependent

Phantom vs Dirty Read

A dirty read observes another transaction's uncommitted data. A phantom read can involve entirely committed rows whose membership changes between repeated predicate queries. Read Committed prevents the former while commonly allowing the latter.

Phantom vs Non-Repeatable Read

A non-repeatable read changes the value or existence of a row already identified. A phantom changes the set of rows matching a predicate. One concurrent update can cause both when it changes an existing row and its predicate membership.

Phantom vs Lost Update

A phantom is a read-set anomaly. A lost update is a write anomaly where one transaction's effect is overwritten or omitted. Stable predicates do not automatically prevent stale writes, and version checks do not automatically stabilize predicate membership.

MVCC and Phantom Reads

MVCC lets a transaction select row versions from a snapshot. A transaction-level snapshot naturally hides later inserts and membership changes from ordinary snapshot reads. A statement-level snapshot can change between queries.

Long snapshots retain old versions and can delay cleanup. Snapshot stability also does not by itself enforce a capacity rule against concurrent writers.

Range Locks

A row lock protects an existing row, but a phantom can occupy a gap where no row currently exists. Range locks protect an interval of index keys so another transaction cannot insert a new qualifying key until the protecting transaction completes.

Without a useful index, the database may lock a broader range or table, increasing contention. Index design is therefore part of serializable performance.

Next-Key Locking

InnoDB next-key locking combines a record lock with a gap lock around indexed records. Locking reads at suitable isolation can prevent inserts into the protected range and therefore prevent phantoms for that operation.

Exact locks depend on isolation, index uniqueness, predicate, and execution plan. Use engine diagnostics rather than assuming one SQL predicate maps to one narrow range.

Predicate Locking and Dependency Tracking

Predicate protection means preserving the logical condition, including rows that do not yet exist. Some lock-based engines implement this through range locks. PostgreSQL Serializable uses predicate-style markers to identify read-write dependencies and may abort a transaction rather than blocking every conflicting insert.

Both approaches can provide serializable outcomes, but their waiting and retry characteristics differ.

Snapshot Isolation and Write Skew

A stable snapshot prevents visible phantoms for the reader, yet two transactions can read the same capacity condition and insert different rows without a direct write conflict. Together they can exceed a limit. This is write skew.

Use Serializable, a locked counter or guard row, a database constraint, or another atomic allocation design when the predicate controls a shared invariant.

Indexes and Predicate Protection

An index aligned with the predicate helps the database identify a narrow key range. For WHERE event_id = ? AND seat_number BETWEEN ? AND ?, a composite index beginning with those filtering columns can improve both query speed and lock precision.

Missing or unsuitable indexes can cause scans, broad locks, more deadlocks, and lower concurrency. Review execution plans together with lock behavior.

Execution Plans Can Change Concurrency Behavior

The SQL text alone does not reveal which keys or gaps an engine will protect. Statistics, parameter values, available indexes, and optimizer choices can switch a query from an index seek to a scan. Under locking Serializable implementations, that change can expand protection from a narrow range to many pages or an entire table. Correctness should remain intact, but latency, blocking, and deadlock risk can change dramatically.

This is why concurrency testing should use production-like data volume and distributions. A small test table may choose a scan that hides the benefit of an index, while a large production table may choose a plan that locks a different key order. Capture the actual plan and lock diagnostics for the same parameter values used by the failing workflow. If plan variation creates unacceptable contention, improve the index and query shape, but do not force a plan without measuring its behavior across representative inputs.

COUNT Queries

Counts make phantom effects obvious. A transaction counts nine active enrollments, another transaction adds one and commits, and the first count returns ten. If the maximum is ten, two concurrent sessions can both decide that one more enrollment is allowed.

Protect the capacity decision with an atomic counter, serializable predicate, unique slot allocation, or constrained reservation model rather than relying on an unprotected count.

DELETE and UPDATE Phantoms

A committed delete removes a matching row from the repeated set. An update can move a row into or out of the set by changing status, amount, date, or another filtered column. These are phantoms even though no insert occurred.

Tests should cover all three operations because indexes and locking plans can differ for inserts, deletes, and updates.

Banking and Financial Reports

A transaction summing high-value transfers can receive a different row set after another transfer commits. Reports assembled from multiple statement snapshots may contain totals and details from different moments.

Use a transaction snapshot, reporting cut-off, replica, or warehouse load when the complete report must describe one point in time.

Orders and Inventory

An order process can count existing reservations and then insert another while a competing transaction does the same. Both counts may be below capacity, yet both inserts together exceed it.

Use atomic stock decrements, unique reservation slots, locked counters, or serializable transactions. A stable snapshot alone does not reserve capacity.

Ticket and Appointment Booking

A query for available seats or appointment slots returns a predicate-defined set. Concurrent bookings remove rows from that set or create reservations that make them unavailable. The user interface is always advisory under concurrency.

A uniqueness constraint on event and seat, or provider and time slot, makes the write authoritative. The losing request returns a controlled conflict.

Enrollment and Payroll

Student enrollment can exceed course capacity when sessions count and insert concurrently. Payroll review sets can change as employees become eligible while a batch is running, causing inconsistent inclusion.

Use explicit batch cut-offs for payroll and atomic slot allocation for enrollment. The correct technique depends on whether the process needs a historical snapshot or current capacity enforcement.

Pagination and Phantoms

Offset pagination across changing data can skip or duplicate rows when inserts or deletes shift positions between page requests. These page requests usually run in separate transactions, but the user-visible symptom resembles changing predicate membership.

Keyset pagination gives more stable traversal. For a fixed export, materialize the dataset or use a bounded transaction snapshot while considering version-retention costs.

How to Prevent Phantom Reads

  • Use Serializable when the predicate must govern a write.
  • Use a transaction snapshot for stable read-only reporting.
  • Use indexed range or next-key locks where supported and appropriate.
  • Model capacity with atomic counters or unique reservation slots.
  • Create constraints that make invalid final state impossible.
  • Keep transactions short and predicates well indexed.
  • Handle serialization failures and deadlocks with bounded retries.

Designing Capacity Rules for Concurrency

Consider an event with one hundred seats. The fragile design counts reservation rows and inserts when the count is below one hundred. The count describes an absence: unused capacity is represented by rows that do not yet exist, so ordinary row locks have nothing concrete to lock. Two sessions can count ninety-nine and both insert. A stronger design pre-creates one hundred seat or slot rows and claims one with a conditional update such as UPDATE seats SET owner_id = ? WHERE seat_id = ? AND owner_id IS NULL. The affected-row count reports whether the claim succeeded.

Another design stores remaining capacity in a parent row and decrements it conditionally with WHERE remaining_capacity > 0. Both approaches convert a predicate over many possible rows into contention on an explicit resource. They may reduce concurrency at one hot row, so partitioning capacity by event, date, region, or inventory bucket can help. The important point is that the write itself enforces the boundary. Isolation, constraints, and retry logic then support a clear invariant instead of compensating for an ambiguous check-then-insert sequence.

Autocommit

Two autocommit queries are separate transactions, so a changing result set is expected current-state behavior rather than a phantom within one transaction. Stable multi-query results require an explicit transaction and suitable isolation.

Autocommit is useful for independent statements but cannot protect a capacity check followed by an insert as one unit.

Transaction Duration

Long serializable or range-locking transactions can block inserts for extended periods. Long snapshots retain old versions and may increase cleanup pressure. Holding a transaction open while users think or remote services respond is especially harmful.

Keep the database phase focused. For long reports, use dedicated reporting infrastructure or a materialized cut-off dataset.

Deadlocks and Retries

Range locks and multiple protected predicates can increase deadlock opportunities. Serializable snapshot systems may abort transactions when dependency cycles become dangerous even without conventional blocking.

Retry the complete transaction from a fresh snapshot, use consistent lock order, bound retries, add jitter, and preserve one idempotency key. Retrying only the insert ignores the predicate decision that preceded it.

Phantom Reads with JDBC

try (Connection connection = dataSource.getConnection()) {
    connection.setAutoCommit(false);
    connection.setTransactionIsolation(
        Connection.TRANSACTION_SERIALIZABLE
    );

    checkCapacityAndReserve(connection);
    connection.commit();
}

Confirm driver support and database semantics. Catch documented serialization and deadlock errors outside the transaction scope so the complete operation can be retried on a fresh connection state.

Connection Pools and Frameworks

Isolation is often connection state. Pools must restore it before reuse. A framework annotation may not apply when a method call bypasses its transaction proxy or asynchronous work leaves the transaction context.

Test actual service boundaries and verify that all predicate reads and writes use the same connection.

Testing Phantom Reads

Session A starts and executes a predicate query, then waits. Session B inserts, updates, or deletes a matching row and attempts to commit. Session A repeats the query. Record returned rows, blocking, commit behavior, and error codes.

Use deterministic barriers rather than sleeps and run against the production database family, schema, indexes, and isolation settings.

Interpreting Blocking, Snapshots, and Aborts

A successful prevention test does not always return the same visible symptom. In a range-locking engine, Session B may wait until Session A commits; after that wait, both transactions can complete in a serial order. In an MVCC snapshot, B may commit immediately while A continues to see its earlier snapshot. Under Serializable dependency detection, both may run concurrently until one commit is rejected with a serialization failure. Each outcome can prevent an unsafe committed history through a different mechanism.

The test must therefore assert more than row counts. Record whether B blocked, whether A's second query used the same snapshot, which commit failed, and whether the application retried the entire unit of work. A timeout is not proof of correct phantom protection, and an exception is not necessarily a defect. Conversely, a stable result set is not proof that a capacity invariant was enforced. The final assertion should examine both transaction outcomes and the committed business state after all retries finish.

Test Cases

  1. Demonstrate an inserted phantom at Read Committed.
  2. Repeat with a deleted qualifying row.
  3. Update a row into and out of the predicate.
  4. Compare Repeatable Read behavior across supported databases.
  5. Observe SQL Server range locks at Serializable.
  6. Test InnoDB consistent reads and locking reads separately.
  7. Force a PostgreSQL Serializable retry scenario.
  8. Remove the predicate index and compare lock scope.
  9. Test capacity enforcement with concurrent reservations.
  10. Verify connection pools reset isolation state.

Monitoring Phantom Protection

Track range-lock waits, deadlocks, serialization failures, retry exhaustion, transaction age, version retention, and query plans for protected predicates. Correlate database events with endpoints and jobs using safe request identifiers.

A serialization failure can be correct behavior. Operational concern begins when rates, retries, or latency exceed service objectives.

Common Mistakes

  • Calling every changed query result a phantom.
  • Confusing one row's changed value with changed set membership.
  • Assuming Repeatable Read behaves identically across databases.
  • Believing a stable snapshot reserves capacity.
  • Locking current rows but not the gaps where new rows can appear.
  • Ignoring index design when relying on range protection.
  • Testing with autocommit or one connection accidentally.
  • Retrying only the final write after a serialization conflict.
  • Changing the global isolation default for one workflow.
  • Holding transactions open during remote calls or user input.

Prevention Best Practices

  • Define the exact predicate and business invariant.
  • Decide whether the workflow needs stable reporting or capacity enforcement.
  • Use constraints and atomic allocation models where possible.
  • Select isolation from actual vendor behavior.
  • Align indexes with predicates to narrow protected ranges.
  • Keep transactions short and connection-scoped.
  • Implement whole-transaction retries with idempotency.
  • Reset pooled connection state.
  • Run deterministic multi-session integration tests.
  • Monitor blocking, aborts, retries, and version retention.

Interview Questions and Answers

What is a phantom read?

It occurs when repeating a predicate query in one transaction returns a different row set because another transaction committed a membership-changing operation.

How is it different from a non-repeatable read?

A non-repeatable read changes a known row's value or existence; a phantom changes the set matching a predicate.

Does Repeatable Read always allow phantoms?

No. The standard model may allow them, but several MVCC implementations provide stable snapshots that prevent visible phantoms.

What prevents phantoms?

Serializable execution, transaction snapshots for read stability, or product-specific range and predicate protection can prevent them.

Does a stable snapshot enforce capacity?

No. Concurrent writers may still violate a shared rule unless conflicts are detected or the invariant is protected at the write boundary.

Practical Exercises

  1. Create the employee table and reproduce the two-session insert example.
  2. Repeat with delete and qualifying update operations.
  3. Compare Read Committed, Repeatable Read, and Serializable.
  4. Inspect range or next-key locks using database diagnostics.
  5. Add and remove the predicate index and compare behavior.
  6. Build a concurrent course-capacity test.
  7. Replace a count-and-insert flow with unique slot allocation.
  8. Implement JDBC serialization retry around the full transaction.
  9. Test snapshot stability separately from write-skew protection.
  10. Document one real project's predicate invariant.

Quick Reference

ConceptMeaning
Phantom readRepeated predicate returns a changed committed set
Range lockProtects an indexed key interval
Gap lockProtects space between existing index records
Next-key lockCombines record and adjacent gap protection
Predicate trackingDetects dependencies involving a logical condition
SnapshotStable view of versions from one point in time
Write skewDifferent-row writes violate a shared read condition
SerializableOutcome is equivalent to an allowed serial order

Phantom Prevention Workflow

  1. Write the exact predicate and business capacity rule.
  2. Identify inserts, deletes, and updates that change membership.
  3. Reproduce the schedule with two controlled sessions.
  4. Confirm actual database and storage-engine semantics.
  5. Separate read-only snapshot needs from write enforcement.
  6. Add constraints or atomic allocation where possible.
  7. Choose serializable, range locking, or another supported strategy.
  8. Align indexes with the protected predicate.
  9. Implement complete retries for retryable conflicts.
  10. Keep the transaction short and connection-local.
  11. Load-test contention, aborts, and version retention.
  12. Monitor and revisit the design as workload changes.

Key Takeaway

A phantom read is a change in the committed row set matching a repeated predicate. It differs from dirty visibility and from one known row changing value. The anomaly matters when a count, absence check, eligibility query, or capacity predicate controls a later business action.

Use transaction snapshots for stable reporting and serializable or write-boundary protection for shared invariants. Range-aware indexing, atomic allocation, constraints, short transactions, idempotent retries, and deterministic multi-session tests complete the design. The correct solution protects the predicate without imposing more contention than the business rule requires.