并发请求中的Laravel数据库锁表

Question

I want to prevent my database to update if suddenly multiple requests send to my database and updated at the same times.

I create user Laravel Users Table as an example and use JMeter to simulate there are two users, sending concurrent request to change the same row of data.

E.g Scenario

My bank account has $1000. In the same times, two requests send in for transfer money to Mr. A and Mr.B from my account.

My record for balance is 1000, Request 1 - Send $700 to Mr.A, and Request 2 - Send $700 to Mr.B. If I didn't lock the table, the system will be treated I have enough balance to send out the money.

My goal is when it comes to concurrent request, the 2nd (FIFO, even it is concurrency web server still will process it and treat one of it as second) will throw the exception / or show return error to the request said it is in use.

below is my code for a test, and I use JMeter to run the test. However, the test shows that both requests are updated. Hence, the latest request will overwrite the first request.

Below, I am expecting Laravel will throw exception or error for database lock, but it still proceed.

Route::get('/db-a', function() {

    \DB::beginTransaction();

    //lock the table
    $rs = \DB::table('users')->where('id', '1')->lockForUpdate()->first();

    $sql = "update users set remember_token='this is a' where id=1";
    \DB::update(DB::raw($sql));

    //purposely put the sleep to see can the table / row be locked

    sleep(3);

    \DB::commit();

    $rs = DB::table('users')->where('id', '1')->first();
    echo($rs->remember_token);
    return;
});

Route::get('/db-b', function () {

    \DB::beginTransaction();

    //lock the table
    $rs = \DB::table('users')->where('id', '1')->lockForUpdate()->first();

    $sql = "update users set remember_token='this is b' where id=1";
    \DB::update(DB::raw($sql));

    //purposely put the sleep to see can the table / row be locked
    sleep(3);


    \DB::commit();

    $rs = DB::table('users')->where('id', '1')->first();
    echo($rs->remember_token);
    return;
});

I do another version for manually catch the exception, but also not working

Route::get('db-callback-a', function() {
    try {
        app('db')->transaction(function () {
            $record = \DB::table('users')->where('id', 1)->lockForUpdate()->first();
            $sql = "update users set remember_token='this is callback a' where id=1";

            \DB::update(DB::raw($sql));
            sleep(3);
        });
    } 
    catch (\Exception $e) {
        // display an error to user
        echo('Database Row in Use, update later');
    }
});

Route::get('db-callback-b', function () {
    try {

        app('db')->transaction(function () {
            $record = \DB::table('users')->where('id', 1)->lockForUpdate()->first();
            $sql = "update users set remember_token='this is callback b' where id=1";


            \DB::update(DB::raw($sql));
            sleep(3);
        });
    } catch (\Exception $e) {
        // display an error to user
        echo ('Database Row in Use, update later');
    }
});