I have a pretty straightforward booking system, which uses a Reservations table.
Only one reservation is allowed to exist with the same reservation_date, reservation_hour, and status = 1, but there could be more records with status = 0.
The reservation process is done in a single method, it first selects the existing records with above conditions. So if there is any existing reservation already, the system won’t let you complete yours.
When multiple users attempt to book for the same resource at the same time, it’s no surprise sometimes I end up with duplicate “active” (i.e. status = 1) reservations.
I read several questions but couldn’t come up with a definitive solution. Where and how is the right place to handle this?
Unfortunately I cannot set a constraint in my table since it relies on the conditional status field value.
And about using DB::Transaction, I don’t see how it could help at all when the race condition is present.
