DB deadlock resilience (extended to transient errors) using Decorator for SQL connection and command.
- Compatible with Dapper.
- Quick integration to existing Microsoft.Data.SqlClient's connection and command with no changes to existing repo.
- Makes use of Polly library for fault tolerance policy and recovery.
- Along with linear back-off on each reattempt.
- Capabilities to Log the context and attempts using Microsoft.Extensions.Logging
- Multiple DB Policies can be further cascaded at one place. viz, additional transient errors (like 40501, 40197, 40550), network timeout faults and recovery (like 40, 10053) with minimal modification.
- Derived type dispose pattern as suggested by Microsoft.
- netstandard2.0
- Can be referenced up untill Core 3.1 and legacy .Net FW 4.7
- Library can easily be upgrade to Core 3.1 lib or other.
- Corresponding test as a driver using Xunit, 3.1 Core.
- Deadlock query on SQL server using single client and single session.
- More on script: https://stackoverflow.com/questions/70019632/simulate-a-dead-lock-on-sql-server-using-single-client-and-single-session
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);
});
Using a Decorator pattern to wrap the Microsoft.Data.SqlClient'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, don't forget to use PolicyRegister if you have more than one policy).
More on that approach: https://stackoverflow.com/questions/70062183/need-a-built-in-way-to-add-deadlock-resilience-to-dapper-for-existing-repos-with/70090881#70090881
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 Microsoft.Data.SqlClient's connection and command will be able to inject and retry with custom policies.