Laravel Queue为何无法避免并发请求引发的MariaDB重复记录？

Hey there, let's tackle this duplicate record issue you're hitting with your Laravel API and MariaDB—even after trying queue serial processing and app-side checks, it's still popping up, right? Let's break down why the previous approaches might not have worked, and walk through some reliable fixes that don't rely on adding unique constraints to your main business tables.

• App-side duplicate checks: These run in your application's memory, so concurrent requests can both pass the check at the exact same time before either has written to the database.
• Laravel Queues (if misconfigured): If you're running multiple queue workers for the same queue, they'll still process jobs in parallel. Serial processing only happens if you limit the queue to a single worker.

1. Database Pessimistic Locking with Transactions #

This uses MariaDB's row-level locking to ensure only one request can check for and create the record at a time. Wrap your logic in a transaction and use lockForUpdate() to lock the rows you're querying:

DB::transaction(function () use ($requestData) {
    // Lock the potential existing record so other requests wait
    $existingRecord = YourModel::where('your_unique_identifier', $requestData['identifier'])
        ->lockForUpdate()
        ->first();

    if (!$existingRecord) {
        YourModel::create($requestData);
    }
});

• The lock only holds for the duration of the transaction, so it won't block other operations unnecessarily.
• Works well for single-server setups, and plays nicely with MariaDB's InnoDB engine (which supports row-level locking).

2. Dedicated Lock Table with Atomic Inserts #

If you can't add unique constraints to your main table, create a lightweight lock table to act as a gatekeeper. The lock table will use a unique constraint to ensure only one request can "claim" the right to create the record:

First, create a migration for the lock table:

Schema::create('record_creation_locks', function (Blueprint $table) {
    $table->string('identifier')->primary(); // Use your unique field value here
    $table->timestamp('expires_at'); // Clean up stale locks automatically
});

Then use this in your business logic:

try {
    DB::transaction(function () use ($requestData) {
        // Try to insert a lock - the unique constraint will fail if another request is already processing
        DB::table('record_creation_locks')->insert([
            'identifier' => $requestData['identifier'],
            'expires_at' => now()->addMinutes(5) // Prevent permanent locks if a request fails
        ]);

        // Double-check for existing records (extra safety)
        $existingRecord = YourModel::where('your_unique_identifier', $requestData['identifier'])->first();
        if (!$existingRecord) {
            YourModel::create($requestData);
        }

        // Release the lock immediately after success
        DB::table('record_creation_locks')->where('identifier', $requestData['identifier'])->delete();
    });
} catch (\Illuminate\Database\QueryException $e) {
    // Catch the unique constraint violation - another request is handling this
    return response()->json([
        'message' => 'This record is already being created. Please try again shortly.'
    ], 409);
}

• Add a scheduled command to clean up expired locks daily to keep the table small.

3. Redis Distributed Lock (For Multi-Server Setups) #

If your app runs on multiple servers, a database-level lock might not cover all cases. Use Laravel's built-in Redis cache locks to create a distributed lock that works across all your servers:

$lockKey = "create-record-{$requestData['identifier']}";
$lock = Cache::lock($lockKey, 10); // Lock for 10 seconds to prevent deadlocks

try {
    // Wait up to 2 seconds for the lock (adjust as needed)
    $lock->block(2);

    DB::transaction(function () use ($requestData) {
        $existingRecord = YourModel::where('your_unique_identifier', $requestData['identifier'])->first();
        if (!$existingRecord) {
            YourModel::create($requestData);
        }
    });
} catch (\Illuminate\Contracts\Cache\LockTimeoutException $e) {
    // Return a rate-limited response if the lock is held too long
    return response()->json([
        'message' => 'Too many concurrent requests. Please try again later.'
    ], 429);
} finally {
    // Always release the lock, even if something fails
    optional($lock)->release();
}

• Make sure your Laravel cache is configured to use Redis for this to work.

4. Fix Your Queue Configuration #

If you want to stick with queues, ensure you're running only one worker for the queue handling record creation. Multiple workers will process jobs in parallel, leading to duplicates even if jobs are queued serially.

Start the queue worker with:

php artisan queue:work --queue=record-creation --workers=1 --timeout=60 --tries=3

• Assign record creation jobs to a dedicated queue (record-creation) so you don't block other queue tasks.
• Add the lockForUpdate() logic inside your queue job's handle() method for extra safety, in case the queue ever ends up with multiple workers by mistake.

The key here is to move the duplicate check to a layer that can enforce atomicity—either the database or a distributed lock system. App-side checks alone can't handle true concurrency because they don't coordinate across multiple request processes.

内容的提问来源于stack exchange，提问作者sunomad