0

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');
    }
});
Shiro
  • 7,344
  • 8
  • 46
  • 80
  • Probably you will need to create a named lock manually, and check if it is being used before updating then throw an exception. Another way is to use something like a concurrency stamp – frz3993 Jul 24 '18 at 15:19
  • can u show example named lock manually? – Shiro Jul 26 '18 at 10:11
  • By using mysql `IS_FREE_LOCK()`, `GET_LOCK()` and `RELEASE_LOCK()`. – frz3993 Jul 26 '18 at 16:22
  • Or a simplified version using stamp, add a column to hold the stamp, when updating also update the stamp to a new value. So the second update will not be successful because it will be using the old value of the stamp. `UPDATE .. WHERE ID =:id AND stamp = :stamp` something like this – frz3993 Jul 26 '18 at 16:30
  • thanks for your reply. my problem is multiple concurrency requests send in for the same record, timestamp method will not work because at that time the timestamp is the same when 2 requests send at the sametimes. I want to lock it, so 2nd requests will not able to access it. I update my question scenario. – Shiro Jul 27 '18 at 07:35
  • The stamp is supposed to be some hash like random characters not a timestamp. I got this idea while working with .net identity framework. Actually, although there seems to be multiple concurrent request, only one will obtain the write lock, in case of innodb a row level locking. So other queries will be queued and wait until the lock is released before being executed. So, no two queries can update the same row at the same time. I believe this is the default behavior. The following query will wait, so there is no exception will be thrown. – frz3993 Jul 27 '18 at 07:57

0 Answers0