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?)

Winglet

Answer 1

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

Horsepowerhour

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

Karynkaryo

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

Before

Answer 4

what i am doing because i am getting a whole lot of error

production.ERROR: SQLSTATE[23000]: Integrity constraint violation: 1062 Duplicate entry

this was based off of multi threaded table insertions using queues... what i thought would be ok to do this with in Laravel firstOrCreate, then I tried updateOrCreate, and I gotta say this is kind of a huge oversight considering how everything is multiused multithreaded multi person... etc etc... this is simple for me. at least it seems to work so far

public function firstOrCreate(array $attributes, array $values = []) {
     if (! is_null($instance = $this->where($attributes)->first())) {
          return $instance;
     }
     try {
         return tap($this->newModelInstance($attributes + $values), function ($instance) {$instance->save();});
    } catch (Exception $e) {
        return $this->where($attributes)->first();
    }
}

now this can still fail if the race conditions are that racy, with multiple collisions, but that can be sort of hedged by either making the function cyclic redundant, or just making it redundant a few times... pretty crappy looking way, but i havent had anymore collisions...

public function firstOrCreate(array $attributes, array $values = []) {
    if (! is_null($instance = $this->where($attributes)->first())) {
        return $instance;
    }
    try {
        return tap($this->newModelInstance($attributes + $values), function ($instance) {$instance->save();});
    } catch (Exception $e) {
            if (! is_null($instance = $this->where($attributes)->first())) {
                return $instance;
            }
            try {
                return tap($this->newModelInstance($attributes + $values), function ($instance) {$instance->save();});
           } catch (Exception $e) {
                return $this->where($attributes)->first();
           }
       }
    }

Brien

Answer 5

for numbers, you can use increment(), decrement() method.

Example: $transaction->increment('amount', $amount);

I tested this by simple code.

<?php

namespace App\Models;

use Illuminate\Database\Eloquent\Factories\HasFactory;
use Illuminate\Database\Eloquent\Model;
use Illuminate\Support\Facades\DB;

class Transaction extends Model
{
    use HasFactory;

    public static function deposit(int $id, int $amount) {
        DB::beginTransaction();
        $transaction = Transaction::find($id);
        if($transaction) {
            echo "$transaction->id has $transaction->amount \n";
            $transaction->increment('amount', $amount);
            // $transaction->amount += $amount;
            sleep(10);
            $transaction->save();
            echo "After deposit now $transaction->id has $transaction->amount \n";
        }
        DB::commit();
    }

    public static function withdraw(int $id, int $amount) {
        DB::beginTransaction();
        $transaction = Transaction::find($id);
        if($transaction && $transaction->amount >= $amount) {
            echo "$transaction->id has $transaction->amount \n";
            $transaction->decrement('amount', $amount);
            // $transaction->amount -= $amount;
            sleep(10);
            $transaction->save();
            echo "After withdraw now $transaction->id has $transaction->amount \n";
        }
        DB::commit();
    }

}

To test this open two different terminal & open tinker and create one record with id => 1 & amount => 5000.

run

Transaction::withdraw(1, 500);

in one terminal and the below in other terminal

Transaction::deposit(1, 1000);

Check by running Transaction::all();

Spindle