Phantom Reads, Explained

Suppose a promotion has exactly 100 coupons. The database already contains 99 successful claims. A customer can claim the final coupon only when fewer than 100 claims exist.

Two checkout requests arrive at nearly the same time. Each request counts the claims and gets 99, so each concludes that one coupon remains. Each then inserts a new claim.

The campaign now has 101 claims for 100 coupons. The second request did not update any row read by the first request. It inserted a new row that changed the answer to “how many claims does this campaign have?” while the first request was still running.

The over-claim is a check-then-write race. The phantom read is the related observation that request A gets if it repeats its count after request B commits: the new claim appears in the matching set. A phantom read alone does not corrupt data. It shows that the first query result was not a guarantee that remained true until request A wrote its claim.

This is not a dirty read. Request A never reads request B's uncommitted claim. A dirty-read sequence would be: B inserts claim 201 but has not committed, A counts 100, and B then rolls back. READ COMMITTED prevents A from seeing that uncommitted claim. In this example, A reads 99, B commits, and A later writes based on its old 99 result.

Suppose we have 99 coupon claims

Before either transaction starts, campaign 42 has 99 successful claims and a hard limit of 100:

Checkout request A runs this query before awarding a coupon:

SELECT COUNT(*) FROM coupon_claims
WHERE campaign_id = 42 AND status = 'claimed';
-- Result: 99

Request A concludes that one coupon remains. It plans to insert claim 200.

Another request claims the last coupon first

Before request A inserts its claim, checkout request B runs the same count, also gets 99, then inserts claim 201 and commits:

INSERT INTO coupon_claims (id, campaign_id, status)
VALUES (201, 42, 'claimed');
COMMIT;

Claim 201 did not exist when request A counted the claims. It now matches the exact same search condition.

Request A still awards another coupon

Request A uses its earlier result and inserts claim 200. The campaign now has 101 claims, even though the limit is 100.

At this point, request A has two possible next steps. In the race above, it skips the count and inserts claim 200, which produces the invalid total of 101. If it repeats the same count before inserting, the second statement sees the claim that B committed:

SELECT COUNT(*) FROM coupon_claims
WHERE campaign_id = 42 AND status = 'claimed';
-- Result: 100

The first count returned 99; this count returns 100. Both use the same condition, campaign_id = 42 AND status = 'claimed'. Claim 201 is the new matching row. This is a phantom read: a later query in the same transaction sees a different set because another transaction inserted or deleted a matching row.

The database did not return an incorrect result. Each statement returned the committed state it was allowed to see. The danger is treating the first count as a guarantee that remains true until the later insert. The final 101 is a stale-decision failure, not evidence that A read uncommitted data.

See the full sequence in SQL

MySQL and PostgreSQL both support READ COMMITTED. At this isolation level, each statement can see data committed before that statement begins.

-- Initial committed state: campaign 42 has 99 successful claims.
 
-- Session A: first checkout request checks whether one coupon remains.
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
START TRANSACTION;
SELECT COUNT(*) FROM coupon_claims
WHERE campaign_id = 42 AND status = 'claimed';
-- Result: 99. Request A decides it can insert one claim.
 
-- Session B: second checkout request makes the same decision.
START TRANSACTION;
SELECT COUNT(*) FROM coupon_claims
WHERE campaign_id = 42 AND status = 'claimed';
-- Result: 99. Request B also decides it can insert one claim.
INSERT INTO coupon_claims (id, campaign_id, status)
VALUES (201, 42, 'claimed');
COMMIT;
 
-- Session A: first request acts on its earlier count.
INSERT INTO coupon_claims (id, campaign_id, status)
VALUES (200, 42, 'claimed');
COMMIT;

The critical configuration is READ COMMITTED: each statement can observe a newer committed state. An explicit START TRANSACTION groups the statements, but does not make their result sets stable at this isolation level.

This differs from a non-repeatable read. A non-repeatable read returns a different value for an existing row. A phantom read changes which rows match a condition, usually because another transaction inserted or deleted a matching row.

Why it matters

Phantom reads matter when a transaction uses a query result to authorize a later write. In this example, the optional second count demonstrates the phantom: request A first sees 99, then sees 100 after B commits. The over-claim happens when A skips that second check and writes based on the stale result. The application must protect the check and write as one operation.

The same pattern appears when an availability check finds no reservation, when a duplicate check finds no matching record, or when a workflow needs all rows in a range to remain stable. A concurrent insert or delete can invalidate the earlier decision.

An explicit transaction does not automatically prevent new rows from matching a later query. The isolation level and query type determine whether the transaction reads one snapshot or sees newly committed rows between statements. For the coupon limit, the application also needs the check and claim to be protected as one operation.

Fix: serialize the decision

When the count and the insert must act on one stable set of rows, run the transaction at SERIALIZABLE isolation:

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
START TRANSACTION;
 
SELECT COUNT(*) FROM coupon_claims
WHERE campaign_id = 42 AND status = 'claimed';
-- Application inserts only when the result is below 100.
 
INSERT INTO coupon_claims (id, campaign_id, status)
VALUES (200, 42, 'claimed');
COMMIT;

The application must handle a serialization failure by retrying the whole transaction. Do not retry only the INSERT, because the count and the insert are one decision.

Replay the coupon race with SERIALIZABLE

Start with 99 claims and a limit of 100. Both requests run the count and see 99, but the database tracks that each transaction read the predicate before writing:

1. Request A and request B both try to claim the final coupon.
2. The database cannot commit both transactions as if they ran independently. Depending on the database engine, one request waits while the other commits, or one receives a serialization error.
3. The failed request retries the complete transaction. Its new count sees 100, so it does not insert another claim.

The final state is 100 claims. The database has preserved the rule by forcing the concurrent decisions into a serial order, rather than allowing both transactions to commit from the old result of 99.

When not to use stronger isolation

SERIALIZABLE is the direct fix when the workflow requires the predicate result to remain stable, but do not raise isolation globally when only one workflow needs that guarantee. Stronger isolation can increase contention, retain more row versions, or cause transactions to wait, abort, and retry.

For a report that only needs an approximate count, READ COMMITTED may be sufficient. For a decision that authorizes a write, use SERIALIZABLE for that transaction or choose a narrower operation that protects the complete decision.

Gotchas and alternatives

Ordinary reads and locking reads can have different behavior at the same isolation level. Verify the database engine, storage engine, query type, and driver settings before relying on a specific result.

The main alternatives are:

• REPEATABLE READ: use a consistent snapshot for ordinary reads. This can prevent a transaction from seeing a later phantom, but it does not by itself enforce every cross-row business limit.
• Atomic conditional update: increment a stored campaign counter only when it is below the limit, then insert the claim only when the update affects one row.
• Predicate or range locks: prevent conflicting inserts into the searched range when supported by the database and query plan.
• One statement: combine the check and write so the database evaluates the condition and applies the change as one operation.
• Optimistic concurrency control: store a version or constraint and reject the write if another transaction changed the relevant state first.

Takeaway

A phantom read occurs when the same predicate query returns a different set of rows, so use an isolation level or database operation that matches whether the set must remain stable.