CODEWITHSUNDEEP
mysqllaravellockinginnodb
Abdillah
Abdillah

Reputation: 1012

Laravel pessimistic locking cannot prevent SELECT on uncommitted row in multiple duplicate request

I tried to prevent unintentional duplicate request from changing my model for the second time when double clicking a button. I do the following.

Log::debug('Begin transaction');
DB::beginTransaction();

$pdo = DB::connection()->getPdo();
$pdo->exec('SET TRANSACTION ISOLATION LEVEL SERIALIZABLE');

try {
    $user = \App\User::sharedLock()->find($id);
    Log::debug('Select user: ' . pickfield($user));

    if ($user->checkout) {
        Log::debug('Already checked out, rollback');
        DB::rollback();

        return Redirect::back()->with('error', 'Already checked out');
    }

    Log::debug('Changing checkout..');
    $user->checkout = new \DateTime($request->input('checkout'));

    Log::debug('Saving checkout: ' . pickfield($user));
    $user->save();

    Log::debug('Commit!');
    DB::commit();
} catch(\Exception $e) {
    Log::debug('Error, rollback!');
    DB::rollback();
}

Transactions still run thus overlap to each other and make SELECT statement retrieve inconsistent model even after I put a shared lock with serializable isolation.

[2019-05-17 01:02:45] local.DEBUG: Begin transaction
[2019-05-17 01:02:45] local.DEBUG: Begin transaction
[2019-05-17 01:02:45] local.DEBUG: Select user: {"id":3225,"checkout":null}
[2019-05-17 01:02:45] local.DEBUG: Changing checkout..
[2019-05-17 01:02:45] local.DEBUG: Saving checkout: {"id":3225,"checkout":{"date":"2019-05-17 01:02:45.428000","timezone_type":2,"timezone":"Z"}}
[2019-05-17 01:02:45] local.DEBUG: On model updating: {}
[2019-05-17 01:02:45] local.INFO: Sending email to [email protected]
[2019-05-17 01:02:45] local.DEBUG: Begin transaction
[2019-05-17 01:02:45] local.DEBUG: Select user: {"id":3225,"checkout":null}
[2019-05-17 01:02:45] local.DEBUG: Changing checkout..
[2019-05-17 01:02:45] local.DEBUG: Saving checkout: {"id":3225,"checkout":{"date":"2019-05-17 01:02:45.604000","timezone_type":2,"timezone":"Z"}}
[2019-05-17 01:02:45] local.DEBUG: On model updating: {}
[2019-05-17 01:02:45] local.INFO: Sending email to [email protected]
[2019-05-17 01:03:00] local.DEBUG: Commit!
[2019-05-17 01:03:00] local.DEBUG: Error, rollback!

I though my code already guarantee to prevent SELECT query executed in the middle of another transaction. I use InnoDB.

Related, no solution: Laravel pessimistic lock not working like supposed to

Any explanation or solution?

Upvotes: 1

Views: 1290

Answers (2)

Abdillah
Abdillah

Reputation: 1012

Oh! I was confused.

It was my locking type that was incorrect. It should be lockForUpdate.

A "for update" lock prevents the rows from being modified or from being selected with another shared lock.

While a shared lock,

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

I've tested both but with MyISAM engine and doesn't work at all that's why I was confused.

Upvotes: 1

Anas Bakro
Anas Bakro

Reputation: 1409

A possible solution is by disabling the button before hitting the endpoint which is important even if you managed to solve this issue as you shouldn't allow (as much as you can) sending duplicate requests at all.

Upvotes: 2

Related Questions