Laravel Retry on Duplicate Key

Automatically retry non-atomic upsert operation when unique constraints are violated.

e.g. firstOrCreate() updateOrCreate() firstOrNew() + save()

Original Issue: Duplicate entries on updateOrCreate · Issue #19372 · laravel/framework

Requirements

Package	Version	Mandatory
PHP	^7.3 || ^7.4 || ^8.0	✅
Laravel	^6.0 || ^7.0 || ^8.0 || ^9.0	✅
PHPStan	>=1.1

Installing

composer require mpyw/laravel-retry-on-duplicate-key

Basic usage

The default implementation is provided by ConnectionServiceProvider, however, package discovery is not available. Be careful that you MUST register it in config/app.php by yourself.

<?php

return [

    /* ... */

    'providers' => [
        /* ... */

        Mpyw\LaravelRetryOnDuplicateKey\ConnectionServiceProvider::class,

        /* ... */
    ],

];

<?php

use Illuminate\Support\Facades\DB;

$user = DB::retryOnDuplicateKey(function () {
    // Email has a unique constraint
    return User::firstOrCreate(['email' => 'example.com'], ['name' => 'Example']);
});

OTHER	YOU
SELECT (No Results)
︙
︙	SELECT (No Results)
︙	︙
INSERT (OK)	︙
	︙
	INSERT (Error! Duplicate entry)
	Prepare for the next retry, referring to primary connection
	SELECT (1 Result)

Advanced Usage

You can extend Connection classes with RetriesOnDuplicateKey trait by yourself.

<?php

namespace App\Providers;

use App\Database\MySqlConnection;
use Illuminate\Database\Connection;
use Illuminate\Support\ServiceProvider;

class DatabaseServiceProvider extends ServiceProvider
{
    public function register(): void
    {
        Connection::resolverFor('mysql', function (...$parameters) {
            return new MySqlConnection(...$parameters);
        });
    }
}

<?php

namespace App\Database;

use Illuminate\Database\Connection as BaseMySqlConnection;
use Mpyw\LaravelRetryOnDuplicateKey\RetriesOnDuplicateKey;

class MySqlConnection extends BaseMySqlConnection
{
    use RetriesOnDuplicateKey;
}

Differences from other native upsert implementations

• [8.x] Add upsert to Eloquent and Base Query Builders by paras-malhotra · Pull Request #34698 · laravel/framework
• staudenmeir/laravel-upsert: Laravel UPSERT and INSERT IGNORE queries

These implementations are focused on atomically performing INSERT-or-UPDATE queries. Hence, there are definitely clear differences in usage.

• firstOrCreate() firstOrNew() have clear advantages if their calls complete mostly with only one SELECT and rarely with succeeding one INSERT.
  • In contrast, you must always execute two queries in all cases with upsert().
• As for updateOrCreate(), there may be considerations depending on RDBMS.
  • For RDBMS other than MySQL, updateOrCreate() would be better unless its call definitely changes field values on rows. upsert() may ruin the sticky optimization when the connection has both Reader (Replica) and Writer (Primary) because they assume that all rows narrowed by WHERE conditions have been affected.
  • In MySQL, upsert() will be efficient without any considerations in many situations. It regards that only rows are affected whose field values are actually changed.
• Be careful that upsert() never triggers Eloquent events such as created or updated because its implementation is on Eloquent Builder, not on Model.
• Only upsert() supports bulk insert. It is beneficial if there are a large number of records and you don't need any Eloquent events.