Preventing Race Conditions Using Database Transactions (Laravel)

Question

How do I prevent such a race condition from occuring? I understand that transactions in Laravel are blocking for updates, but how do I prevent stale data from being used? Is there a way to lock the database from reading while another transaction is going on? (i.e. have the second request wait for the first request to complete?)

Suppose username field in database for primary key id = 7 is null.

Request 1 comes in and does this:

public function raceCondition1() {

    DB::beginTransaction();

    //Get the model with primary key 7
    $user = User::findorfail(7);

    sleep(6);

    $user->username = 'MyUsername';

    $user->save();

    DB::commit();
}

Two seconds later, I run Request 2, which just concatenates something to the username column and saves:

public function raceCondition2() {

    DB::beginTransaction();

    $user = User::findorfail(7);

    sleep(6);

    $user->username = 'USER_' . $user->username;

    $user->save();

    DB::commit();
}

The result in this case in the database is: USER_

The second request read from the database before the first request could save, and used the stale NULL value. Is there a way to lock the database from reading while another transaction is going on? (i.e. have the second request wait for the first request to complete?)

Answer 1

Laravel supports "pessimistic locking". For more information about that refer to the Laravel documentation on pessimistic locking.

Answer 2

Transactions are used when you are executing queries and wish to have a mechanism to reverse the resulting modifications if there is a possibility of error during their execution.

Where as what you are looking is Internal Locking Methods, where every request to the database is put in queue and is processed only when the previous is processed.

I don't know whether this features comes out of the box from laravel ORM but it could be easily implemented from classic sql queries.

Check out this link, to understand how the entire mechanism works, I believe what you are looking for is Row Level Locking.

Locking Methods

Answer 3

To solve the racing condition problem of an application requires high performance, optimistic locking is better than pessimistic locking since pessimistic locking may create deadlocks.

In fact, optimistic locking is not a database feature, it's just a best practice.

For further more detail, you can check this great answer: Optimistic locking in MySQL