Laravel 5.1 entering multiple duplicate records into the database

Question

I have written an application using Laravel 5.1. The application is for parking spaces. It "provisions" the spaces by creating 3 different available slots in the database (Morning, Afternoon, All Day).

The problem is that on a quick page refresh, the spaces are provisioned twice, resulting in 6 slots getting entered into the database.

However, if I refresh the page after a 5 second delay, no further records are entered. My if statement below seems to work, just not when the page / connection is refreshed quickly.

foreach($bays as $bay) {
    if(!BookingDates::where('date', $date)->where('parking_bay', $bay->number)->count()) {
        BookingDates::insert(['parking_bay' => $bay->number, 'date' => $date, 'slot' => 'Morning', 'time' => '7am - 1pm', 'status' => 'Available', 'created_at' => DB::raw('now()')]);
        BookingDates::insert(['parking_bay' => $bay->number, 'date' => $date, 'slot' => 'Afternoon', 'time' => '1pm - 7pm', 'status' => 'Available', 'created_at' => DB::raw('now()')]);
        BookingDates::insert(['parking_bay' => $bay->number, 'date' => $date, 'slot' => 'All Day', 'time' => null, 'status' => 'Available', 'created_at' => DB::raw('now()')]);
    }
}

Does anyone know why this might happen?

Answer 1

When dealing with this sort of issue, database transactions often turn out to be part of the solution. The code will look something like this:

DB::transaction(function () use ($bays, $date){
    foreach($bays as $bay) {
        if(!BookingDates::where('date', $date)->where('parking_bay', $bay->number)->count()) {
            BookingDates::insert(['parking_bay' => $bay->number, 'date' => $date, 'slot' => 'Morning', 'time' => '7am - 1pm', 'status' => 'Available', 'created_at' => DB::raw('now()')]);
            BookingDates::insert(['parking_bay' => $bay->number, 'date' => $date, 'slot' => 'Afternoon', 'time' => '1pm - 7pm', 'status' => 'Available', 'created_at' => DB::raw('now()')]);
            BookingDates::insert(['parking_bay' => $bay->number, 'date' => $date, 'slot' => 'All Day', 'time' => null, 'status' => 'Available', 'created_at' => DB::raw('now()')]);
        }
    }
});

To really ensure data integrity, you'll also want to define a unique index across the columns parking_bay, date, and slot. You may need to step outside the migration API and just execute a SQL statement in your migration, which will vary based on whether you are using MySQL, MSSQL, Postgres, etc.

Between transactions and a unique index, the database will refuse to insert duplicate rows and will rollback the bad insert, so you won't be stuck with something like one person having the morning slot and another having the all day slot.

Let me know if you need any further help!