How to fix the 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 to a specific endpoint from Postman, only one record is updated in the database.

Here is the code I'm having the issue:

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

I tried DbUpdateConcurrencyException handling in my code above. This seems working for only 2 concurrent requests, but if there are more than 2 concurrent requests I am having the same issue.

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

I tried using a transaction. 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. I got deadlocks.

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

So I removed the transaction and added WebApiThrottle to force requests every second.

Here is the service:

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

Here is the WebApiThrottle in WebApiConfig:

config.MessageHandlers.Add(new ThrottlingHandler()
{
    Policy = new ThrottlePolicy(perSecond: 2, perMinute: 28)
    {
        IpThrottling = true,
        EndpointThrottling = true,
        EndpointRules = new Dictionary<string, RateLimits>
        {
            { "api/v2/game/watson/purchase", new RateLimits { PerSecond = 1, PerMinute = 22, PerHour = 1100 } }
        }
    },
    Repository = new CacheRepository(),
    QuotaExceededMessage = "You may only perform this action every {0} seconds."
});

What are your suggestions? (My goal is to handle multiple concurrent requests that's why I am trying to find a proper way.)

Here is the final working 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;

    while (true)
    {
        try
        {
            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();
                break;  //exit from while loop
            }
        }
        catch (DbUpdateConcurrencyException)
        {
            _unitOfWork.ClearChangeTracker(); //IS REQUIRED, so the next select will read new RowVersion also
            Thread.Sleep((new Random()).Next(0, 1000)); //if you want to add a random pause 0-1 second
        }
    }

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

You should enclose your try...catch inside a "while" cycle, to handle any number of requests. Your example works until there are two requests only (no more). The first request is updating inside "try"; in the same time, the second is updating inside "catch"... and what would happen on the third request? So you should "save" inside "try" only; and in case of exception, find again a Guid.Empy inside "catch" and restart again (trying again to save, going up to "try" code). — Fabio Caruso
– Fabio Caruso, Commented Dec 5, 2023 at 17:54
Yes, it's your code: you can do how you like, but think: any exception inside try block will be handled by the catch block. But you put some update and save instructions inside catch block also. And, what about any eventual (concurrency) exception verifying inside the catch block? — Fabio Caruso
– Fabio Caruso, Commented Dec 6, 2023 at 20:39
"how about this code?" is not on-topic on Stack Overflow. If you still have a problem, please explain it. If it solved your issue, post it as an answer. — Gert Arnold
– Gert Arnold, Commented Dec 8, 2023 at 13:07
@FabioCaruso optimistic concurrency isn't broken. In fact it's always been the basic concurrency strategy in .NET and increases throughput by several orders of magnitude. Increasing locks may cover up the original problem but easily result in bigger ones, like deadlocks or an application that crawls to a halt — Panagiotis Kanavos
– Panagiotis Kanavos, Commented Dec 11, 2023 at 7:52
@Cenk the updated code is broken - TransactionScope uses an explicit database transaction that forces a connection to remain open for a long time. It's clear the non-repository classes you use are breaking EF Core's behavior. I'd suggest removing them completely, and just using EF Core itself, as intended. To understand how optimistic concurrency works or how conflicts occur, read the docs: Handling Concurrency Conflicts — Panagiotis Kanavos
– Panagiotis Kanavos, Commented Dec 11, 2023 at 7:57

Fabio Caruso · Accepted Answer · 2023-12-11 18:28:53Z

1

I don't know how your Update method works (hoping it updates the relative entities), and I understand that it's more difficult to change the whole structure of your program to eliminate the "repository coding", and use the DbContext instance like "Unit of Work", naturally doing operations inside an "using" block. Nevertheless, if you have the patience to add, in your table, a column like this (assuming you are using SQL Server), you can implement the Optimistic Concurrency:

[DatabaseGenerated(DatabaseGeneratedOption.Computed)]
[ConcurrencyCheck]
public byte[] RowVersion { get; set; }

And you can try to use this piece of code I wrote in 10 minutes, so we can only hope it solves your problem:

private HttpResponseMessage CallGameNew(RequestDto requestDto)
{
    // Code omitted for brevity.

    List<GameBank> gameBankResult = null;
    
    while (true)
    {
        try
        {           
            //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;
                for (var i = 0; i < k; i++)
                {
                    gameBankResult[i].clientTrxRef = gameRequest.clientTrxRef;
                    gameBankResult[i].referenceId = gameRequest.referenceId;
                    gameBankResult[i].requestDateTime = DateTime.Now;
                    gameBankResult[i].responseDateTime = DateTime.Now;
                    _unitOfWork.GameBankRepository.Update(gameBankResult[i]);
                }
            
                if (requestDto.quantity == 1)
                {
                    //Code omitted for brevity.
                }            

                _unitOfWork.GameBankRepository.Save();
                break;  //exit from while loop
            }           
        }
        catch
        {
            _dbContext.ChangeTracker.Clear();  //IS REQUIRED, so the next select will read new RowVersion also
            Thread.Sleep((new Random()).Next(0, 1000));  //if you want to add a random pause 0-1 second
        }           
    }

    return response;
}

Be patient to change the line:

_dbContext.ChangeTracker.Clear();

according to your dbcontext class name.

Good luck.

answered Dec 11, 2023 at 18:28

Fabio Caruso

3571 gold badge2 silver badges5 bronze badges

Sign up to request clarification or add additional context in comments.

21 Comments

raysefo Over a year ago

I will try and let you know, thank you for your help. Does this code support multiple concurrent requests?

raysefo Over a year ago

I mean if 2 concurrent requests come to this endpoint, does your code update 2 records in the database?

Fabio Caruso Over a year ago

You can use your ClearChangeTracker method, because the effect is the same: from the catch block, the code flow will go up to "Query GameBank database", reading a new value of RowVersion column, and trying again to update your rows.

raysefo Over a year ago

Please check my edited final working code in my post.

raysefo Over a year ago

Actually without altering the database, adding the nearly same code to the new API solved my problem. I did 10 threads with JMeter, and worked as it expected without concurrency problems. Thank you for your time and patience.

|

Collectives™ on Stack Overflow

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

1 Answer 1

21 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

21 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related