How to fix update problem in concurrent request in ASP.NET Web API 2

Cenk 991 Reputation points
2023-12-04T15:39:18.34+00:00

I am working on an ASP.NET Web API 2 project with .NET Framework 4.6.2. When I send two concurrent requests with the same parameters from Postman, only one record is updated in the database, even though there should be two. I have included screenshots of the Postman requests and the table after the response. Here is the code I'm using:

private HttpResponseMessage CallGameNew(RequestDto requestDto)
{
    // Code omitted for brevity.
    
    List<GameBank> gameBankResult = null;

    //Query GameBank database
    gameBankResult = _unitOfWork.GameBankRepository.GetGames(g =>
        g.productCode == requestDto.productCode && g.referenceId == Guid.Empty);

    if (gameBankResult != null && gameBankResult.Count() >= requestDto.quantity)
    {
        var k = requestDto.quantity - 1;
        for (var i = k; i >= 0; --i)
        {
            gameBankResult[i].clientTrxRef = gameRequest.clientTrxRef;
            gameBankResult[i].referenceId = gameRequest.referenceId;
            gameBankResult[i].requestDateTime = DateTime.Now;
            gameBankResult[i].responseDateTime = DateTime.Now;
        }

        //***** UPDATE GameBank *****
        _unitOfWork.GameBankRepository.Update(gameBankResult[k]);

        if (requestDto.quantity == 1)
        {
            //Code omitted for brevity.
        }
            
    }

    _unitOfWork.Save();

    return response;
}

What is causing this issue and how can I fix it?

Screenshots:

Ekran görüntüsü 2023-12-04 182338

Ekran görüntüsü 2023-12-04 182646

ASP.NET
ASP.NET
A set of technologies in the .NET Framework for building web applications and XML web services.
3,415 questions
C#
C#
An object-oriented and type-safe programming language that has its roots in the C family of languages and includes support for component-oriented programming.
10,640 questions
ASP.NET API
ASP.NET API
ASP.NET: A set of technologies in the .NET Framework for building web applications and XML web services.API: A software intermediary that allows two applications to interact with each other.
318 questions
0 comments No comments
{count} votes

4 answers

Sort by: Most helpful
  1. Louis Arndt 5 Reputation points
    2023-12-09T05:51:15.8433333+00:00

    The Laboratory Corporation of America Holdings, MyLabCorp , is among the most popular healthcare companies in the United States out there.

    With over 75,000 employees working for the company, it is essential for them to have a mechanism that allows employees to get all the insights into their work details and day-to-day work schedules without having to reach out to their manager or the Human Resources Department every time.

    http://mylabcorp.live/

    1 person found this answer helpful.
    0 comments No comments

  2. maxdavid 0 Reputation points
    2023-12-04T16:24:17.0033333+00:00

    Handling concurrent updates in ASP.NET Web API 2 can be tricky, as multiple requests can attempt to modify the same data simultaneously, potentially leading to data inconsistencies. To address this issue, you can implement optimistic concurrency control (OCC) mechanisms, which ensure that data remains consistent even in the face of concurrent updates.

    Optimistic Concurrency Control (OCC)

    OCC relies on the assumption that conflicts are rare, and it attempts to resolve them without blocking other requests. It achieves this by:

    Versioning: Each data entity has a version identifier, typically a timestamp or row version.

    Read-Modify-Write Cycle: When a request retrieves data for update, it also retrieves the current version.

    Update Attempt: The request modifies the data and includes the current version in the update request.

    Server-side Validation: The server receives the update request and checks if the included version matches the current version in the database. If it matches, the update proceeds, and the version is incremented. If it doesn't match, it indicates a conflict, and the update is rejected.

    Implementing OCC in ASP.NET Web API 2

    To implement OCC in ASP.NET Web API 2, you can utilize the Entity Framework's concurrency token functionality. The following steps outline the process:

    Enable Concurrency Token: In your Entity Framework configuration, enable concurrency tokens by setting the ConcurrencyMode property of your DbContext to Fixed. This will generate a concurrency token for each entity.

    Attach Concurrency Token: In your API controller, when retrieving data for update, attach the concurrency token to the retrieved entity.

    Include Concurrency Token in Update Request: When sending the update request, include the retrieved concurrency token along with the updated data.

    Handle Concurrency Conflict: In your API controller, when handling the update request, check if the concurrency token in the update request matches the current version in the database. If it doesn't match, handle the concurrency conflict appropriately, such as returning a conflict error or prompting the user to refresh and retry.

    0 comments No comments

  3. Bruce (SqlWork.com) 61,491 Reputation points
    2023-12-04T16:33:18.1966667+00:00

    your read and update are not atomic. both requests read the same values, then write the same values. the read and update should be done in a database transaction with proper locking.

    also your update logic does not make a lot of sense. why the for loop, but only update of last entry? the sample I believe only returns one row, but why more would there be more more than one? why update the other rows but not save.


  4. Cenk 991 Reputation points
    2023-12-05T05:13:58.1566667+00:00

    This change seems to work, any ideas?

    //Update GameBank
    try
    {
        _unitOfWork.GameBankRepository.Update(gameBankResult[k]);
        _unitOfWork.Save();
    }
    catch (DbUpdateConcurrencyException)
    {
        // Refresh and retry
        gameBankResult[k] = _unitOfWork.GameBankRepository.GetByID(gameBankResult[k].GameBankID);
        _unitOfWork.GameBankRepository.Update(gameBankResult[k]);
        _unitOfWork.Save();
    }