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

Question

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:

Answer 1

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.

Answer 2

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.

Answer 3

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();
}