Can SqlConfigurableRetryProviders be re-used

[Vandersteen]

Is it advised to always create a new retry provider for every connection / command or can they be re-used across many connections / commands. It is unclear from the documentation what the best practices are:

private static readonly SqlRetryLogicBaseProvider DefaultRetryProvider = SqlConfigurableRetryFactory.CreateIncrementalRetryProvider(
                new SqlRetryLogicOption()
                {
                    NumberOfTries = 5,
                    MaxTimeInterval = TimeSpan.FromSeconds(10)
                }
            );
            
//...

public IConnection GetConnection()
{
    var connection = new SqlConnection(_options.ConnectionString);
    connection.RetryLogicProvider = DefaultRetryProvider;
    return connection;
}

vs creating it for every connection

public IConnection GetConnection()
{
    var connection = new SqlConnection(_options.ConnectionString);
    connection.RetryLogicProvider = SqlConfigurableRetryFactory.CreateIncrementalRetryProvider(...);
    return connection;
}

Same thing goes for commands

[arellegue]

You may find the information at https://devblogs.microsoft.com/azure-sql/configurable-retry-logic-for-microsoft-data-sqlclient/ to have all the answers to your queries.

[DavoudEshtehari]

If you have a similar retry logic with all the objects, it's safe re-using a provider, which is exposed in SqlConfigurableRetryFactory. The way you should create a new provider and consume it, depends on your application, like concurrent calls and maintenance policy.

I'll convert this issue to a discussion.

[Vandersteen]

Thanks for the info.
Aside from the app.config (which set's it globally), can the retry policy be set for a specific set of connections.

We have 2 IDbConnection builders in our app:

• primary database
• read-only replica database(s)

So we have something like:

public IDbConnection GetPrimaryConnection()
{
    var connection = new SqlConnection(_options.ConnectionString);
    connection.RetryLogicProvider = SqlConfigurableRetryFactory.CreateIncrementalRetryProvider(...);
    return connection;
}

public IDbConnection GetReadOnlyConnection()
{
    var connection = new SqlConnection(_options.ReadOnlyConnectionString);
    connection.RetryLogicProvider = SqlConfigurableRetryFactory.CreateIncrementalRetryProvider(...);
    return connection;
}

Could we have different set of retry policies using the app.config somehow.

If not, is there a way to set the retry policy for a connection, but also for all commands executed from that connection when creating that connection ?

I would like to avoid having to set the 'default' retry policy for all commands executed on a connection separately.
We are using it mostly with dapper ( IDbConnection.QueryAsync<T>() )

Or isn't that necessary in most cases (and the connection retry should be enough to handle most cases of transient errors).

[DavoudEshtehari]

Could we have different set of retry policies using the app.config somehow.

Only when you need a default retry provider for all connections/commands on your application without manipulating it, you can use app.config.

If not, is there a way to set the retry policy for a connection, but also for all commands executed from that connection when creating that connection ?

SqlConnection and SqlCommand retry providers work separately. I don't suggest using the same provider for connections and commands since their retry logics should be different based on different transient errors.

[AMeyerInkSoft]

I'm adding to this discussion because I had a similar concern and I wasn't able to readily interpret or confirm behavior elsewhere on the web. From what I can tell, we can safely share a single retry logic provider object instance between all connection objects because as a 'provider' class, a SqlConnection object will use this provider object as guidance for creating and tracking a retry counter on the SqlConnection object instead of incrementing a retry counter in the provider object (or maybe it just returns an enumerator object that is never reused). Put another way, we hopefully won't get some sort of crazy problem where all connections fail after only one connection fails because they all share the same retry provider instance.

I haven't made the time to comb through all of the code associated with the retry feature, but the best indication I've seen to confirm that a shared provider instance between all SqlConnection instances is safe is that if the SqlConnection.RetryLogicProvider is null (default), the static, default provider (perhaps constructed via XML config) is used and is shared among all connections (an internal SqlConfigurableRetryLogicManager.ConnectionProvider class & property).

Let me know, however, if my understanding or interpretation of the documentation is incorrect.

[DavoudEshtehari]

A provider object can be shared among multiple connection or command objects, as it does not maintain any state or counter itself. Instead, it creates and returns a SqlRetryLogicBase object, which encapsulates the retry logic for a specific operation. The SqlRetryLogicBase object is responsible for tracking the number of retries, the next interval time, and the transient condition. Therefore, each connection or command object will have its own retry logic object, even if they use the same provider object (which is not recommended).

The default SqlConnection.RetryLogicProvider property is null, which means that the connection will not silently touch any retry logics.