mysql - php race condition database with row entries
So im trying to figure out how to avoid a race condition. I have a product where you can make bookings on resources that are limited.
Each resource has a number of capacity. A capacity is calculated with number of rows in a database table called booking_resources where each resource booked has a row.
During code execution i do a multiple of checks for example:
- Does this person have access
- Is the resource available in the given timeframe
- Does booking options validate
- make the booking
When "making the booking" the resources needed for the booking are inserted into the booking_resources table. This makes the number of rows that has resource_id grow which in turn will make the next check on is resource available in a giving timeframe. My problem is i cannot find any documentation on what to do in this case. Should i add an extra column in my resource table and in this way lock the row? (i use innodb)
I've read something about using select ... for update.. but this doesnt make sense in my case, because i dont "cache" the number of bookings in the row. The whole idea is that i have a resource that is available 24/7, and each booking takes some of its capacity. It might have a capacity of 10 (which means that i can have 10 people using the resource at the same time). This also means that (theoretically) peoples bookings of resources can overlap as long as no more than 10 people are using it at the same time.
Hope my question gives an idea of the problem im facing and hope you guys have an elegant solution that will prevent overbooking in my application.
data structure:
bookings:
id | company_id | points | from | to | price | booking_data | booker_choises | due_order_id_datetime (when booking is deleted if not part of order) | created | updated | deleted
booking_resources:
id | booking_id | resource_id | from | to | points | created | updated | deleted
resources:
id | company_id | name | min_booking | max_bookings | min_points | max_points | created | updated | deleted
To be able to reproduce the issue i have made a javascript that clicks on the same button on the same time in two different windows. This is done by using a settimeout timer and click event (using jquery).
<script>
function syncClick(selector,time_to_activate){
date = new Date();
if(date.getTime() >= time_to_activate){
console.log('clicked');
jQuery(selector).click();
} else {
setTimeout(function(){
console.log("diff:"+(date.getTime() - time_to_activate));
syncClick(selector,time_to_activate);
},1);
}
}
</script>
Thanks. /W
Answer
Solution:
So for following case:
UserA and UserB check BookA for availability on monday. So it will show "Available" after they both check. Use a stored procedure "sp_CheckAvail" that checks availability. It should return true in both cases.
Now UserA requests to book it for monday so clicks "book". The Book will get reserved for monday.
The request should look like this:
Of cource this must all run within a transaction, so even if the click on the link at the very same moment, only one client will get the reservation.
Please understand I can't write a complete query for you. But this way it will work.