0

I am creating a module wherein customers (all customers, not individual) should only be able to book a schedule of the same date to a maximum of 5 times in 2 tables, namely acceptance_bookings, and turnover_bookings.

For example, 5 customers booked a schedule of the same date (07-17-2019), 3 of which are acceptance_booking, and 2 turnover_booking. If other customers have attempted to book on the same date, they should be rejected.

Currently my implementation would look like the following:

** START OF TRANSACTION **

** CODE FOR INSERTING ACCEPTANCE OR TURNOVER BOOKING ** 

$acceptance_bookings = AcceptanceBooking::selectRaw("DATE(date) as date")
            ->havingRaw("DATE(date) = '$dateString'")
            ->get()
            ->count();

$turnover_bookings = TurnoverBooking::selectRaw("DATE(date) as date")
            ->havingRaw("DATE(date) = '$dateString'")
            ->get()
            ->count();

$total_count = $acceptance_bookings + $turnover_bookings;

if($total_count > 5) 
    ** ROLLBACK THE DB TRANSACTION

** END OF TRANSACTION

I am worried that in race conditions, there is a chance that both inserts will get past the validation check at the end of the code which checks if there are more than 5 entries to that date.

In my implementation (code above), I insert the booking before I check if it actually is already past limit, and roll back the transaction if it is past 5 entries. Placing the condition before the insert would probably yield the same result in a race condition.

What is the best way to handle this scenario to avoid race conditions? The strict requirements I want to maintain is that there should never be more than 5 entries of the same booking schedules across the 2 tables.

Note: I cannot change the tables, like trying to create a booking table and have a reference ID to determine if it is an acceptance or turnover booking as I am integrating an already working system.

Rick
  • 2,003
  • 4
  • 16
  • 28
  • 1
    See [Why should I provide a Minimal Reproducible Example for a very simple SQL query?](https://meta.stackoverflow.com/questions/333952/why-should-i-provide-a-minimal-reproducible-example-for-a-very-simple-sql-query) for example data and expected results.. We need to know how the tables look like `SHOW CREATE TABLE table` for every table involved in the question/query and we need to know how the data looks like. – Raymond Nijland Aug 18 '19 at 00:05
  • But maybe something like `INSERT INTO SELECT FROM DUAL WHERE 5 <= (SELECT COUNT(*) FROM table WHERE DATE(date) = '')` ? **edit:** you could union it also if you need to check two tables.. – Raymond Nijland Aug 18 '19 at 00:07
  • https://stackoverflow.com/questions/45073861/how-to-limit-number-of-rows-can-be-store-in-mysql-table/45076505#45076505 – Strawberry Aug 18 '19 at 00:13
  • https://stackoverflow.com/questions/45523186/limit-number-of-patients-by-appointment-and-type-of-exam-php-mysql/45523521#45523521 – Strawberry Aug 18 '19 at 00:18
  • Thanks for the suggestions, I will be looking into it – Rick Aug 18 '19 at 00:19
  • @Rick Check out this answer to another question to understand one way to handle race conditions in MySQL: https://stackoverflow.com/a/48629009/1797579 – Willem Renzema Aug 18 '19 at 02:36
  • @WillemRenzema I can see that he uses locks. Does that affect performance? – Rick Aug 18 '19 at 02:51
  • I have tried creating a procedure to check if the insert date is already exceeding limit. Is it safer than doing the validation on laravel? I'm not sure if MySQL does things concurrently. But if it does not, Maybe doing the insert by using a stored procedure is safer. – Rick Aug 18 '19 at 02:53
  • As demonstrated, no procedure required – Strawberry Aug 18 '19 at 08:53

1 Answers1

0

Your code has the right idea; what is the problem? BEGIN at the start and COMMIT at the end (if not failure).

One thing more: The SELECT needs to be SELECT .. FOR UPDATE. This "locks" the rows that you are counting --> avoids race problem.

Rick James
  • 135,179
  • 13
  • 127
  • 222
  • My code is in a transaction so that should be fine. Do I really need to lock the selected rows? Because those values will almost never be updated. What I'm worried about are race conditions. If there is only one slot left for a date, and multiple users book them at the same time, I think there's a possibility that they could get past these validations. – Rick Aug 18 '19 at 13:02
  • @Rick - See https://stackoverflow.com/a/48629009/1766831 for an excellent discussion of `FOR UPDATE`. – Rick James Aug 18 '19 at 17:22