1

Lets say i have a query that gets a post model with its comment model. So the query should look something like this

Post::with(['comments'])->get();

Now, i want to implement laravel's pessimistic locking like so

Post::with(['comments'])->sharedLock()->get();

Will it lock the rows from the comment's table as well? The documentation mention,

A shared lock prevents the selected rows from being modified until your transaction commits

The documentation gives an example of retrieving 1 model instance with no relationship. So i am unclear weather it applies to the main model and its relationship as well.

Firdaus Indradhirmaya
  • 314
  • 4
  • 18

2 Answers2

1

So after doing a few tests, it seems that it does lock its relationship rows as well. Here is my scenario.

I have 2 tabs open, the first tab have /locktest1 opened and the other one with /locktest2. The first runs a function like so

DB::transaction(function () {
    dump(Post::with(['comments'])->sharedLock()->get());
    sleep(5);
});

While the second tab with /locktest2 points to a function like so

DB::transaction(function () {
    dump(Comment::sharedLock()->get(), 'done');
});

The sleep is placed after the first function to simulate latency. The second parameter in the second function is just an indication for me to see if the second function runs right after the first one finish. I ran the first tab with /locktest1, waited a bit and then ran the 2nd tab with /locktest2. Both of the browser's loading indicator are running at this point. After 5 second, the first tab finished its sleep and the second tab finishes getting the comment model right after that.

Dharman
  • 30,962
  • 25
  • 85
  • 135
Firdaus Indradhirmaya
  • 314
  • 4
  • 18
  • I cannot confirm this behaviour. I have tested this with a fresh Laravel Applacation. And also in your example there should not be any delay at all in /locktest2, as a shared lock does not prevent from reading and in your example you do not perform any update operation. – shock_gone_wild Sep 02 '20 at 20:06
  • Thanks for pointing it out, that was an oversight by me @shock_gone_wild – Firdaus Indradhirmaya Sep 03 '20 at 06:55
1

If you are using a shared lock, the rows are protected from modification until the transaction commits. This does not prevent from reading. The lock comes into effect as soon as an update to a previously locked resource is attempted.

Additionally as @Tim Lewis suspected, eager loaded relations are not locked by default.

I'll give two example routes, to test different scenarios. Just open domain.com/test1 and domain.com/test2 in different browser tabs. Then reload Tab 1 and after that reload Tab 2. Depending on which lines you uncomment, you will see differences in loading time of the second tab.

This applies in my testing environment to MariaDB version 10.

Route::get('test1', function () {
    \DB::transaction(function () {
        \App\User::with(['comments' => function ($query) {
//            $query->sharedLock();
        }])->sharedLock()->get();
        sleep(15);
    });
});

Route::get('test2', function () {
        $user = \App\User::first();
        $user->first_name = \Str::random(10);
//        $user->save();

        $comment = \App\Comment::sharedLock()->first();
        $comment->created_at = \Carbon\Carbon::now()->addDays(array_rand(range(1, 10000)));
//        $comment->save();
});
shock_gone_wild
  • 6,700
  • 4
  • 28
  • 52
  • Lets say there are 2 queries inside a transaction closure. The first query gets a model with all of its relationship, while the second query updates one of the row's relationship from the previous query. Since they are both in the same transaction closure, will it update? Or will it be locked? – Firdaus Indradhirmaya Sep 03 '20 at 07:32
  • After i tried your example, it seems there maybe something wrong with my environment. From what i understand, if i run /test1 and then /test2 on 2 separate tabs, then test2 will have to wait for /test1 to finish. But from what i observe, no matter if the sharedLock on any of the query is commented or not, /test2 will still wait for the /test1. I am also using MariaDB v.10 on phpmyadmin. Any idea for this? – Firdaus Indradhirmaya Sep 03 '20 at 08:36
  • Hm.. I am using MariaDB v10 with the InnoDB Engine. Did you also consider the ->sharedLock()->get(); When selecting the user? $user->save() Will have to wait then... – shock_gone_wild Sep 03 '20 at 13:33
  • My setup is also the same. I double check and im pretty sure i consider that possibility. The problem is not that it doesn't wait for the transaction to finish. The problem is that it wait for the transaction to finish no matter if there is sharedLock() or not. For example, if i comment the sharedLock() on /test1 (while keeping the sleep()), /test2 will still wait for the sleep() to finish for it to finally update, even though /test1 doesn't impose sharedLock(). Am i missing something here? – Firdaus Indradhirmaya Sep 04 '20 at 06:25