Issue using microsoft.data.sqlclient RetryLogicProvider #2536
Description
Hi, I'm using azure portal SQL databases in my development for use in cloud infrastructure. Those databases are prone to transient errors. That is why I decided to try and use the new Retry logic feature in the microsoft.data.sqlclient to better handle those cases.
I set up a POC to try and make the feature work in the case the database has to do maintenance. On Microsoft documentation and troubleshooting, apparently using this PowerShell command "Invoke-AzSqlDatabaseFailover" can simulate the maintenance of a database.
Unfortunately, when I'm trying the retry feature when the connection is in use and getting the data from the database and I end up severing the connection in the middle of the reading process, I end up catching an exception like this one (A transport-level error has occurred when receiving results from the server. (provider: TCP Provider, error: 0 - An existing connection was forcibly closed by the remote host.)) instead of having the retry being called.
Here is my POC code :
private const string CnnStringFormat = "Server={myServer};Initial Catalog={myDatabase};User ID={username};Password={Password};MultipleActiveResultSets=False;Encrypt=False;TrustServerCertificate=True;Connection Timeout=30; Max Pool Size=100; ConnectRetryCount=3; ConnectRetryInterval = 20;";
private const string Query = "SELECT * FROM [dbo].[BigTable]";
private const string Query2 = "SELECT * FROM [sql].[BIG_INVOICE]";
private const string QueryInsert = "INSERT INTO [dbo].[BigTable] ([Id],[FirstName],[LastName],[Email],[Salary],[City],[Country],[ModifiedDate]) VALUES({0},'Sophia','Miller','c67b4b0dfadb41d0b036',16415.78,'New York City','China','2024-05-23 11:15:29.8000000')";
static async Task Main(string[] args)
{
// 1. Define the retry logic parameters
var options = new SqlRetryLogicOption()
{
NumberOfTries = 5,
MaxTimeInterval = TimeSpan.FromSeconds(20),
DeltaTime = TimeSpan.FromSeconds(15),
AuthorizedSqlCondition = null,
// error number 3702 : Cannot drop database "xxx" because it is currently in use.
TransientErrors = new int[] { 64, 233, 997, 1204, 1205, 1222, 3702, 4060, 4221, 10053, 10054, 10060, 10061, 10928, 10929, 11001, 26023, 26040, 26041, 40143, 40197, 40501, 40540, 40613, 49918, 49919, 49920 }
};
// 2. Create a retry provider
var provider = SqlConfigurableRetryFactory.CreateExponentialRetryProvider(options);
// define the retrying event to report execution attempts
provider.Retrying += (object s, SqlRetryingEventArgs e) =>
{
int attempts = e.RetryCount + 1;
Console.ForegroundColor = ConsoleColor.Yellow;
Console.WriteLine($"attempt {attempts} - current delay time:{e.Delay} \n");
Console.ForegroundColor = ConsoleColor.DarkGray;
if (e.Exceptions[e.Exceptions.Count - 1] is SqlException ex)
{
Console.WriteLine($"{ex.Number}-{ex.Message}\n");
}
else
{
Console.WriteLine($"{e.Exceptions[e.Exceptions.Count - 1].Message}\n");
}
// It is not good practice to do time-consuming tasks inside the retrying event which blocks the running task.
// Use parallel programming patterns to mitigate it.
if (e.RetryCount == provider.RetryLogic.NumberOfTries - 1)
{
Console.WriteLine("This is the last chance to execute the command before throwing the exception.");
Console.WriteLine("Press Enter when you're ready:");
Console.ReadLine();
Console.WriteLine("continue ...");
}
};
SqlConnection sqlConnection = new SqlConnection();
try
{
DbConnectionStringBuilder connStringBuilder = new SqlConnectionStringBuilder(CnnStringFormat);
using (DbConnection connection = connStringBuilder.Connection())
{
sqlConnection = connection as SqlConnection;
sqlConnection.RetryLogicProvider = provider;
sqlConnection.Open();
for (int i = 0; i < 2000000; i++)
{
await sqlConnection.ExecuteSqlAsync(string.Format(QueryInsert, 1));
Console.WriteLine(i.ToString());
}
//using (IDbCommand command = sqlConnection.CreateCommandSql(Query, null, 0))
//{
// CancellationToken cancellationToken = default;
// ((SqlCommand)command).RetryLogicProvider = provider;
// IDataReader reader = await command.GetReaderAsync(CommandBehavior.Default, cancellationToken);
// int count = 0;
// if (reader is SqlDataReader sqlDataReader)
// {
// while (await sqlDataReader.ReadAsync(cancellationToken))
// {
// count++;
// Console.Write(reader.GetValue(0).ToString() + " ");
// Console.WriteLine(count.ToString());
// }
// command.Cancel();
// connection.Close();
// }
//}
sqlConnection.Close();
}
}
catch (Exception ex)
{
Console.WriteLine(ex.ToString());
sqlConnection.Close();
}
}`
Since I would expect the retry logic to be active in this POC once the connection is lost, I was wondering if I'm missing anything with my development. I'm expecting the connection to be retried and not the application to stop immediately when the exception happens.