locked
Maximum Number of Retries Exceeded RRS feed

  • Question

  • User1122355199 posted

    Hello everyone and thanks for the help in advance.  I have been struggling through multiple issues involving deadlocks on a .Net Core 3.1 endpoint that processes incoming status calls from an external web service.  I am receiving numerous error messages:

    Microsoft.EntityFrameworkCore.Storage.RetryLimitExceededException: Maximum number of retries (25) exceeded while executing database operations with 'SqlServerRetryingExecutionStrategy'. See inner exception for the most recent failure. ---> Microsoft.EntityFrameworkCore.DbUpdateException: An error occurred while updating the entries. See the inner exception for details. ---> Microsoft.Data.SqlClient.SqlException (0x80131904): Transaction (Process ID 66) was deadlocked on lock | communication buffer resources with another process and has been chosen as the deadlock victim. Rerun the transaction. at Microsoft.Data.SqlClient.SqlCommand.<>c.b__164_0(Task`1 result) at System.Threading.Tasks.ContinuationResultTaskFromResultTask`2.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, Thread threadPoolThread) --- End of stack trace from previous location where exception was thrown --- at Microsoft.EntityFrameworkCore.Storage.RelationalCommand.ExecuteReaderAsync(RelationalCommandParameterObject parameterObject, CancellationToken cancellationToken) at Microsoft.EntityFrameworkCore.Storage.RelationalCommand.ExecuteReaderAsync(RelationalCommandParameterObject parameterObject, CancellationToken cancellationToken) at Microsoft.EntityFrameworkCore.Storage.RelationalCommand.ExecuteReaderAsync(RelationalCommandParameterObject parameterObject, CancellationToken cancellationToken) at Microsoft.EntityFrameworkCore.Update.ReaderModificationCommandBatch.ExecuteAsync(IRelationalConnection connection, CancellationToken cancellationToken) ClientConnectionId:fdf360e0-e951-4993-8448-9ce1c90dab96 Error Number:1205,State:45,Class:13 --- End of inner exception stack trace --- at Microsoft.EntityFrameworkCore.Update.ReaderModificationCommandBatch.ExecuteAsync(IRelationalConnection connection, CancellationToken cancellationToken) at Microsoft.EntityFrameworkCore.Update.Internal.BatchExecutor.ExecuteAsync(IEnumerable`1 commandBatches, IRelationalConnection connection, CancellationToken cancellationToken) at Microsoft.EntityFrameworkCore.Update.Internal.BatchExecutor.ExecuteAsync(IEnumerable`1 commandBatches, IRelationalConnection connection, CancellationToken cancellationToken) at Microsoft.EntityFrameworkCore.ChangeTracking.Internal.StateManager.SaveChangesAsync(IList`1 entriesToSave, CancellationToken cancellationToken) at Microsoft.EntityFrameworkCore.ChangeTracking.Internal.StateManager.SaveChangesAsync(DbContext _, Boolean acceptAllChangesOnSuccess, CancellationToken cancellationToken) at Microsoft.EntityFrameworkCore.Storage.ExecutionStrategy.ExecuteImplementationAsync[TState,TResult](Func`4 operation, Func`4 verifySucceeded, TState state, CancellationToken cancellationToken) --- End of inner exception stack trace --- at Microsoft.EntityFrameworkCore.Storage.ExecutionStrategy.ExecuteImplementationAsync[TState,TResult](Func`4 operation, Func`4 verifySucceeded, TState state, CancellationToken cancellationToken) at Microsoft.EntityFrameworkCore.DbContext.SaveChangesAsync(Boolean acceptAllChangesOnSuccess, CancellationToken cancellationToken)

    The code for the endpoint:

            public async Task UpdateStatus(string SMSSid, string SMSStatus, string ErrorCode)
            {
                using (var ctx = new myDbContext())
                {
    
    
                    try
                    {
                        var entity = ctx.tbl_Log_SMSMessages.FirstOrDefault(item => item.SmsMessageSid == SMSSid);
                        if (!(entity == null))
                        {
                            entity.SmsStatus = SMSStatus;
                            if (ErrorCode != "")
                            {
                                entity.ErrorCode = ErrorCode;
                            }
    
                            //ctx.SaveChanges();                  
    
                        }
                        await ctx.SaveChangesAsync();
    
                    }
                    catch(Exception ex)
                    {
                        string errmessage = "";
                        if (!String.IsNullOrEmpty(SMSSid))
                        {
                            errmessage += "SMSSid:  " + SMSSid + "; ";
                        }
                        else
                        {
                            errmessage += "SMSSid is null;  ";
                        }
    
                        if (!String.IsNullOrEmpty(SMSStatus))
                        {
                            errmessage += "SMSStatus:  " + SMSStatus + "; ";
                        }
                        else
                        {
                            errmessage += "SMSStatus is null;  ";
                        }
    
                        if (!String.IsNullOrEmpty(ErrorCode))
                        {
                            errmessage += "ErrorCode:  " + ErrorCode + "; ";
                        }
                        else
                        {
                            errmessage += "ErrorCode is null;  ";
                        }
    
                        errmessage += ex.ToString();
                        SendEmail sendEmail = new SendEmail(" UpdateSMSStatus Error", errmessage);
    
                    }                
    
                }
    
            }

    And the DbContext

        public class CeCemContext : DbContext
        {
            protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
            {
    
                optionsBuilder.UseSqlServer("connection; Connection Timeout=300; Max Pool Size=200; pooling='true'", sqlServerOptionsAction: sqlOptions =>
                {
                    sqlOptions.EnableRetryOnFailure(
                    maxRetryCount: 25,
                    maxRetryDelay: TimeSpan.FromSeconds(60),
                    errorNumbersToAdd: null);                
    
                    sqlOptions.CommandTimeout((int)TimeSpan.FromMinutes(10).TotalSeconds);
                    
                });
                
            }

    Running on Windows 2012 Server and SQL Server 2012

    Saturday, August 15, 2020 9:21 PM

Answers

  • User1120430333 posted

    I would use ADO,NET, SQL Command objects and execute a sproc. BTW, you don't have to read the record first. You can execute an Update T-SQL with a Where clause statement. 

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Monday, August 17, 2020 10:38 PM

All replies

  • User1120430333 posted

    You can see if using Read Uncommitted can be applied using a  System Transection scope with EF Core.

    https://www.c-sharpcorner.com/UploadFile/ff2f08/prevent-dead-lock-in-entity-framework/

    Your other option could be to use a stored procedure using T-SQL Nolock.

     https://www.sqlshack.com/understanding-impact-clr-strict-security-configuration-setting-sql-server-2017/

     

    Sunday, August 16, 2020 7:11 AM
  • User1122355199 posted

    Am I just better off moving to ADO.Net?

    Sunday, August 16, 2020 4:27 PM
  • User1120430333 posted

    kmcnet

    Am I just better off moving to ADO.Net?

    Of course, you are using the ADO.NET Entity Framework. ;) The basic concepts of how EF does things is centered around ADO.NET.  In a Web application where an application is using a database concurrently with users,  the Read Uncommitted using a transaction scope and NoLock using T-SQL is warranted if one knows about the record lockouts that MS SQL Server does by default. I have used option 1 in previous versions of EF with no problems on reads. You should try it and see what happens or call a sproc from EF with sproc using Nolock on the reads.

    Sunday, August 16, 2020 9:05 PM
  • User1122355199 posted

    Thanks for the response.  I guess my question was whether to use straight ADO.Net in lieu of Entity Framework.  My thinking is the performance might be quicker.  I'm not sure how to apply the NoLock in my situation since there is no pure read function.  Let me explain.  A worker function connects to the Twilio Api to send a SMS message.  Information is returned from the Api which is in turn inserted into the SQL Server table.  This function work reasonably well as long as the outbound volume is managed to approximately 500 records over the span of approximately 5 minutes.  Higher volume causes locking on the insert.  Once the messages are sent, the Api sends status updates to our server.  Records inserted by the worker function are updated with status information.  The current error I am receiving is occurring on the update of the record that was inserted by the worker function.  Since I have ample retries, I have trouble believing the record is still locked after the insert.  I have also received occasional ZombieCheck errors on the update, but haven't received them recently.  Since the update involves an initial select, I'm not sure how to manage the NoLock.

    Sunday, August 16, 2020 11:43 PM
  • User1120430333 posted

    You could try either way using EF or using ADO.NET to execute sproc

           public async Task UpdateStatus(string SMSSid, string SMSStatus, string ErrorCode)
            {
                using (var ctx = new myDbContext())
                
                {
    
                   try
                    {
                        ctx.Database.ExecuteSqlCommand(("SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;");
                        var entity = ctx.tbl_Log_SMSMessages.FirstOrDefault(item => item.SmsMessageSid == SMSSid);
                        if (!(entity == null))
                        {
                            entity.SmsStatus = SMSStatus;
                            if (ErrorCode != "")
                            {
                                entity.ErrorCode = ErrorCode;
                            }
    
                            //ctx.SaveChanges();                  
    
                        }
                        await ctx.SaveChangesAsync();
    
                    }
                    catch(Exception ex)
                    {
                        string errmessage = "";
                        if (!String.IsNullOrEmpty(SMSSid))
                        {
                            errmessage += "SMSSid:  " + SMSSid + "; ";
                        }
                        else
                        {
                            errmessage += "SMSSid is null;  ";
                        }
    
                        if (!String.IsNullOrEmpty(SMSStatus))
                        {
                            errmessage += "SMSStatus:  " + SMSStatus + "; ";
                        }
                        else
                        {
                            errmessage += "SMSStatus is null;  ";
                        }
    
                        if (!String.IsNullOrEmpty(ErrorCode))
                        {
                            errmessage += "ErrorCode:  " + ErrorCode + "; ";
                        }
                        else
                        {
                            errmessage += "ErrorCode is null;  ";
                        }
    
                        errmessage += ex.ToString();
                        SendEmail sendEmail = new SendEmail(" UpdateSMSStatus Error", errmessage);
    
                    }                
    
                }
    
            }

    sproc

    Nolock
    Select record
    Update
    

    Monday, August 17, 2020 4:13 AM
  • User1122355199 posted

    Thanks for the response.  Since I'm using core, I used:

                        ctx.Database.ExecuteSqlRaw("SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;");
    

    Unfortunately, it doesn't seem to have worked.

    Monday, August 17, 2020 5:33 PM
  • User1120430333 posted

    I would use ADO,NET, SQL Command objects and execute a sproc. BTW, you don't have to read the record first. You can execute an Update T-SQL with a Where clause statement. 

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Monday, August 17, 2020 10:38 PM