Be part of JetBrains PHPverse 2026 on June 9 – a free online event bringing PHP devs worldwide together.

MichMich's avatar

Looking for a performant way to prevent a race condition.

I built and manage an application which is slowly getting a lot of traction. One particular piece of the application is turning into a performance bottle neck, so I'm trying to find out if there is a better or more perfomant way to solve my issue.

The particular piece of code is responsible for turning a string into a series of sections. For example: this/is/a/section would turn into 4 sections. IF they don't allready exist. If they do exsist then it will just return the existing sections. The mentioned example would tun in the following 4 sections:

[id => 1, name => "this", parent_section_id => null],
[id => 2, name => "is", parent_section_id => 1],
[id => 3, name => "a", parent_section_id => 2],
[id => 4, name => "section", parent_section_id => 3]

To accomplish this, I use the following code:

$path = "this/is/a/section";

collect(explode('/', $path))
  ->reduce(function ($parentSection, $sectionName) {
    return Section::firstOrCreate([
      'name' => $sectionName,
      'parent_section_id' => $parentSection ? $parentSection->id : null
    ]);
  }, null);

The race condition I ran into is that if there are multiple proccess running at the same time, it sometimes happened that a section was created twice. This is because the firstOrCreate does two queries: one to check if the section exists, and another to create it if it doesn't. If another process would run the same code just after the first query was run, they both would "think" there wasn't an exsisting section already, which resulted in the section being created twice.

To solve this, I'm currently using an atomic lock:

Cache::lock('SECTION_CREATION_LOCK', 10)->block(5, function () {
  // the code block mentioned earlier will be run inside this lock.
});

If i'm not mistaken, this will try for 5 seconds to create a (max) 10 second lock. During this lock, all other processes will wait until the lock is released.

Unfortunately this is a huge performance bottleneck which also causes a lot of lock exceptions due to the timeout.

Is there any cleaner way to prevent the race condition? Or is there a better way to use the locking mechanism? Any help is highly appriciated.

0 likes
12 replies
rodrigo.pedra's avatar

Aren't database transactions more suitable for what you are describing?

Also consider using ->lockForUpdate() while inside the transaction, something like this:

<?php // ./routes/console.php

use App\Models\Section;
use Illuminate\Support\Facades\Artisan;
use Illuminate\Support\Facades\DB;
use Illuminate\Support\Str;

Artisan::command('test', function () {
    $path = "this/is/a/section";

    DB::transaction(function () use ($path) {
        Str::of($path)->explode('/')->reduce(function ($parent, $name) {
            return Section::query()->lockForUpdate()->firstOrCreate([
                'name' => $name,
                'parent_section_id' => $parent?->id,
            ]);
        });
    });
});

references:

2 likes
rodrigo.pedra's avatar

One more thing: congrats on your product gaining traction!

I always say these kind of issues, due to growth, are the best ones to have =)

1 like
MichMich's avatar

@rodrigo.pedra As far as I know database transactions have an other purpose: to roll back all the changes if on if the queries fails. So this wont prevent the issue unless the insert would throw an error. Unfortunately I can't use "unique" columns since there are some other fields that dictate weather or not an exsisting record is the one I'm looking for. I left this out of my example to keep things simple.

I did found out MySQL has locking features built in. That might be a bit more performant.

1 like
rodrigo.pedra's avatar

@MichMich

As far as I know database transactions have an other purpose: to roll back all the changes if on if the queries fails

This is partially true. Indeed transactions allows to rollback changes if one DML statement fails. But they are also meant to ensure a series of DML statements are executed as a single unit.

reference: https://dev.mysql.com/doc/refman/8.0/en/glossary.html#glos_transaction

For completeness DML (Data Manipulation Language) usually refers to INSERT, UPDATE, and DELETE statements. "Usually" because Some DBMS might provide additional statements -- such as SQL Server's MERGE.

This is useful to ensure consistency, for example in a banking application, when a user sends money to another, you want to ensure both the UPDATE statement that updates the senders' balance, and also the UPDATE statement that updates the receiver's balance are executed as a single unit, or fail as a single unit.

If you could enforce a unique key, even if it was a composite key, when you insert a new record to a table, any other transaction trying to also insert a new record, would wait for the first transaction to end (either with a commit, or a rollback), as they can't be sure that duplicate values would be inserted into the unique key.

As you don't, or can´t have unique keys, I would either try to use an "UPSERT" as suggested by @snapey , or go with a table lock, like this:

<?php // ./routes/console.php

use App\Models\Section;
use Illuminate\Support\Facades\Artisan;
use Illuminate\Support\Facades\DB;
use Illuminate\Support\Str;

Artisan::command('test', function () {
    $path = "this/is/a/section";
    
    try {
        DB::statement('LOCK TABLES `sections` WRITE');

        Str::of($path)->explode('/')->reduce(function ($parent, $name) {
            return Section::query()->firstOrCreate([
                'name' => $name,
                'parent_section_id' => $parent?->id,
            ]);
        });
    } finally {
        DB::statement('UNLOCK TABLES');
    }
});

By locking the table, any other lock request, and any DML request, will wait until the first acquired lock is released.

And as you locked it for write, any other process that tries to read the table will succeed without a problem.

reference: https://dev.mysql.com/doc/refman/8.0/en/lock-tables.html

MichMich's avatar

@Snapey Upsert would be the perfect solution If I could use an "unique" column. Unfortunately I can't use "unique" columns since there are some other fields that dictate wether or not an existing record is the one I'm looking for. I left this out of my example to keep things simple.

I'm gonna try and see wether or not native MySQL locking is more performant than Laravel's built in lock.

Snapey's avatar

@MichMich if you created a unique index for the combination of columns, then wrap the insert in a try catch block?

You would create the record if it didn't exist or catch and ignore the error if it did

Snapey's avatar

@MichMich but the problem as described is that you need to first query the database to see if the record exists and if not, then create it. This can lead to a race condition where two seperate threads can both think there is no existing record and go on to create one.

But now you say that duplicated records are acceptable.

Perhaps the problem is not sufficiently defined?

MichMich's avatar

@Snapey Yeah, I agree. I think I need to write out all the scenarios in which an error should be thrown and when duplicates are permitted. This will probably point me to the proper direction.

Thanks for being my rubber ducky.

1 like

Please or to participate in this conversation.