Handling concurrent requests

Hi I'm using Laravel and query builder.

Scenario:

Let's say that there are 2 concurrent requests.
Request A, gets money value 10 dollars from mysql table
for A request, php calculates some payments
Before A request's payment calculation completed, Request B comes and gets 10 for money value from mysql table
for A request's, php completes calculating and payment value is 8 dollars and checks if (10>8)
and updates mysql table money as 2
B request's payment calculation completes, and checks if (10>8)
and updates mysql table money as -6

The issue is B requests money value should be 2 and it shouldn't complete the payment.

How can i handle this. must i lock the tables?

Thanks

Devon

9 years ago

Level 18

Firstly, I'd recommend using database transactions.

Secondly, the InnoDB engine supports row-level pessimistic locking which means you don't have to lock an entire table like you do with the MyISAM engine.

Hope that help! :)

tekas

9 years ago

Level 1

Thanks but transaction doesn't solve this issue.

DB::beginTransaction(); try {

    $result = Db::table(DB_T_TEST.' as s')
        ->select('s.money')
        ->first();

    if ($result->money > 3000)
    {
    $update = DB::table(DB_T_TEST)
        ->update(['money' => DB::raw('money - 1000')]);

    }
        DB::commit();

    } catch (\Exception $e) {
        DB::rollback();
        abort(400, 'Something went wrong, Please try it again.'.$e);
    }

jlrdw

9 years ago

Level 75

How are you getting two concurrent requests on same record? If it's a different user, records / forms shouldn't be overlapping.

If your database is designed correctly, you shouldn't have to mess with any locking.

This could only happen if two people shared passwords, login at the same time, and attempt to edit the exact same record.

You the developer cannot control if someone shares their password.

tekas

9 years ago

Level 1

The user can send more than one concurrent request with some hack tools.

I solved it with sharedLock

DB::beginTransaction(); try { $result = Db::table(DB_T_TEST.' as s') ->select('s.money') ->where('id',1)->sharedLock()->first();

        if ($result->money > 3000)
    {
    $update = DB::table(DB_T_TEST)
        ->where ('id', 1)
        ->update(['money' => DB::raw('money - 1000')]);


    }
        DB::commit();

    } catch (\Exception $e) {
        DB::rollback();
    }

Devon

9 years ago

Level 18

:/ Sorry, maybe I didn't word my post very clearly... But that's exactly what I was suggesting, a transaction with pessimistic locking.

Glad you got it figured out though. :)

Please or to participate in this conversation.