As you all know, the Eloquent is an ORM that is used by the Laravel framework. It has a very nice API for working with multiple database sources.
But today, I’m gonna talk about a weird behavior in Eloquent. I’ve faced a very uncommon race-condition issue in Eloquent
...OrCreate() methods, in my case, specifically the
updateOrCreate() method. The race-condition issues are very rare in PHP programming so you may not be familiar with them (then read this).
This problem still exists even in Laravel 7 (reported here). Since they have closed the origin issue, I don’t know whether it still exists in Laravel 8 or not. Let’s begin!
If you have a scenario that a new record either must be selected/created or updated (if it already exists), you’ll be faced with an
Integrity constraint violation: 1062 Duplicate entry error when your query is run in two concurrent requests. Why? We’ll see.
The problem comes from an unwise implementation in Laravel Eloquent codes. In our case, we are calling the
updateOrCreate()method on an Eloquent model which one of our fields is Unique. So for better understanding, let’s take a closer look at its codes in the
As you can see, this method calls the
tap helper function which runs the given closure (second argument) with the given value (first argument) as its parameter. So technically, it first calls the
firstOrNew() method and passes its value (which is an Eloquent model) to the closure, then inside the closure, it fills and saves the selected/created model. Nothing here. We must go deeper to see what is inside
See?! It first runs a select query to see does any record with the given attributes exists or not. If it does, it returns it. If not, it tries to create a new instance with a single parameter (which is a merged result of two
Now let’s imagine two users are sending a request at the same time. Both requests will query the database with a select statement. The select query doesn’t return any record so they both try to create a new record which is the cause of the issue. One of these two creates the record sooner and when the other one wants to do the same, MySQL says the record is duplicate and you are violating the unique integrity constraint. In such a situation, Laravel should use an
Upsert query instead of this selecting/creating approach.
How to fix it?
The solution would be using an Upsert query instead. If you are using Laravel 8 or higher, you have access to the
upsert method. If not, you must use
DB::statement('...’) syntax to run an
upsert query like this:
DB::statement("INSERT INTO books
(id, title, author, year_published)
(@id, @title, @author, @year_published)
ON DUPLICATE KEY UPDATE
title = @title,
author = @author,
year_published = @year_published;");
CAUTION: Be aware of SQL Injection attacks. Filter your external inputs before using them in a raw query.
Of course, you can make a custom abstract class for your models and override these
...OrCreate() methods with a proper implementation so you don’t have to run a raw query over and over.
If it helped, give it a clap!