Use Predicate Locks to eliminate write skews

Write Skews can be particularly tricky to catch and resolve under concurrent conditions. Traditional ways of preventing race conditions like Atomic Writes and Explicit Locks do not work in the case of write skew because the problem here is being caused by Phantoms - we need to lock objects that don't exist yet.

To truly prevent write skews, the database has to safeguard against writes/updates into non-existing rows. In the meeting room booking example, books don't yet exist in the database when two transactions insert records simultaneously.

This means that if one transaction has searched for existing bookings for a room within a certain time window, another transaction should not be allowed to concurrently insert or update another booking for the same room and time range.

A Predicate Lock can help lock non-existent objects.

A predicate lock is a lock that belongs to all objects that match some search condition, like:

SELECT * FROM bookings
    WHERE booking_date = '2021-02-21'
    AND start_time >= '2018-01-01 09:00';
    AND end_time <= '2018-01-01 09:30'
    AND status = 'ACTIVE';

The rules of access restriction are simple:

• If a transaction wants to read objects matching some condition, it must acquire a shared-mode predicate lock on the query's conditions. It has to wait if another transaction has an exclusive lock on any object matching the same conditions.
• If a transaction wants to insert, update, or delete an object, it must first check whether the old or new value matches any active predicate locks. If yes, the transaction must wait until the other transaction has been committed or aborted before it can continue.

Put together, these rules ensure that locks apply even to non-existent objects, which might be added in the future. By preventing all forms of write skew and other race conditions, the database's isolation becomes serializable.