In this lesson, you will learn how Predicate Locking acts as a guardian against "phantom" data inconsistencies during range-based queries.
The Phantom Problem
Imagine you are running a report to sum the salaries of all employees in the "Engineering" department:
SELECT SUM(salary) FROM employees WHERE department = 'Engineering';
Under standard row-level locking, the database locks only the existing rows that match your criteria. But what if, while your transaction is running, another user inserts a new employee into the "Engineering" department? That new row wasn't locked because it didn't exist yet, but it now invalidates the results of your report. This surprise appearance of new data is called a phantom.
How Predicate Locking Works
Instead of locking individual rows, Predicate Locking locks the logic of your WHERE clause itself. The database doesn't just look at the rows currently in the table; it registers a lock on the criteria department = 'Engineering'.
Think of it like a "No Trespassing" sign placed on a specific set of rules. Any other transaction attempting to insert, update, or delete a row that satisfies the predicate department = 'Engineering' will be blocked until your transaction completes.
Why Use It?
By locking the criteria rather than the physical rows, the database ensures that no new data can sneak into your result set while your transaction is active. It is a pessimistic approach to concurrency:
- The Request: You perform a range scan using a specific condition.
- The Lock: The system places a lock on that logical condition.
- The Protection: Any concurrent transaction trying to write data that satisfies that condition is forced to wait.
- The Result: Your range scan remains consistent, seeing only the data that existed when you started, without interference from new insertions.
While highly effective at preventing phantoms, predicate locking can be computationally expensive because the database must check every incoming write against all active predicate locks. Consequently, many modern systems implement this via Key-Range Locking, which achieves the same goal by locking the index gaps between known rows.
Key Takeaway: Predicate locking prevents phantoms by locking the logical criteria of a query rather than individual rows, ensuring that no new data can be added to your range scan while it is in progress.