A predicate lock protects not just existing rows but an entire logical condition (a WHERE clause predicate). While a standard row lock prevents other transactions from modifying a specific row, a predicate lock prevents them from inserting, deleting, or modifying any row that matches the predicate. This is the key mechanism for preventing phantom reads.

The Phantom Problem

Consider a transaction T1 that runs: SELECT * FROM employees WHERE dept = 'Engineering'. Under standard row-level locking, T1 acquires shared locks on every existing row where dept = 'Engineering'. But another transaction T2 can insert a brand-new row with dept = 'Engineering' — this row did not exist when T1 acquired its locks, so no lock protects it. If T1 runs the same query again, it sees the new row — a phantom.

How Predicate Locks Work

A predicate lock on dept = 'Engineering' covers the entire space of rows matching that condition, including rows that do not yet exist. Before any transaction can insert or modify a row, the lock manager checks whether the new or updated row would satisfy any active predicate lock's condition. If it does, the operation blocks or aborts.

Why Pure Predicate Locks Are Impractical

Checking whether a new row satisfies an arbitrary predicate is computationally expensive. With N active predicate locks, each insert must evaluate N conditions. Predicates can involve complex expressions (joins, subqueries, functions). In practice, databases approximate predicate locks using index-based techniques.

Gap Locks (InnoDB)

MySQL InnoDB implements an approximation called gap locks. A gap lock protects the "gap" between two consecutive index keys. For example, if the index contains keys 10 and 20, a gap lock on (10, 20) prevents any insertion of a row with a key in that range. This is cheaper than evaluating arbitrary predicates — it just requires comparing against index boundaries.

Next-Key Locks

A next-key lock in InnoDB combines a record lock (on the index record itself) with a gap lock (on the gap before the record). Together, a set of next-key locks on an index range prevents both modification of existing rows and insertion of new rows in the covered range. This is InnoDB's default locking under REPEATABLE READ for range queries.

Index-Range Locks

More generally, an index-range lock approximates a predicate lock by locking a range of index entries that covers the predicate. If the query is WHERE age BETWEEN 25 AND 35, the database locks the corresponding range in the age index. This may lock slightly more than necessary (if the index granularity does not perfectly match the predicate), but it is efficient and prevents phantoms.

When Predicate Locking Is Needed

Predicate locking (or its approximations) is only required for the SERIALIZABLE isolation level. At REPEATABLE READ, InnoDB uses next-key locks for some protection but does not fully prevent all phantoms. At READ COMMITTED, gap locks are disabled entirely.