phpmysqllaravellaravel-5pessimistic-locking

Laravel lockforupdate (Pessimistic Locking)


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


Solution

  • 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;
            }));
        }