A transaction can lock every row it reads and still leave a business rule exposed. The gap appears when the rule is about a set described by a predicate, not only the rows that currently satisfy it.
Suppose an application limits a small allocation group to four active reservations. A transaction queries the active rows, sees three, and decides that one more reservation is valid. If another transaction inserts a new matching row before the first transaction commits, both decisions may have been based on a set that no longer represents the committed state.
The new row is a phantom: it was absent from an earlier evaluation of the predicate and appears in a later evaluation or in the final state. The important engineering point is broader than repeated reads. A concurrency rule expressed as “all rows matching this condition” needs protection for the condition itself, or an equivalent mechanism that makes conflicting changes detectable.
A row lock protects rows that exist
Consider a reservation table:
CREATE TABLE reservations (
id BIGINT PRIMARY KEY,
zone TEXT NOT NULL,
active BOOLEAN NOT NULL
);An application permits at most four active reservations in zone A. The relevant state is not one particular row. It is the result of a predicate:
SELECT COUNT(*)
FROM reservations
WHERE zone = 'A'
AND active = true;Assume the query returns 3. Two concurrent transactions can each observe that result and each insert a new reservation.
T1 T2
-----------------------------------------------
count active in A = 3
count active in A = 3
insert reservation 104
insert reservation 105
commit commitIf the isolation level permits both transactions to commit, the final count is 5. Each insert touched a different row, so conflict detection based only on overlapping row writes has no reason to reject either transaction.
Locking the three rows returned by the query does not necessarily close the gap. The conflicting operation is an insertion of a row that did not exist when those locks were acquired. No lock on an existing reservation can literally identify that future row.
This is the boundary between row identity and predicate membership.
The predicate is part of the shared state
It is tempting to describe a phantom as a read anomaly: execute a query twice and obtain a different set of rows. That description is accurate for the observable symptom, but it can hide the design issue.
For an invariant such as “no more than four active reservations in zone A,” the shared state includes a fact derived from a set:
count(rows where zone = A and active = true) <= 4An insert, delete, or update can change membership in that set. Changing zone from B to A matters just as much as inserting a new row. Changing active from false to true does too.
The concurrency mechanism therefore has to account for operations that alter the truth of the predicate. Protecting only the values already returned by a query is narrower than protecting the invariant.
This distinction also separates phantom problems from lost updates. A lost update commonly involves two writers targeting the same logical item. Version columns or conditional updates can detect that direct collision. Phantom-sensitive invariants can fail even when the transactions write disjoint rows.
Repeatable rows are not necessarily a stable set
Isolation terminology can obscure this point because database products implement isolation levels with different mechanisms. The SQL standard describes phenomena that isolation levels must prevent, while specific engines may use locks, multiversion concurrency control, serialization checks, or combinations of these techniques.
A transaction that receives a stable snapshot may execute the same query twice and see the same rows even while another transaction commits a matching insert. From the transaction’s local view, no phantom appears during those reads. Yet a rule derived from that snapshot can still conflict with a concurrent rule derived from another snapshot.
That is the same structural risk visible in write skew: transactions make individually valid decisions from views that cannot all be accepted while preserving a cross-row invariant.
The practical question is not merely whether a second SELECT returns an extra row. It is whether the database can accept a concurrent history whose committed result violates the predicate-based rule.
Serializable isolation addresses that broader question. Its contract is that committed transactions have an outcome equivalent to some serial execution. If no serial ordering can produce the concurrent result while respecting the transactions’ observed conditions, at least one transaction must not commit as originally attempted.
Range protection makes absence lockable
Lock-based database engines can prevent phantoms by protecting key ranges rather than only existing records. The exact lock type and behavior are engine-specific, but the general mechanism is concrete.
Suppose an index orders entries by (zone, active). A query for active rows in zone A corresponds to a range in that index. A range-oriented lock can protect not just keys already present but also positions into which another matching key would be inserted.
Conceptually:
index order
(A,false) ... | (A,true) ........ | (B,false) ...
^ protected range ^An insertion that would land inside the protected range conflicts with the range protection. The database has turned an absence into something concurrency control can represent.
This depends on the database engine, isolation level, access path, and lock implementation. It is unsafe to infer exact range-lock behavior from generic SQL syntax alone. A SELECT ... FOR UPDATE, for example, does not have identical phantom-prevention semantics across database systems and isolation modes.
The general lesson is narrower: predicate protection requires a mechanism capable of representing potential membership changes, not just locks on rows already found.
Multiversion systems can detect conflicts instead
Multiversion concurrency control changes the implementation shape. Readers can often operate on snapshots without blocking writers, so protecting a predicate does not always mean holding a physical range lock for the duration of the transaction.
A serializable multiversion system can track dependencies among transactions and reject a transaction when accepting all observed dependencies would permit a non-serializable history. The application-visible result is commonly a serialization failure that requires the transaction to be retried from a fresh state.
The mechanism matters because application code must treat serialization failure differently from a permanent validation error. The database is not saying that the requested state is intrinsically invalid. It is saying that this attempt cannot be committed consistently with the concurrent history.
A retry re-evaluates the predicate against a new transactional view. In the reservation example, the retried transaction may now observe four active rows and decline to insert another one.
Serializable isolation therefore moves conflict detection into the database, but it does not remove the need for application policy. The application still defines the invariant and decides what to do after the transaction is restarted and the current state is observed.
A constraint is stronger when the rule can be encoded directly
Concurrency control is not the only place to enforce a predicate-based invariant. If a database constraint can express the rule directly, the constraint often provides a more local statement of valid committed state.
Some rules map naturally to uniqueness. A system that permits one active lease per resource can sometimes model active ownership so that a unique constraint rejects a second conflicting row. The database then does not need application code to count rows before deciding whether an insert is valid.
Other rules, such as an arbitrary maximum count across a predicate, are not generally expressible as a simple SQL CHECK constraint because such constraints commonly evaluate values within one row rather than running arbitrary cross-row queries. Available options depend on the database product and schema design.
A useful distinction is:
state constraint:
this committed representation must be valid
transaction decision:
this operation is valid given the state I observedWhen the first form can encode the actual rule, it narrows the number of execution histories the application must reason about. When it cannot, transaction isolation and explicit conflict handling become more important.
Materializing the contested fact changes the conflict shape
Some predicate rules can be transformed into direct row conflicts by materializing the fact that transactions compete over.
Instead of deriving capacity only by counting reservation rows, a schema might maintain a row for each zone with an explicit allocation counter:
zone_capacity
-------------------------
zone | active | limit
A | 3 | 4A reservation transaction that increments active now writes the same capacity row as other competing transactions. A conditional update can make the limit part of the write:
UPDATE zone_capacity
SET active = active + 1
WHERE zone = 'A'
AND active < 4;If exactly one row is updated, the capacity claim succeeded. If zero rows are updated, no capacity was available at the moment the statement executed.
This model trades a predicate conflict for contention on a shared row. It also introduces a consistency obligation between the counter and the reservation records. The design is sound only if the updates that must agree are kept within an appropriate atomic boundary or are reconciled under an explicitly weaker model.
Materialization is therefore not a universal improvement. It changes where the concurrency conflict occurs and which state must remain synchronized.
Indexes affect execution, not the logical invariant
Predicate-oriented concurrency is often discussed beside indexes because range-lock implementations operate through index structures and query plans. That connection can lead to an incorrect conclusion: that adding an index by itself makes the invariant safe.
An index changes how the database locates rows. It does not, on its own, establish serializable semantics for the application transaction.
In a lock-based implementation, the selected index and access path can affect which keys or ranges are locked. In a multiversion implementation, indexes can affect execution without being the mechanism that establishes serializability. These details are database-specific and need verification against the engine’s documentation.
The invariant remains logical:
the committed set matching predicate P must satisfy rule RPhysical access structures support the database mechanism, but they do not replace the isolation contract.
Empty sets expose the issue most clearly
The sharpest phantom case is often a query that returns nothing.
Suppose a transaction checks for an active job with a particular external identifier:
SELECT id
FROM jobs
WHERE external_id = 'evt-83'
AND active = true;The result is empty. There is no returned row to lock. Two transactions can both observe absence and both decide to create a row unless some other mechanism makes those operations conflict.
If the actual invariant is uniqueness, a unique constraint on an appropriate representation is a direct answer. If the invariant is more complex, serializable isolation, range protection, or a deliberately materialized coordination record may be required.
Absence is data from the application’s perspective. Concurrency control has to represent that absence somehow when future writes are not allowed to invalidate the decision based on it.
Treat set membership as a first-class concurrency boundary
Row-level reasoning works well when correctness is attached to identifiable records: update this account only if its version is still 12; claim this task only if its status is still ready. It becomes incomplete when correctness depends on a set whose membership can change concurrently.
The engineering boundary is the predicate. Inserts, deletes, and updates that cross that boundary can invalidate decisions even without touching any row previously read or written by another transaction.
There are several legitimate ways to close the gap: a database constraint that directly represents valid state, serializable transactions that reject incompatible histories, range-oriented locking where the engine provides the required semantics, or a schema that materializes the contested fact into a direct write conflict. Each option moves the protection to a different layer.
The important property is the same in every case. A transaction that relies on the absence of a matching row, or on an aggregate over a matching set, needs a concurrency mechanism whose scope reaches beyond the rows that happened to exist when the query ran.