php – How to lock database for Laravel's `firstOrCreate`?-ThrowExceptions

Exception or error:

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.

How to solve:

Leave a Reply

Your email address will not be published. Required fields are marked *