How to prevent "race condition" in this case?

I am not sure this is race condition but this is the first thing that came up to my head. Following my other posts, I realized that even with everything I added, there might be a situation where if the system is used by many people simultaneously it will make many of them get errors.

This is the situation: There are 2 tables, one with available codes, and another table with codes in use.

I do a query to fetch the codes from "codes" table and check against unused codes from "inUse" table. The "codes" table holds different types of codes and the user can request a different amount of codes from each type, so I have multiple queries before actually getting the list of codes, which leads to a situation where, until I get the list of available codes, anther user can get the same list of codes even if I lock the table.

DB::transaction(function () use ($values) {

    // Query here to get all available codes of each type into a collection

    // Here I use the take() method on the collection with the number of codes the user requested from each code type, so now I have an array like this: ['1232', '112333', '333777'].
   
    // After getting list of codes I re-select only the codes the user requested and lock the rows
    $codes = DB::table('available')->whereIn('codes', $values)->lockForUpdate()->pluck('codes')->toArray();

    $existingValues = DB::table('inUse')->whereIn('codes', $codes)->get();
    // Insert the values that don't exist
    if (!empty($existingValues)) {
        DB::table('inUse')->insert($codes );
    }
});

At first I thought I could put the lock on the first query that get's the initial collection from the "codes" table, but I thought that when many people use it, it might give them timeout error because many users will be locked until the transaction of other users is finished.

Also, I cannot use Laravel's queue system which would have been a nice solution.

The other thing I did was to wrap the entire transaction in a while loop and have number of retries (so sort of like queue), but I am not sure it's a good solution still.

Is my solution with the while alright? Will it maybe cover many cases where there are many users together on the system? This is the way I implement the retry solution, from my other post: https://laracasts.com/discuss/channels/code-review/is-it-possible-to-retry-the-db-transaction-if-it-fails

LaryAI

3 years ago

Level 58

One solution to prevent a race condition in this case is to use a database lock on the "inUse" table before inserting any new codes. This will ensure that only one transaction can access the "inUse" table at a time, preventing multiple users from inserting the same codes simultaneously.

Here's an updated version of the code with the lock added:

DB::transaction(function () use ($values) {

    // Query here to get all available codes of each type into a collection

    // Here I use the take() method on the collection with the number of codes the user requested from each code type, so now I have an array like this: ['1232', '112333', '333777'].
   
    // After getting list of codes I re-select only the codes the user requested
    $codes = DB::table('available')->whereIn('codes', $values)->pluck('codes')->toArray();

    // Lock the "inUse" table to prevent race conditions
    DB::table('inUse')->lockForUpdate()->get();

    $existingValues = DB::table('inUse')->whereIn('codes', $codes)->get();
    // Insert the values that don't exist
    if (!empty($existingValues)) {
        DB::table('inUse')->insert($codes);
    }
});

Note that this solution may cause some transactions to wait for the lock to be released, which could lead to slower performance during high traffic periods. However, it should prevent any race conditions from occurring.

Ligonsker

3 years ago

Level 8

@LaryAI But in this case, there can happen a situation where 2 users get the same codes, and after 1 transaction ends, then the second user would be thrown an error that the codes are already in use, but I don't want that to happen, I want him to try with new codes. So it's not really preventing the race condition if 2 users can still come with the same list of codes

hondnl

3 years ago

Level 5

The available codes in the table available are not being removed , once they are "in use" ?

Please or to participate in this conversation.