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

Cenk 986

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

4 answers

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/
Please sign in to rate this answer.

1 person found this answer helpful.

0 comments No comments
Sign in to comment
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.
Please sign in to rate this answer.

0 comments No comments
Sign in to comment
Bruce (SqlWork.com) 60,391 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.
Please sign in to rate this answer.
Cenk 986 Reputation points

2023-12-04T17:01:23.2233333+00:00

How to make atomic?
Sign in to comment

Cenk 986

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

Cenk 986 Reputation points

2023-12-06T07:06:05.63+00:00

I tried this solution, up to 5 concurrent requests with Postman, and it worked. Any suggestions?
Lan Huang-MSFT 28,821 Reputation points Microsoft Vendor

2023-12-06T07:33:51.0866667+00:00

Hi @Cenk,

This should be after you fire DbUpdateConcurrencyException and update again.

You can take a look at the detailed definition of DbUpdateConcurrencyException.

DbUpdateConcurrencyException:Exception thrown by DbContext when it was expected that SaveChanges for an entity would result in a database update but in fact no rows in the database were affected. This usually indicates that the database has been concurrently updated such that a concurrency token that was expected to match did not actually match. Note that state entries referenced by this exception are not serialized due to security and accesses to the state entries after serialization will return null.

Cenk 986

Hi @Lan Huang

Here is my revised code but I wonder how to handle any number of concurrent requests? Would you please guide me?

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

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

    _unitOfWork.Save();

    return response;
}

Cenk 986

I tried to use a transaction as follows. But when I tried a performance test in Postman, 150 requests were sent (3 virtual users for 1 minute with a fixed load profile). Only 134 records were updated in the table. What is wrong with the solution?

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

    using (var scope = new TransactionScope(TransactionScopeOption.Required, 
        new TransactionOptions { IsolationLevel = IsolationLevel.ReadCommitted }))
    {
        //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();
        scope.Complete();
    }

    return response;
}

AgaveJoe 27,496 Reputation points

2023-12-08T18:45:02.98+00:00
The design relates the quantity property to an array index. If the quantity is 5 and the gameBankResult.Count() is 10 then gameBankResult[4] is updated. How is this logical?

I'm guessing the user case goes something like, there are 10 items available because the items have been assigned an empty referenceId GUID. A user wants 5 of these "empty" items (quantity). Therefore, 5 of the "empty" gameBankResults are given to the user by setting the game bank referenceId to the user's referenceId.

If this is the use case then the update needs to be within the loop, right?

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

Frankly, it seems like the logic is overly complex for keeping track of inventory but then again I have no idea how the code is supposed to work.
Cenk 986 Reputation points

2023-12-09T05:29:23.2133333+00:00

Assume the quantity is 1 always. Why I can not update all of the concurrent requests?
AgaveJoe 27,496 Reputation points

2023-12-09T10:16:50.48+00:00
Assume the quantity is 1 always. Why I can not update all of the concurrent requests?

If the quantity is a constant then why is there a quantity property? Even if the quantity is always one the design has the same logical problem.

Secondly, you have code that specifically handles when the quantity is 1 but you did not share the code!!!

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

I don't think you have a concurrency problem. I think you a buggy design.

Cenk 986

here is the updated code:

private HttpResponseMessage CallGameNew(RequestDto requestDto)
{
    HttpResponseMessage response = null;

    //ProductCode Conversion
    var productCode =
        _unitOfWork.ProductCodeRepository.GetByCode(p => p.clientCode == requestDto.productCode);

    if (productCode != null)
    {
        requestDto.productCode = productCode.gameCode;
    }

    var gameRequest = _mapper.Map<RequestDto, GameRequest>(requestDto);

    //Unique reference ID
    gameRequest.referenceId = Guid.NewGuid();

    var gameRequestDto = _mapper.Map<GameRequest, GameRequestDto>(gameRequest);
    //Create signature
    gameRequest = UtilitiesWatson.CreateSignature(gameRequestDto, RequestType.Initiate);

    //Set service
    gameRequest.service = "OUR";
    gameRequest.customerID = 5; //WATSON
    gameRequest.clientTrxRef = requestDto.clientTrxRef; //WATSON

    //Add initiation request into database
    _unitOfWork.GameRepository.Insert(gameRequest);
    _unitOfWork.Save();

    GameBank gameBankResult = null;

    gameBankResult = _unitOfWork.GameBankRepository.GetGame(g =>
        g.productCode == requestDto.productCode && g.referenceId == Guid.Empty);
    _unitOfWork.Save();

    if (gameBankResult != null)
    {
        gameBankResult.clientTrxRef = gameRequest.clientTrxRef;
        gameBankResult.referenceId = gameRequest.referenceId;
        gameBankResult.requestDateTime = DateTime.Now;
        gameBankResult.responseDateTime = DateTime.Now;

        _unitOfWork.GameBankRepository.Update(gameBankResult);
        _unitOfWork.Save();

        var gameBankConfirmResponse =
            _mapper.Map<GameBank, GameConfirmResponse>(gameBankResult);

        gameBankConfirmResponse.purchaseStatusDate = DateTime.Now;
        gameBankConfirmResponse.clientTrxRef = gameRequest.clientTrxRef;

        //ProductCode Conversion
        var productCodeReverse = _unitOfWork.ProductCodeRepository.GetByCode(p =>
            p.gameCode == requestDto.productCode);

        if (productCodeReverse != null)
        {
            gameBankConfirmResponse.productCode = productCodeReverse.clientCode;
        }

        var resultResponse = JsonConvert.SerializeObject(gameBankConfirmResponse,
            Formatting.Indented,
            new JsonSerializerSettings()
            {
                ReferenceLoopHandling = ReferenceLoopHandling.Ignore
            });
        response = new HttpResponseMessage
        {
            StatusCode = System.Net.HttpStatusCode.OK,
            Content = new StringContent(resultResponse, System.Text.Encoding.UTF8,
                "application/json"),
        };
        //Set service
        gameBankConfirmResponse.service = "OUR";
        gameBankConfirmResponse.clientTrxRef = requestDto.clientTrxRef;

        _unitOfWork.GameConfirmResponseRepository.Insert(gameBankConfirmResponse);
        _unitOfWork.Save();
    }

    return response;
}

AgaveJoe 27,496 Reputation points

2023-12-11T16:33:53.17+00:00

here is the updated code:

We can't read you mind. Does the new code function as expected and you wanted to let us know?
Cenk 986 Reputation points

2023-12-11T17:42:55.98+00:00

It is the simplified code that is running and having issue when getting concurrent requests. What design is buggy and why?
AgaveJoe 27,496 Reputation points

2023-12-11T18:40:39.1+00:00

It is the simplified code that is running and having issue when getting concurrent requests.

How are you testing concurrency errors? What evidence do you have that proves there is a concurrency issue and not some other coding issue? Have you implement optimistic concurrency?

What design is buggy and why?

The original code has a for...loop that updates several item properties within an array but only saves one of the array items. Plus it uses a quantity as an array index. I assume the new code replaces the original code. Does the new code function as expected? If you are still having problems what debugging steps have you performed to fix the problem? Is there any way you can provide a working example that the community can run to reproduce this issue?

Share via

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

4 answers