my code:

DB::transaction(function () {
       $foo = Foo::whereId(1)->lockForUpdate()->with('bars')->first();
       // dump foo & bars
       // update foo's columns
       // update bars' columns
   });

and I run this code at the same time twice, first time it can update correctly, but the second time when I query the foo, foo's columns is correct, but the bars are still old(in database it's correct), why is it and how to solve it?

CodePudding user response：

When you use the lockForUpdate() method in your code, it places a row-level lock on the records that are returned by the query. This lock prevents other transactions from updating the same records until the current transaction is committed. However, it does not prevent other transactions from reading the same records.

When you use the with function it will look into the relation with the rows in the table bars before the lock is placed and since the lock only prevents other transactions from updating the same records not reading this is why the related bars are old.

You have a couple of options to solve this problem:

1. Reload the relation after the transaction by calling the relation again:

$foo->load('bars');

2. Use the lockForUpdate() method on the related table(s) as well, in this case the bars table:

$foo = Foo::whereId(1)
            ->with(['bars' => function ($query) {
                $query->lockForUpdate();
            }])
            ->lockForUpdate()
            ->first();

This will ensure that the related bars records are locked as well, and they will be up-to-date when the transaction is committed.

3. Nesting another transaction within the first transaction, and by doing so, you lock the related records as well, this will not only update the current transaction but also the other related transactions that could be running in parallel.

DB::transaction(function () {
       $foo = Foo::whereId(1)->lockForUpdate()->first();
       DB::transaction(function() use($foo){
              $foo->bars()->lockForUpdate()->get();
              // update foo's columns
              // update bars' columns
       });
   });

It's worth noting that while the row-level lock allows multiple transactions to read the same records, it can cause contention when multiple transactions try to update the same records simultaneously. It's worth testing your code with and without the lockForUpdate() method to see which approach works best for your specific use case.

I hope it clarifies your confusion.

CodePudding user response：

Since you are using lockForUpdate() and want to use the new data after you update, you need to re-hydrate the model using refresh()

The refresh method will re-hydrate the existing model using fresh data from the database. In addition, all of its loaded relationships will be refreshed as well:

https://laravel.com/docs/9.x/eloquent#refreshing-models

There is a great answer by NoOorZ24 @ Laravel lockforupdate (Pessimistic Locking) explaining how the lock for transaction works to further clarify.