DbUpdateException- Duplicate key error is unhanded while i am catching all exceptions

Question

I have the following methods

public async Task Foo()
    {
        try
        {

            //Do stuff
            bool inserted = false;
            int tries=0;
            while (!inserted && tries<2)
            {
                try
                {
                    inserted = await Bar();                        
                }
                catch (Exception ex)
                {
                    //log ex and continue
                }
                finally
                {
                  if(!inserted)
                  {
                     tries++;
                  }
                }
            }
        }
        catch (Exception ex)
        {
            //log ex and continue
        }
    }

and

public async Task<bool> Bar()
    {
        //setup opbject to be inserted to database

        try
        {
            //the table can not have auto incrememnt so we read the max value
            objectToBeAdded.id = Context.Set<object>().Max(o => o.id) + 1;
            await Context.Set<object>().AddAsync(objectToBeAdded);
            await Context.SaveChangesAsync();
            return true;
        }
        catch (Exception ex) {
            return false;
        }
    }

The code runs in a multi threaded environment and many times per minute so there is always a chance for the following exception.

Microsoft.EntityFrameworkCore.DbUpdateException: An error occurred while updating the entries. See the inner exception for details. ---> MySql.Data.MySqlClient.MySqlException: Duplicate entry 'XXXXX' for key 'PRIMARY' ---> MySql.Data.MySqlClient.MySqlException: Duplicate entry 'XXXXX' for key 'PRIMARY'

Unfortunately it's a very hard error to reproduce and our issue is that it crashes the application instead of retrying and moving on.

We can not change the table to support auto increment Primary Key.

Edit: The full stack trace as requested

-Error- Failed executing DbCommand (8ms) [Parameters=[@p0='?' (DbType = Int64), @p1='?' (DbType = Boolean), ....., @pN='?' (DbType = Decimal)], CommandType='Text', CommandTimeout='600'] INSERT INTO table (id, col1, ....colN) VALUES (@p0, @p1, .... @pN); -Error- An exception occurred in the database while saving changes for context type 'Entities'. Microsoft.EntityFrameworkCore.DbUpdateException: An error occurred while updating the entries. See the inner exception for details. ---> MySql.Data.MySqlClient.MySqlException: Duplicate entry 'XXXXX' for key 'PRIMARY' ---> MySql.Data.MySqlClient.MySqlException: Duplicate entry 'XXXXXX' for key 'PRIMARY' at MySqlConnector.Core.ServerSession.TryAsyncContinuation(Task1 task) in C:\.......\mysqlconnector\src\MySqlConnector\Core\ServerSession.cs:line 1248 at System.Threading.Tasks.ContinuationResultTaskFromResultTask2.InnerInvoke() at System.Threading.ExecutionContext.RunInternal(ExecutionContext executionContext, ContextCallback callback, Object state) --- End of stack trace from previous location where exception was thrown --- at System.Threading.Tasks.Task.ExecuteWithThreadLocal(Task& currentTaskSlot) --- End of stack trace from previous location where exception was thrown --- at MySqlConnector.Core.ResultSet.ReadResultSetHeaderAsync(IOBehavior ioBehavior) in C:........\mysqlconnector\src\MySqlConnector\Core\ResultSet.cs:line 42 --- End of inner exception stack trace --- at MySql.Data.MySqlClient.MySqlDataReader.ActivateResultSet(ResultSet resultSet) in C:........\mysqlconnector\src\MySqlConnector\MySql.Data.MySqlClient\MySqlDataReader.cs:line 80 at MySql.Data.MySqlClient.MySqlDataReader.ReadFirstResultSetAsync(IOBehavior ioBehavior) in C:........\mysqlconnector\src\MySqlConnector\MySql.Data.MySqlClient\MySqlDataReader.cs:line 302 at MySql.Data.MySqlClient.MySqlDataReader.CreateAsync(MySqlCommand command, CommandBehavior behavior, ResultSetProtocol resultSetProtocol, IOBehavior ioBehavior) in C:........\mysqlconnector\src\MySqlConnector\MySql.Data.MySqlClient\MySqlDataReader.cs:line 287 at MySqlConnector.Core.TextCommandExecutor.ExecuteReaderAsync(String commandText, MySqlParameterCollection parameterCollection, CommandBehavior behavior, IOBehavior ioBehavior, CancellationToken cancellationToken) in C:........\mysqlconnector\src\MySqlConnector\Core\TextCommandExecutor.cs:line 37 at Microsoft.EntityFrameworkCore.Storage.Internal.RelationalCommand.ExecuteAsync(IRelationalConnection connection, DbCommandMethod executeMethod, IReadOnlyDictionary2 parameterValues, CancellationToken cancellationToken) at Microsoft.EntityFrameworkCore.Update.ReaderModificationCommandBatch.ExecuteAsync(IRelationalConnection connection, CancellationToken cancellationToken) --- End of inner exception stack trace --- at Microsoft.EntityFrameworkCore.Update.ReaderModificationCommandBatch.ExecuteAsync(IRelationalConnection connection, CancellationToken cancellationToken) at Microsoft.EntityFrameworkCore.Update.Internal.BatchExecutor.ExecuteAsync(DbContext _, ValueTuple2 parameters, CancellationToken cancellationToken) at Microsoft.EntityFrameworkCore.Storage.ExecutionStrategy.ExecuteImplementationAsync[TState,TResult](Func4 operation, Func4 verifySucceeded, TState state, CancellationToken cancellationToken) at Microsoft.EntityFrameworkCore.Storage.ExecutionStrategy.ExecuteImplementationAsync[TState,TResult](Func4 operation, Func4 verifySucceeded, TState state, CancellationToken cancellationToken) at Microsoft.EntityFrameworkCore.ChangeTracking.Internal.StateManager.SaveChangesAsync(IReadOnlyList`1 entriesToSave, CancellationToken cancellationToken) at Microsoft.EntityFrameworkCore.ChangeTracking.Internal.StateManager.SaveChangesAsync(Boolean acceptAllChangesOnSuccess, CancellationToken cancellationToken) at Microsoft.EntityFrameworkCore.DbContext.SaveChangesAsync(Boolean acceptAllChangesOnSuccess, CancellationToken cancellationToken)

Answer 1

I guess, what makes this error hard to reproduce is, that your codes runs asynchronously. You're getting the new max id by querying the current max id in the datacontext, which, in turn, may be changed right after you queried it, since another thread might also create a new entity.

What you should do, is putting a lock statement around the logic in your Bar()-method. By doing so, your second insert is being processed after your first one finished and thus all items share the same max id.

Something along the lines below should help, I didn't check it in visual studio, if it actually compiles, but you get the idea.

private object _lockObject = new object();
public async Task<bool> Bar()
{
   //setup object to be inserted to database

    try
    {
        // lock your changes, so they run in a safe order
        lock (_lockObject)
        {
            //the table can not have auto incrememnt so we read the max value
            objectToBeAdded.id = Context.Set<object>().Max(o => o.id) + 1;
            await Context.Set<object>().AddAsync(objectToBeAdded);
            await Context.SaveChangesAsync();
        }
        return true;
    }
    catch (Exception ex) {
        return false;
    }
}

Answer 2

As far as I can see, you do not remove the added object from the DbContext, so the duplicate key is still there.

You should

1. Either remove(detach) it
2. Or create a new DbContext from scratch each time just to be sure.