CODEWITHSUNDEEP
phpmysqlrace-condition
Wel Rachid
Wel Rachid

Reputation: 23

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:

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

Upvotes: 0

Views: 239

Answers (1)

davidbaumann
davidbaumann

Reputation: 218

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.

Upvotes: -2

Related Questions