[x] Generate random sequences of bookings and test that, when applied serially, the bookings logic does not allow two bookings for any date.
[x] Generate random sequences of bookings and test that, when applied in parallel with multiple threads, the bookings logic does not allow two bookings for any date and doesn't get stuck (no deadlocks).
[x] Fix the query deadlock issue that the parallel test revealed.
This PR changes how we acquire row write locks when inserting new bookings. We need a lock to prevent concurrent inserts from creating overlapping bookings. First implementation tried to acquire write locks directly to booking rows. However, the bookings are selected by a set of and and or conditions on start and end times. This resulted in deadlocks when two competing inserts acquired gap locks to same rows but then prevented each other completing the select.
The implementation is now changed to simply acquire a lock on the bookable row instead. The lock is acquired by primary key (bookable id) so it is precise and only locks a single row in index vs the previous gap locks that resulted from range queries. This strategy will also work well later when we add allows, blocks and schedules to bookables that need to be taken into account when calculating if a given timeslot is available for booking. So all in all, this is a win for simplicity and also effective solution.
This PR changes how we acquire row write locks when inserting new bookings. We need a lock to prevent concurrent inserts from creating overlapping bookings. First implementation tried to acquire write locks directly to booking rows. However, the bookings are selected by a set of and and or conditions on start and end times. This resulted in deadlocks when two competing inserts acquired gap locks to same rows but then prevented each other completing the select.
The implementation is now changed to simply acquire a lock on the bookable row instead. The lock is acquired by primary key (bookable id) so it is precise and only locks a single row in index vs the previous gap locks that resulted from range queries. This strategy will also work well later when we add allows, blocks and schedules to bookables that need to be taken into account when calculating if a given timeslot is available for booking. So all in all, this is a win for simplicity and also effective solution.