How to lock database for Laravel's `firstOrCreate`?

Question

We currently encounter a Duplicate entry QueryException when executing the following code:

Slug::firstOrCreate([
  Slug::ENTITY_TYPE => $this->getEntityType(),
  Slug::SLUG        => $slug
], [
  Slug::ENTITY_ID   => $this->getKey()
]);

Since the firstOrCreate method by Laravel first checks if the entry with the attributes exist before inserting it, this exception should never occur. However, we have an application with million of visitors and million of actions every day and therefore also use a master DB connection with two slaves for reading. Therefore, it might be possible that some race conditions might occur.

We currently tried to separate the query and force the master connection for reading:

 $slugModel = Slug::onWriteConnection()->where([
     Slug::SLUG        => $slug,
     Slug::ENTITY_TYPE => $this->getEntityType()
 ])->first();

 if ($slugModel && $slugModel->entity_id !== $this->getKey()) {
    $class = get_class($this);
    throw new \RuntimeException("The slug [{$slug}] already exists for a model of type [{$class}].");
}

if (!$slugModel) {
   return $this->slugs()->create([
         Slug::SLUG        => $slug,
         Slug::ENTITY_TYPE => $this->getEntityType()
   ]);
}

However the exception still occurs sometimes.

Our next approach would be to lock the table before the reading check and release the lock after the writing to prevent any inserts with the same slug from other database actions between our reading and our writing. Does anyone know how to solve this? I don`t really understand how Laravel's Pessimistic Locking can help solving the issue. We use MySql for our database.

I don't think Pessimistic locking can solve your issue. These type of locking is used to lock the resources being updated from multiple connections. Also, since your traffic is very high I don't recommend using table locking without proper research on the impact of it on your system. [pro and cons of locking table](https://www.databasejournal.com/features/mysql/the-pros-and-cons-of-mysql-table-locking.html) [optimistic-vs-pessimistic-locking](https://stackoverflow.com/questions/129329/optimistic-vs-pessimistic-locking) — Anuj Shrestha, Mar 17 '20 at 10:12
I'm still learning about database concurrency issue myself, but from reading "Designing Data-Intensive Applications", the phenomenon you're experiencing is called "write skew." The author says: "Automatically preventing write skew requires true serializable isolation." IMO before locking or setting a new isolation level, it might be worthwhile considering how your application could handle duplicates. If the slugs are auto-generated, you could handle the exception and generate a new one. If they are user-defined, users could "pre-reserve" slugs in a different db table or an in-memory database. — D Malan, Mar 17 '20 at 11:16
Thanks for your responses! We will try to handle the exceptions and generate a new one for these cases. Thank you! — Ferdinand Frank, Mar 17 '20 at 13:56

score 2 · Answer 1 · answered Sep 02 '21 at 20:10

I would not recommend to lock the table, especially if you have millions of viewers.

Most race-conditions can be fixed by locks, but this is not fixable with locks, because you cannot lock a row that does not exist (there is something like gap locking, but this won't help here.).

Laravel does not handle race-conditions by itself. If you call firstOrCreate it does two queries:

SELECT item where slug=X and entity_type=Y
If it does not exists, create it

Now because we have two queries, race condition is possible, meaning two user in parallel reach step 1, then both try to create the entry in step 2 and your system will crash.

Since you already have a Duplicate Key error, it means you aleady put a unique constrain on the tuple on the two columns that identify your row, which is good.

What you could do now, is to catch the duplicate key error like this:

try{
    $slug = Slug::firstOrCreate([
  Slug::ENTITY_TYPE => $this->getEntityType(),
  Slug::SLUG        => $slug
], [
  Slug::ENTITY_ID   => $this->getKey()
]);
}
catch (Illuminate\Database\QueryException $e){
    $errorCode = $e->errorInfo[1];
    if($errorCode == 1062){
        $slug = Slug::where('slug','=', $slug)->where('entity_type','=',  $this->getEntityType())->first();
    }
}

[mpyw/laravel-retry-on-duplicate-key: Automatically retry non-atomic upsert operation when unique key constraints are violated.](https://github.com/mpyw/laravel-retry-on-duplicate-key) — mpyw, Nov 22 '21 at 05:17
I know, but this the definitely different approach. Please see the bottom of README. — mpyw, Nov 23 '21 at 00:07

score 0 · Answer 2 · answered Jan 05 '21 at 17:46

0

one solution for this is to use Laravel queue and make sure that it runs one job at a time, in this way you will never have 2 identical queries at the same time.

for sure this will not work if you want to return back the result in the same request.

answered Jan 05 '21 at 17:46

shamaseen

2,193
2
21
35

score 0 · Answer 3 · answered Sep 02 '23 at 06:07

This has been completely resolved since Laravel 10.22.0, Please read it through translation: [Laravel] createOrFirst の登場から激変した firstOrCreate, updateOrCreate に迫る！

If retry processing under unique key constraints is not sufficient: mpyw/laravel-database-advisory-lock: Advisory Locking Features for Postgres/MySQL/MariaDB on Laravel

How to lock database for Laravel's `firstOrCreate`?

3 Answers3