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)
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.
If you find the post has answered your issue, then please mark post as 'answered'.
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.
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.
If you find the post has answered your issue, then please mark post as 'answered'.
Member
317 Points
1700 Posts
Maximum Number of Retries Exceeded
Aug 15, 2020 09:21 PM|kmcnet|LINK
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:
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
Contributor
4663 Points
3967 Posts
Re: Maximum Number of Retries Exceeded
Aug 16, 2020 07:11 AM|DA924|LINK
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/
Member
317 Points
1700 Posts
Re: Maximum Number of Retries Exceeded
Aug 16, 2020 04:27 PM|kmcnet|LINK
Am I just better off moving to ADO.Net?
Contributor
4663 Points
3967 Posts
Re: Maximum Number of Retries Exceeded
Aug 16, 2020 09:05 PM|DA924|LINK
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.
Member
317 Points
1700 Posts
Re: Maximum Number of Retries Exceeded
Aug 16, 2020 11:43 PM|kmcnet|LINK
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.
Contributor
4663 Points
3967 Posts
Re: Maximum Number of Retries Exceeded
Aug 17, 2020 04:13 AM|DA924|LINK
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
Member
317 Points
1700 Posts
Re: Maximum Number of Retries Exceeded
Aug 17, 2020 05:33 PM|kmcnet|LINK
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.
Contributor
4663 Points
3967 Posts
Re: Maximum Number of Retries Exceeded
Aug 17, 2020 10:38 PM|DA924|LINK
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.