Need to make all the existing repos (around 30+) fault tolerant to deadlock and recover from it with log and wait approach.
Tried with success: I've answered below a custom SqlResiliencyPolicy using Polly after some research and tailored it to the project.
But, what I seek: The present way (PFB answered), demands me to either
await _policy.ExecuteAsync ORIAsyncPolicy param. and later call the intended method. Sort of extension to IDbConnection:public static Task<T> GetAsync<T>(this IDbConnection connection, object primaryKey, IAsyncPolicy policy) => return await _policy.ExecuteAsync(async () => GetAsync<T> (...));
In both ways, I need to change all my 30+ repos. But, is there a built-in way in dapper/some-other-approaches, where we can
"configure a Policy in startup and auto-magically all DB calls via dapper become resilient (fall backs to their fault tolerant mechanism) Similar to the ways of http clients resilience where policy is added while you register a client"
By this: will've code changes to minimum, need not touch repos but only the startup.
I've a below approach and sort of need an improvement over it.
Below will be by-far the apt approach to have minimal/no changes to the existing repo. Kudos to the blog written by @Sergey Akopov and to my colleague who pointed at this blog.
Short answer: Use a Decorator pattern to wrap the SQL Client's Connection and Command instances and Inject the retry policy from Polly to these decorators. By this, will be able to wrap all the SQL execution endpoints with retry policy. This will be compatible with Dapper, as it's an extension to IDbConnection.
Create a DI'able Retry policies which encapsulate the policy inside it. Also, We can totally decouple the Policy to separate class and register it for DI (not shown in this answer, but this is followed in other answers, don't forget to use PolicyRegister if you have more than one policy).
Git repo: https://github.com/VinZCodz/SqlTransientFaultHandling
Details:
Interface for Policy, no async methods since Microsoft.Data.SqlClient endpoints are not async.
public interface IRetryPolicy
{
void Execute(Action operation);
TResult Execute<TResult>(Func<TResult> operation);
}
Concrete implementation, which embeds the policy inside and wraps retry logic for all the DB calls via Sql Client thus Dapper.
public class RetryPolicy : IRetryPolicy
{
private readonly ILogger<RetryPolicy> _logger;
private readonly Policy _retryPolicy;
private readonly ISet<int> _transientDbErrors = new HashSet<int>(new[] { 1205 });
private const int _transientErrorRetries = 3;
public RetryPolicy(ILogger<RetryPolicy> logger)
{
_logger = logger;
_retryPolicy = Policy
.Handle<SqlException>(ex => _transientDbErrors.Contains(ex.Number))
.WaitAndRetry(
retryCount: _transientErrorRetries,
sleepDurationProvider: attempt => TimeSpan.FromMilliseconds(attempt * 100),
onRetry: LogRetryAction);
}
public void Execute(Action operation) => _retryPolicy.Execute(operation.Invoke);
public TResult Execute<TResult>(Func<TResult> operation) => _retryPolicy.Execute(() => operation.Invoke());
private void LogRetryAction(Exception exception, TimeSpan sleepTime, int reattemptCount, Context context) =>
_logger.LogWarning(
exception,
$"Transient DB Failure while executing query, error number: {((SqlException)exception).Number}; reattempt number: {reattemptCount}");
}
Now, somehow we need to inject this policy to SqlClient's con and cmd, need a sealed class which 'is-a' DbConnection (DAL endpoints will remain intact) and also 'has-a' DbConnection (mimic the operations but with retries):
public sealed class ReliableSqlDbConnection : DbConnection
{
private readonly SqlConnection _underlyingConnection;
private readonly IRetryPolicy _retryPolicy;
private bool _disposedValue;
private string _connectionString;
public ReliableSqlDbConnection(string connectionString, IRetryPolicy retryPolicy)
{
_connectionString = connectionString;
_retryPolicy = retryPolicy;
_underlyingConnection = new SqlConnection(connectionString);
}
public override string ConnectionString
{
get => _connectionString;
set => _underlyingConnection.ConnectionString = _connectionString = value;
}
public override void Open()
{
_retryPolicy.Execute(() =>
{
if (_underlyingConnection.State != ConnectionState.Open)
{
_underlyingConnection.Open();
}
});
}
public override string Database => _underlyingConnection.Database;
public override string DataSource => _underlyingConnection.DataSource;
public override string ServerVersion => _underlyingConnection.ServerVersion;
public override ConnectionState State => _underlyingConnection.State;
public override void ChangeDatabase(string databaseName) => _underlyingConnection.ChangeDatabase(databaseName);
public override void Close() => _underlyingConnection.Close();
protected override DbTransaction BeginDbTransaction(IsolationLevel isolationLevel) => _underlyingConnection.BeginTransaction(isolationLevel);
protected override DbCommand CreateDbCommand() => new ReliableSqlDbCommand(_underlyingConnection.CreateCommand(), _retryPolicy);
}
Since, we are instantiating SqlConnection whenever its asked for we also need to dispose it properly, following derived type dispose pattern as suggested by Microsoft:
protected override void Dispose(bool disposing)
{
if (!_disposedValue)
{
if (disposing)
{
if (_underlyingConnection.State == ConnectionState.Open)
{
_underlyingConnection.Close();
}
_underlyingConnection.Dispose();
}
_disposedValue = true;
}
base.Dispose(disposing);
}
Following similar approach to DbCommand:
public sealed class ReliableSqlDbCommand : DbCommand
{
private readonly SqlCommand _underlyingSqlCommand;
private readonly IRetryPolicy _retryPolicy;
private bool _disposedValue;
public ReliableSqlDbCommand(SqlCommand command, IRetryPolicy retryPolicy)
{
_underlyingSqlCommand = command;
_retryPolicy = retryPolicy;
}
public override string CommandText
{
get => _underlyingSqlCommand.CommandText;
set => _underlyingSqlCommand.CommandText = value;
}
public override int CommandTimeout
{
get => _underlyingSqlCommand.CommandTimeout;
set => _underlyingSqlCommand.CommandTimeout = value;
}
public override CommandType CommandType
{
get => _underlyingSqlCommand.CommandType;
set => _underlyingSqlCommand.CommandType = value;
}
public override bool DesignTimeVisible
{
get => _underlyingSqlCommand.DesignTimeVisible;
set => _underlyingSqlCommand.DesignTimeVisible = value;
}
public override UpdateRowSource UpdatedRowSource
{
get => _underlyingSqlCommand.UpdatedRowSource;
set => _underlyingSqlCommand.UpdatedRowSource = value;
}
protected override DbConnection DbConnection
{
get => _underlyingSqlCommand.Connection;
set => _underlyingSqlCommand.Connection = (SqlConnection)value;
}
protected override DbParameterCollection DbParameterCollection => _underlyingSqlCommand.Parameters;
protected override DbTransaction DbTransaction
{
get => _underlyingSqlCommand.Transaction;
set => _underlyingSqlCommand.Transaction = (SqlTransaction)value;
}
public override void Cancel() => _underlyingSqlCommand.Cancel();
public override int ExecuteNonQuery() => _retryPolicy.Execute(() => _underlyingSqlCommand.ExecuteNonQuery());
public override object ExecuteScalar() => _retryPolicy.Execute(() => _underlyingSqlCommand.ExecuteScalar());
public override void Prepare() => _retryPolicy.Execute(() => _underlyingSqlCommand.Prepare());
protected override DbParameter CreateDbParameter() => _underlyingSqlCommand.CreateParameter();
protected override DbDataReader ExecuteDbDataReader(CommandBehavior behavior) => _retryPolicy.Execute(() => _underlyingSqlCommand.ExecuteReader(behavior));
protected override void Dispose(bool disposing)
{
if (!_disposedValue)
{
if (disposing)
{
_underlyingSqlCommand.Dispose();
}
_disposedValue = true;
}
base.Dispose(disposing);
}
}
Existing DAL side:
DI:
services.AddScoped<IRetryPolicy, RetryPolicy>();
services.Configure<DbConnectionOption>(options =>
{
options.ConnectionString = connectionString;
});
Lazy load the Decorator:
_connection = new Lazy<IDbConnection>(() =>
{
return new ReliableSqlDbConnection(_dbOptions.ConnectionString, _retryPolicy);
});
Xunit Test: This test actually creates a deadlock on single session and reties it.
Thanks to @Martin Smith for awesome script, more on script: Simulate a dead lock on SQL server using single client and single session
[Fact]
public void It_creates_reliablesqldbConnection_and_deadlock_itself_to_log_and_retry()
{
var logger = new FakeLogger<RetryPolicy>(); //create your own logger.
using var reliableSqlDbConnection = new ReliableSqlDbConnection(_fixture.Configuration["ConnectionStrings:DataContext"],
new RetryPolicy(logger)); //create your own fixture.
//Awesome script which deadlocks itself on single con and process with it's meta data.
Assert.ThrowsAsync<SqlException>(() => reliableSqlDbConnection.ExecuteAsync(
@"BEGIN TRAN
CREATE TYPE dbo.OptionIDs AS TABLE( OptionID INT PRIMARY KEY )
EXEC ('DECLARE @OptionIDs dbo.OptionIDs;')
ROLLBACK "));
Assert.Equal(LogLevel.Warning, logger.Logs.Select(g => g.Key).First());
var retries = logger.Logs[LogLevel.Warning].First();
Assert.Equal(3, retries.Count());
Assert.Equal("Transient DB Failure while executing query, error number: 1205; reattempt number: 1", retries.First());
}
Summary:
With this, Open Connection, ExecuteReader, ExecuteScalar, ExecuteNonQuery etc will have retry capabilities wrapped around them, which will ultimately be invoked by all Dapper endpoints.
By this, will've code changes to minimum, need not touch repos but only the startup. Just by providing a wrapper/decorator to SqlClient's connection and command will be able to inject and retry with custom policies.