I've read the question (with updates) at My custom DbExecutionStrategy is not being called but don't think it's the same issue.
I've written a custom execution strategy and included log information so I know when it gets called. However, I am still getting SQL deadlocks from queries with no indication that my custom execution strategy is being called.
Custom execution strategy:
using System;
using System.Data.Entity.Infrastructure;
using System.Data.SqlClient;
using System.Diagnostics;
using log4net;
namespace MyDataModel
{
// Based on https://stackoverflow.com/questions/13159326/implementing-retry-logic-for-deadlock-exceptions
// and https://www.codeproject.com/Tips/758469/Implementing-Connection-Resiliency-with-Entity-Fra
public class CustomExecutionStrategy : DbExecutionStrategy
{
private int _totalFailures;
public CustomExecutionStrategy()
{
}
public CustomExecutionStrategy(int maxRetryCount, TimeSpan maxDelay)
: base(maxRetryCount, maxDelay)
{
}
public static string StackTrace()
{
var st = new StackTrace();
var sfs = st.GetFrames();
var result = string.Empty;
foreach (var sf in sfs)
{
var method = sf.GetMethod();
result += (string.IsNullOrEmpty(result) ? string.Empty : "->") + method.DeclaringType.Name + "." + method.Name;
}
return result + " ";
}
protected override bool ShouldRetryOn(Exception exception)
{
var log = LogManager.GetLogger("Logger");
if (exception is TimeoutException)
{
_totalFailures++;
log.Info($"Retrying {exception.GetType().Name} ({_totalFailures}) - {StackTrace()}");
return true;
}
if (exception is SqlException sqlException)
{
foreach (SqlError err in sqlException.Errors)
{
// Enumerate through all errors found in the exception.
if (Enum.IsDefined(typeof(RetryableSqlErrors), err.Number))
{
_totalFailures++;
log.Info($"Retrying {exception.GetType().Name}, {(RetryableSqlErrors)err.Number} ({_totalFailures}) - {StackTrace()}");
return true;
}
}
}
log.Error($"Not retrying {exception.GetType().Name} - {StackTrace()}");
return false;
}
}
}
The setup:
using System.Data.Entity;
using MyDataModel.Extensions;
namespace MyDataModel
{
internal class EntityFrameworkConfiguration : DbConfiguration
{
public EntityFrameworkConfiguration()
{
SetExecutionStrategy("System.Data.SqlClient",
() => new CustomExecutionStrategy(Setting.MaxRetry, Setting.ShortWait));
}
}
}
And, in the App.Config:
<entityFramework codeConfigurationType="MyDataModel.EntityFrameworkConfiguration, MyDataModel">
.
.
.
</entityFramework>
Here are some log extracts:
2020-08-11 04:09:15,047 [54] [26390536] ERROR MyDataModel.CustomExecutionStrategy - Not retrying SqlException - <>c__DisplayClass61_1.<FixItemAndRetry>b__1->BaseEntity.FixItemAndRetry...
2020-08-11 04:12:02,510 [52] [26557999] ERROR MyDataModel.CustomExecutionStrategy - Not retrying SqlException - <>c__DisplayClass61_1.<FixItemAndRetry>b__1->BaseEntity.FixItemAndRetry...
2020-08-11 11:09:53,350 [29] [3010774] INFO MyDataModel.CustomExecutionStrategy - Retrying SqlException, DeadlockVictim (1) - Invoice..ctor->SyncInvoiceWithPayment.Start...
2020-08-11 11:56:12,047 [3] [5789470] INFO MyDataModel.CustomExecutionStrategy - Retrying SqlException, DeadlockVictim (1) - Invoice..ctor->D365SyncInvoiceWorker.Start...
2020-08-11 11:56:17,048 [3] [5794471] INFO MyDataModel.CustomExecutionStrategy - Retrying SqlException, DeadlockVictim (2) - Invoice..ctor->D365SyncInvoiceWorker.Start...
2020-08-11 14:16:20,111 [25] [3930388] INFO MyDataModel.CustomExecutionStrategy - Retrying SqlException, DeadlockVictim (1) - Invoice..ctor->SyncInvoiceWithPayment.Start...
2020-08-11 14:16:25,110 [25] [3935386] INFO MyDataModel.CustomExecutionStrategy - Retrying SqlException, DeadlockVictim (2) - Invoice..ctor->SyncInvoiceWithPayment.Start...
2020-08-11 14:40:35,089 [13] [5385366] INFO MyDataModel.CustomExecutionStrategy - Retrying SqlException, DeadlockVictim (1) - Invoice..ctor->SyncInvoiceWithPayment.Start...
2020-08-11 14:40:40,089 [13] [5390365] INFO MyDataModel.CustomExecutionStrategy - Retrying SqlException, DeadlockVictim (2) - Invoice..ctor->SyncInvoiceWithPayment.Start...
2020-08-11 14:58:51,459 [20] [6481736] INFO MyDataModel.CustomExecutionStrategy - Retrying SqlException, DeadlockVictim (1) - Invoice..ctor->SyncInvoiceWithPayment.Start...
2020-08-11 14:58:53,958 [20] [6484234] INFO MyDataModel.CustomExecutionStrategy - Retrying SqlException, DeadlockVictim (2) - Invoice..ctor->SyncInvoiceWithPayment.Start...
So I know the custom execution strategy is being called and is retrying some deadlocks.
But also from the log, I have:
2020-08-11 11:09:46,783 [26] [3004206] INFO Utility.Settlement - Getting invoice lines
2020-08-11 11:09:48,352 [26] [3005775] ERROR Utility.Settlement - Stl: <N0675833635639> Save Failed !
Error: An error occurred while reading from the store provider's data reader. See the inner exception for details.
InnerError: Transaction (Process ID 409) was deadlocked on lock | communication buffer resources with another process and has been chosen as the deadlock victim. Rerun the transaction.
From the code and interpreting the error message, this occurs when trying to query records from the database.
How do I intercept EF6 (version 6.4.4) queries and get it to retry SQL deadlocks on those as well?
