php race condition database with row entries

Question

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

well...no.. but this is only because i cannot see how the transaction would help here. do they actually lock the table itself? so only 1 thing can be inserted at a time? do i have to do an insert and afterwards "check" that it still holds and if not then make one of the transactions "rollback"? — Wel Rachid
– Wel Rachid, Commented Mar 26, 2018 at 9:44
Well if you're doing multiple database add/update/delete operations as part of one sequence, then it would be advisable in order to prevent inconsistent data if a statement fails or cannot be executed (e.g. because it fails a test such as something being available). It's not clear if that's part of your scenario but sounds like it might be. It may not be the solution to your issue but it should help in other ways. — ADyson
– ADyson, Commented Mar 26, 2018 at 9:47
Anyway you haven't really given us a truly clear picture of the situation. We can't see your actual data structure or how you calculate the capacity. But ideally if someone is enquiring about a particular period of time, you should perhaps lock that to them temporarily until they confirm. And give them a time limit until their exclusive access ends. A lot of ticket websites do this for popular events - you select a number of tickets, and then you have a few minutes to confirm the booking and make payment, before they are released back to the public again and you might lose it. — ADyson
– ADyson, Commented Mar 26, 2018 at 9:49
i'll comment on all of the things you write in a sec. Just to give a clear picture. "this" is the actually locking of the timeframe. When a booking is created you have X minutes to confirm (create the order - and make the payment).. — Wel Rachid
– Wel Rachid, Commented Mar 26, 2018 at 9:57

davidbaumann · Accepted Answer · 2018-03-26 10:52:16Z

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:

call sp_CheckAvail(bookid, $date, available); //This function will check 
                                              //wheter the book is available at the moment
                                              of the request

if available
    BEGIN
      insert into reservations(bookid, date)
      values($book, $date);  //This will write the reservation only if the book is available
    END

select available as result;

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.

I don't understand the downvoting. If there was a way to make a database constraint about the date range, I would prefer and recommend to do this. But as he needs to check that range, it's better this way. For distributed application it's important, to check in a transaction again before doing something.

Collectives™ on Stack Overflow

php race condition database with row entries

1 Answer 1

1 Comment

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Related