Isn't weird to get unique constraint violation when using updateOrCreate method?

[foremtehan]

I'm using updateOrCreate in some job workers like this:

public function handle()
{
    Model::query()->updateOrCreate(['video_id' => $videoId], compact('status'));
}

I've set up 3 queue workers in supervisor.

However, sometimes (not always), I get this error:

PDOException: SQLSTATE[23505]: Unique violation: 7 ERROR: duplicate key value violates unique constraint "posts_notified_videos_video_id_unique"

Isn't that strange? Shouldn't updateOrCreate check if the record exists before trying to create it? My guess is that the 3 processes might be attempting to create the same record simultaneously. How can I prevent this? Do I need to handle it myself, or does laravel have a built-in solution for this kind of issue?

[shaedrich]

You could try

• ShouldBeUnique or ShouldBeUniqueUntilProcessing
• WithoutOverlapping

[foremtehan]

They are separate jobs with different constructor arguments. Each job notifies users with a different user id but the same video_id . I’m not sure if I need to make them unique, or else..?

[shaedrich]

Then, maybe WithoutOverlapping is more helpful then

[shaedrich]

Maybe, it's better to use a single, atomic operation than one UPDATE call and then one CREATE call. You could try upserting:

public function handle()
{
    Model::query()->upsert(['video_id' => $videoId, 'status' => $status], ['video_id'], ['status']);
}

[ghost]

Or he can implement a retry to fetch the row on that error with a try catch.