How to handle DbUpdateConcurrencyException?

Question

I have an endpoint which hits two functions based on query parameter. There is a use case where this endpoint will hit simultaneously and due to that I am running into concurrency issue. To avoid this, I have added retry logic but looks like it is not working as expected.

I am getting the following error while updating the same cosmos document (DbUpdateConcurrencyException).

Microsoft.EntityFrameworkCore.DbUpdateException: Conflicts were detected for item with id 'ABC123'.

What am I doing wrong here? What is the best approach to handle this scenario on large volume? (i.e., >15K-20K request per hour)

Approach:

• Trigger endpoints to hit the cosmos at the same time with same partition key.
• Add retry logic (using Polly) in case of DbUpdateConcurrencyException.
• Prior to retry, stop tracking all currently tracked entities and then move forward.
• My retry attempts are 3 times, every 250 ms.

Code:

Service.cs

using System;
using System.Linq;
using System.Net;
using System.Text;
using System.Threading;
using System.Threading.Tasks;
using AutoMapper;
using FluentValidation;
using Microsoft.EntityFrameworkCore;
using Microsoft.EntityFrameworkCore.ChangeTracking;
using Microsoft.Extensions.Logging;
using Polly;
using Polly.Retry;

namespace Services
{

    public class SpecificationsService : ServiceBase, ISpecificationsService
    {
        private readonly ILogger<SpecificationsService> _logger;
        private readonly IManageSpecifications _manageSpecifications;
        // private readonly AsyncRetryPolicy _policy = Policy
        //     .Handle<DbUpdateConcurrencyException>()
        //     .WaitAndRetryAsync(MasterDataConstants.RetryOnConcurrencyException.MaxRetryAttempt, _ =>  TimeSpan.FromMilliseconds(MasterDataConstants.RetryOnConcurrencyException.RetryInterval));

        private readonly AsyncRetryPolicy _policy = Policy
            .Handle<DbUpdateConcurrencyException>()
            .RetryAsync(MasterDataConstants.RetryOnConcurrencyException.MaxRetryAttempt,
                (_, _) => Task.Delay(MasterDataConstants.RetryOnConcurrencyException.RetryInterval));
            
        private int _retryAttempt;

        public SpecificationsService(ILogger<SpecificationsService> logger, IManageSpecifications manageSpecifications)
        {
            _logger = logger;
            _manageSpecifications = manageSpecifications;
        }

        public async Task<ServiceResponse<MyDto>> UpsertMotorSpecificationsAsyncWithRetryAsync(MyDto myDto, CancellationToken cancellationToken)
        {
            return await _policy.ExecuteAsync(ct => UpsertMotorSpecificationsAsync(myDto, ct), cancellationToken);
        }
        
        public async Task<ServiceResponse<MyDto>> UpsertMotorSpecificationsAsync(MyDto myDto, CancellationToken cancellationToken)
        {
            try
            {
                var materializedEntity = await _manageSpecifications.MyRepository.GetDataEntityByIdAsync(myDto.Vin, cancellationToken);

                EntityEntry<MaterializedEntity> materializedEntityEntry;

                if (materializedEntity == null)
                {
                    materializedEntityEntry = await _manageSpecifications.MyRepository.AddMaterializedEntityAsync(materializedEntityToCreate, cancellationToken);
                    response.StatusCode = ResultCode.Created;
                }
                else
                {
                    materializedEntityEntry = await _manageSpecifications.MyRepository.UpdateMaterializedEntity(materializedEntity);
                    response.StatusCode = ResultCode.Updated;
                }

                if (await _manageSpecifications.SaveChangesAsync(cancellationToken) > 0)
                {
                    response.Result = myDto;
                }
                else
                    return GetExceptionServiceResponse<MyDto>();
            }
            catch (DbUpdateConcurrencyException dbUpdateConcurrencyException)
            {
                _manageSpecifications.ClearAsync();
                _retryAttempt++;
                
                if (_retryAttempt.Equals(MasterDataConstants.RetryOnConcurrencyException.MaxRetryAttempt))
                {
                    return GetExceptionServiceResponse<MyDto>();
                }

                throw;
            }
            catch (Exception exception)
            {
                return GetExceptionServiceResponse<MyDto>(exception.GetBaseException().Message);
            }
        }

        public async Task<ServiceResponse<MyDto>> UpsertAtdSpecificationsWithRetryAsync(MyDto myDto, CancellationToken cancellationToken)
        {
            return await _policy.ExecuteAsync(ct => UpsertAtdSpecificationsAsync(myDto, ct), cancellationToken);
        }

        public async Task<ServiceResponse<MyDto>> UpsertAtdSpecificationsAsync(MyDto myDto,
            CancellationToken cancellationToken)
        {
            try
            {
                var materializedEntity = await _manageSpecifications.MyRepository.GetDataEntityByIdAsync(myDto.Vin, cancellationToken);

                EntityEntry<MaterializedEntity> materializedEntityEntry;

                if (materializedEntity == null)
                {
                    materializedEntityEntry = await _manageSpecifications.MyRepository.AddMaterializedEntityAsync(materializedEntityToCreate, cancellationToken);
                    response.StatusCode = ResultCode.Created;
                }
                else
                {
                    materializedEntityEntry = await _manageSpecifications.MyRepository.UpdateMaterializedEntity(materializedEntity);
                    response.StatusCode = ResultCode.Updated;
                }

                if (await _manageSpecifications.SaveChangesAsync(cancellationToken) > 0)
                {
                    response.Result = myDto;
                }
                else
                    return GetExceptionServiceResponse<MyDto>();
            }
            catch (DbUpdateConcurrencyException dbUpdateConcurrencyException)
            {
                _manageSpecifications.ClearAsync();
                _retryAttempt++;
                
                if (_retryAttempt.Equals(MasterDataConstants.RetryOnConcurrencyException.MaxRetryAttempt))
                {
                    return GetExceptionServiceResponse<MyDto>();
                }

                throw;
            }
            catch (Exception exception)
            {
                return GetExceptionServiceResponse<MyDto>(exception.GetBaseException().Message);
            }
        }
        
      }
}

Repo.cs

using System;
using System.Threading;
using System.Threading.Tasks;
using Microsoft.EntityFrameworkCore;

namespace Cosmos.UnitsOfWork
{
    public interface IManageSpecifications : IUnitOfWork
    {
        public IMyRepository MyRepository { get; }

        void ClearAsync();
    }

    public class ManageSpecifications: IManageSpecifications
    {
        private IMyRepository? _MyRepository;

        private readonly DbContext _dbContext;

        public IMyRepository MyRepository
        {
            get
            {
                return _MyRepository ??= new SomeRepository(_dbContext);
            }
        }

        /// <summary>
        ///     Overloaded Constructor
        /// </summary>
        /// <param name="dbContextFactory">Instance of IDbContextFactory</param>
        public ManageSpecifications(IDbContextFactory<DbContext> dbContextFactory)
        {
            _dbContext = dbContextFactory.CreateDbContextAsync().GetAwaiter().GetResult() ?? throw new ArgumentNullException(nameof(DbContext));
        }

        public Task<int> SaveChangesAsync(CancellationToken cancellationToken)
        {
            return _dbContext.SaveChangesAsync(cancellationToken);
        }

        public void ClearAsync()
        {
             _dbContext.ChangeTracker.Clear();
        }
        
        public Task<int> SaveChangesAsync()
        {
            return _dbContext.SaveChangesAsync();
        }

        private void Dispose(bool disposing)
        {
            if (disposing)
            {
                _dbContext.Dispose();
            }
        }

        public void Dispose()
        {
            Dispose(true);
        }
    }
}

Error:

 Microsoft.EntityFrameworkCore.DbUpdateException: Conflicts were detected for item with id 'ABC123'.
 ---> Microsoft.Azure.Cosmos.CosmosException : Response status code does not indicate success: Conflict (409); Substatus: 0; ActivityId: 7b3c8312-9108-402b-b22e-b0c5cc036903; Reason: (
code : Conflict
message : Entity with the specified id already exists in the system.