A lock, in DBMS, is a variable that is associated with a data item. Locks in DBMS help synchronize access to the database items by concurrent transactions.
Lock Based Protocol in DBMS is used to eliminate concurrency problems for simultaneous transactions, (this refers to a situation wherein one transaction results in changing the results of another transaction’s search query. The lock-based protocol comes into the picture by locking or isolating a particular transaction to a single user. Locking helps the system to appear as though all transactions appeared sequentially.
Flight seat booking
While booking a flight seat, if two users are booking the same seat at the same time there are going to be many types of conflict between the two transactions. Therefore, we need to implement a system wherein if one user has searched for existing bookings for a seat within a certain time window, another user is not allowed to concurrently insert or update another booking for the same seat and time range. (concurrently he/she can insert bookings for other seats, or for the same seat at a different time that doesn’t affect the ongoing booking.)
Implementation of Predicate Locking
Here comes into the picture predicate locking. It works similarly to the shared/exclusive lock, but rather than belonging to a particular object (e.g., one row in a table), it is common to all objects that satisfy some particular search condition(s), such as:
SELECT * FROM bookings WHERE seat_id = 543 AND
end_time > ‘2022-09-24 17:00’ AND start_time < ‘2022-09-24 18:00’;
A predicate lock works by restricting access to predicate rather than access to records, the access restrictions are as follows:
- If user user1 wants to read objects matching some condition (let’s say x conditions), like in the SELECT query, then it is required to have a predicate lock on the condition of the query, this predicate lock will be shared between the conditions (on all x conditions). If another user user2 currently has an exclusive lock on any object matching those (all x conditions) conditions, user1 will be required to wait until user2 releases its lock, and only will user1 will be allowed to make its query.
- Before user1 can insert, delete or update any object, it will be required to check whether the old or new value matched any predicate lock acquired by user2. If yes, then user1 must wait until user2 has completed or discarded its queries.
The idea of predicate lock is to also take into account those objects that might be added in the probable future, such objects are known as phantoms. In case of any matching between the predicate locks, the database will discard or stop any sort of write skew and race conditions.
Advantages of predicate locking:
- Predicate locking helps in serializable isolation.
- Provides modularity and simplicity.
Disadvantages of predicate locking:
- Very expensive to implement.