eloquent - Laravel 10.43.0: Prevent concurrent requests from updating the same row - Stack Overflow

admin管理员组

文章数量:1242790

I am implementing an API endpoint that allows our users to request a resource from our inventory table. For simplicity, let's say that the table has a boolean available column. The idea is that this endpoint will select the first row from the inventory table where available is true, and update that row with the appropriate account ID and setting available to false. So it would look something like this:

public function assignItemToAccount(Account $account): Inventory {
    $item = Inventory::where('available', true)->first();

    $item->update([
        'account_id' => $account->id,
        'available' => false
    ]);

    return $item->fresh();
}

The problem I'd like to avoid is, since I am using ->first(), concurrent requests to this endpoint would want to assign the same item from inventory. I see that Laravel supports pessimistic locking via the lockForUpdate method, but the documentation is sparse, and I'm not sure how to use it in this context – or if this is even the right approach.

I am implementing an API endpoint that allows our users to request a resource from our inventory table. For simplicity, let's say that the table has a boolean available column. The idea is that this endpoint will select the first row from the inventory table where available is true, and update that row with the appropriate account ID and setting available to false. So it would look something like this:

public function assignItemToAccount(Account $account): Inventory {
    $item = Inventory::where('available', true)->first();

    $item->update([
        'account_id' => $account->id,
        'available' => false
    ]);

    return $item->fresh();
}

The problem I'd like to avoid is, since I am using ->first(), concurrent requests to this endpoint would want to assign the same item from inventory. I see that Laravel supports pessimistic locking via the lockForUpdate method, but the documentation is sparse, and I'm not sure how to use it in this context – or if this is even the right approach.

• laravel
• eloquent
• laravel-10
• pessimistic-locking

Share Improve this question Follow asked Feb 18 at 0:53 VinceVince 3,28811 gold badge2020 silver badges2929 bronze badges

Add a comment  | 

1 Answer 1

Sorted by: Reset to default 1

this is how I usually code for database transactions.

<?php

use Illuminate\Support\Facades\DB;

class SomeClass {

    public function assignItemToAccount(Account $account)
    {
        DB::beginTransaction();
    
        try {
            $item = Inventory::where('available', true)
                ->lockForUpdate()
                ->first();

            if (is_null($item)) {
                DB::rollBack();
                return response()->json(['message' => 'Item not found'], 404);
            }

            $item->update([
                'account_id' => $account->id,
                'available' => false
            ]);

            DB::commit();
        } catch (\Exception $e) {
            \Log::error($e->getMessage());
            DB::rollBack();

            return response()->json(['message' => 'Failed to assign Item'], 500);
        }

        return $item->fresh();
    }

}

Whenever I think a process needs to have database transactions, I surround them in try catch block.

I prefer using DB::beginTransaction() over DB::transaction(function () {}) since it has more control over when to commit and rollback.

When using the lockForUpdate(), it will prevent other process from reading/writing until the initial process commits the database transaction. So if assignItemToAccount method is happening at the same time through the request, 1 request is forced to wait until the other completes its transaction.

本文标签： eloquentLaravel 10430 Prevent concurrent requests from updating the same rowStack Overflow