Laravel lockforupdate (Pessimistic Locking)

Question

i'm trying to figure out how to use/test the lockforupdate correctly, but i found is not function like what i expected

this is just testing

public function index() {
        return dd(\DB::transaction(function() {
            if (\Auth::guard('user')->check()) {
                $model = \App\Models\User::find(1)->lockForUpdate();
                sleep(60);
                $model->point = 100000;
                $model->save();
            } else {
                $model = \App\Models\User::find(1);
                $model->point = 999;
                $model->save();
            }

            return $model;
        }));
}

i try to test in 2 browser, browser 1 user logged in and browser 2 not logged in, browser 1 hit refresh, then there will lockforupdate and sleep 60 seconds before update

in the 60 seconds, i go browser 2 and hit refresh, however the record is not locked, i check phpmyadmin and the record is updated(within the 60 seconds lock trigger by browser 1)

but after 60 seconds, the record has been modified again by browser 1(Point 100000)

so am i misunderstanding the lockforupdate is use for?or i test it incorrectly?

what i expected is the row shouldn't be modified by browser 2 in the first 60 seconds(blank page with loading favicon or error throw?)

https://laravel.com/docs/5.2/queries#pessimistic-locking

and i did some research but still cannot understand what different between sharedLock(LOCK IN SHARE MODE) and lockForUpdate(FOR UPDATE)

btw i confirmed the database is innodb

score 24 · Accepted Answer · answered Jan 01 '16 at 13:45

24

This work, finally, but still don't understand what sharedLock(LOCK IN SHARE MODE) and lockForUpdate(FOR UPDATE) different

    public function index() {
        return dd(\DB::transaction(function() {
            if (\Auth::guard('user')->check()) {
                $model = \App\Models\User::lockForUpdate()->find(1);
                sleep(30);
                $model->point = 100000;
                $model->save();
            } else {
                $model = \App\Models\User::lockForUpdate()->find(1);
                $model->point = $model->point + 1;
                $model->save();
            }

            return $model;
        }));
    }

answered Jan 01 '16 at 13:45

user259752

1,065
2
9
24

10

From the docs: https://laravel.com/docs/5.2/queries#pessimistic-locking sharedLock locks only for write, lockForUpdate also prevents them from being selected – cdarken Nov 10 '16 at 21:28
Transaction Locks are used for handling database between Multi-User-Access. If your table is locked, then other user's cant access it. So data is managed properly. – Shankar Thiyagaraajan Dec 29 '16 at 08:37

score 20 · Answer 2 · answered Mar 09 '21 at 13:04

So this is old question but I believe my answer can clarify how ->lockForUpdate() works

From Laravel documentation:

A shared lock prevents the selected rows from being modified until your transaction is committed.

So as it is written - lock will be active from when you call it until your transaction is done.

Remember:

->find(1) works like ->first(), ->get(), ->insert(), ->save() etc. - it executes the query

->lockForUpdate() works like ->where(), ->select(), join() etc. - it adds to the query, but doesn't execute it

$model = \App\Models\User::find(1)->lockForUpdate(); - you try to add lock after query has already executed

$model = \App\Models\User::lockForUpdate()->find(1); - you add lock before query is executed therefore lock is active until transaction is finished

Difference is that in 1st scenario ->lockForUpdate() wasn't executed when you taught that it was

I think this should be accepted as the answer for the question — Александр Родригес, Oct 29 '21 at 21:25

score 2 · Answer 3 · edited Nov 18 '20 at 11:08

2

Read This Article Reference

Pessimistic vs Optimistic Locking in Laravel

SharedLock:

DB::table('users')->where('votes', '>', 100)->sharedLock()->get();

LockForUpdate:

DB::table('users')->where('votes', '>', 100)->lockForUpdate()->get();

Laravel Docs

edited Nov 18 '20 at 11:08

louisfischer

1,968
2
20
38

answered Sep 09 '20 at 12:24

Ayman Elshehawy

2,746
23
21

Laravel lockforupdate (Pessimistic Locking)

3 Answers3

Read This Article Reference

Pessimistic vs Optimistic Locking in Laravel

Linked