ASP.NET Web API 2并发请求数据库更新异常的解决实践

我正在开发基于.NET Framework 4.6.2的ASP.NET Web API 2项目，通过Postman向特定端点发送并发请求时，数据库中仅单条记录被更新。

初始问题代码 #

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

尝试的解决方案 #

方案1：捕获DbUpdateConcurrencyException #

仅在2个并发请求时有效，超过2个则问题复现：

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

方案2：使用事务 #

在Postman性能测试（3个虚拟用户1分钟固定负载发送150次请求）时，仅134条记录被更新，且出现死锁：

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

方案3：使用WebApiThrottle限流 #

移除事务后添加限流，强制每秒处理请求：

服务代码

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

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."
});

最终可行代码 #

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

优化建议 #

• 数据库索引优化：给GameBank表的productCode和referenceId字段创建联合索引，提升查询效率，缩短锁持有时间，减少并发冲突。
• 重试策略完善：添加最大重试次数限制，避免无限循环；将随机延迟替换为指数退避策略（比如第一次延迟100ms，第二次200ms，以此类推），进一步降低请求冲突概率。
• 变更追踪管理：确保_unitOfWork.ClearChangeTracker()正确实现，彻底清除EF上下文的缓存，保证每次查询都读取数据库最新数据。
• 批量场景优化：如果后续需要支持多数量请求，建议使用WITH (UPDLOCK, READPAST)查询提示，在查询时锁定目标行并跳过已被锁定的记录，直接获取可用数据，减少冲突等待。
• 事务合理使用：可以将GameRepository.Insert和GameBank的更新操作合并到同一个事务中，保证数据一致性，但需注意控制事务范围，避免过长时间持有锁。

内容的提问来源于stack exchange，提问作者raysefo