How to `lockForUpdate()` on an existing Eloquent model?

Question

lockForUpdate() and sharedLock() are functions in Laravel Eloquent to set exclusive or shared locks (documentation here).

However, I couldnt find a nice syntax to apply this on a single, already instantiated Eloquent model. Consider the following example code:

DB::transaction(function() {
    // Find the user with ID = 1.
    $user = User::find(1);
    $user->lockForUpdate()->update([
        'balance' => $user->balance + 1
    ]);

    // ... some more stuff happens here in the transaction
});

The code above will NOT work as expected. lockForUpdate() returns a new query builder here, and it will cause ALL users to have their balance incremented by one.

I want the balance property to be read-locked for the duration of this transaction, such that any other transaction that happens in parallel will not derail the account balance by calculating the wrong result. So how can I make sure that the balance property is locked while updating this user? I know I can call the following function but it seems a bit counter-intuitive to create a new query for this, which includes the $user variable as well:

$updated = User::query()->where('id', 1)->lockForUpdate()->update([
    'balance' => $user->balance
]);

Note: I want to keep ->increment() and ->decrement() out of the equation here. I can't use these functions since I need Eloquent's updating/updated/saving/saved event hooks to fire properly (and they do not get triggered when using these functions). This is to be expected though, for more info see https://github.com/laravel/framework/issues/18802#issuecomment-593031090 .

user_1346367 · Answer

Hm it seems I managed to find a quick resolution to this question.

I think the intended way is to do this like so:

DB::transaction(function() {
    // You can also use `findOrFail(1)` or any other query builder functions here depending on your needs.
    $user = User::lockForUpdate()->find(1);
    $user->update([
        'balance' => $user->balance + 1
    ]);
});

This will then generate the following SQL (excerpt from MySQL general query log):

200524 13:36:04    178 Query    START TRANSACTION
178 Prepare select * from `users` where `users`.`id` = ? limit 1 for update
178 Execute select * from `users` where `users`.`id` = 1 limit 1 for update
178 Close stmt  
178 Prepare update `users` set `balance` = ?, `users`.`updated_at` = ? where `id` = ?
178 Execute update `users` set `balance` = 15, `users`.`updated_at` = '2020-05-24 13:36:04' where `id` = 1
178 Close stmt
QUERY     COMMIT

How to `lockForUpdate()` on an existing Eloquent model?

1 Answers