Issue using microsoft.data.sqlclient RetryLogicProvider

[avillemaire]

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.

[arellegue]

Thank you for reporting an issue with RetryLogicProvider.

I noticed that the repro provided does not compile due to a couple of issues:

1. DbConnectionStringBuilder does not contain a definition for Connection
2. QueryInsert is undefined.

However, in the error message you reported it seems that the actual error code is getting lost and only showing error code 0. Since error code 0 is not in the transient error list to retry on, the retry logic is not getting triggered.

Could you update the repro so we can use that to investigate further, please?

[avillemaire]

I used a private connection and database, I wasn't clear in that fact, but the databases I used are basic database service tier with nothing special in it. It would be possible to use your own for testing. As for insertQuery, it's the same, it will just depend on the table you use for testing, but it's a simple SQL Insert element : "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')"

I'm sorry, but I don't think I can give my credentials to my database for you as it can contain sensitive information.
Do you think it's possible to do without those or you absolutely need them?

[avillemaire]

I also just tried to setup TransientErrors like that to ensure I had as much error numbers as possible and the same exception happens instead of having the retry logic happening :
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 = Enumerable.Range(0, 50000) };

[arellegue]

Could you try adding this line of code as the first line to see if the PoC will throw an actual retriable error code instead of error code 0:

AppContext.SetSwitch("Switch.Microsoft.Data.SqlClient.UseManagedNetworkingOnWindows", true);

[avillemaire]

This is the exception error I get when I add the line of code you gave me :
A transport-level error has occurred when receiving results from the server. (provider: TCP Provider, error: 35 - An internal exception was caught).

Message : Unable to read data from the transport connection: An existing connection was forcibly closed by the remote host..
Stack trace : at System.Net.Sockets.Socket.AwaitableSocketAsyncEventArgs.ThrowException(SocketError error, CancellationToken cancellationToken)
at System.Net.Sockets.Socket.AwaitableSocketAsyncEventArgs.System.Threading.Tasks.Sources.IValueTaskSource<System.Int32>.GetResult(Int16 token)
at Microsoft.Data.SqlClient.SNI.SNINetworkStream.d__1.MoveNext()
at Microsoft.Data.SqlClient.SNI.SslOverTdsStream.d__5.MoveNext()
at System.Net.Security.SslStream.d__1611.MoveNext() at System.Runtime.CompilerServices.PoolingAsyncValueTaskMethodBuilder1.StateMachineBox1.System.Threading.Tasks.Sources.IValueTaskSource<TResult>.GetResult(Int16 token) at System.Net.Security.SslStream.<ReadAsyncInternal>d__1631.MoveNext()
at System.Runtime.CompilerServices.PoolingAsyncValueTaskMethodBuilder1.StateMachineBox1.System.Threading.Tasks.Sources.IValueTaskSource.GetResult(Int16 token)
at Microsoft.Data.SqlClient.SNI.SNISslStream.d__1.MoveNext()

[arellegue]

Could you comment out the TransientErrors in the var options = new SqlRetryLogicOption() block please just to see if the default transient error list in SqlConfigurableRetryFactory will catch the error to retry on.

[avillemaire]

I just tried it with the transientError list commented, the default transient error list in [SqlConfigurableRetryFactory] doesn't pick this error for some reason. it will throw the exception instead of retrying even with the default list.

[avillemaire]

Verified if I could give the connection string for the connection and the Query. I added them in the main issue comment code part. You should be able to test it on your side as well now.

[arellegue]

Oh, I am so sorry I did not expect you to provide private connection string. Since this is a public forum, you may want to remove any private information in your connection string.

However, I want to request one more information. An event source trace log would really help to troubleshoot this issue. More information on how to capture event source trace log can be found here.

[avillemaire]

SqlError.ctor | ERR | Info Number 0, Error State 0, Error Class 20, Error Message 'A transport-level error has occurred when receiving results from the server. (provider: TCP Provider, error: 35 - An internal exception was caught)', Procedure '', Line Number 0, Batch Index -1
CachedAsyncState.ResetAsyncState | API | ObjectId 2, Client Connection Id 5432bbf2-3dac-498d-aad5-3a184f995dcc, AsyncCommandInProgress=True
TdsParserStateObjectManaged.Dispose | Info | State Object Id 1, Session Id 5432bbf2-3dac-498d-aad5-3a184f995dcc, Disposing session Handle and counters.
TdsParserStateObjectManaged.Dispose | Info | State Object Id 1, Session Id 5432bbf2-3dac-498d-aad5-3a184f995dcc, sessionHandle is available, disposing session.
SqlConnection.OnError | Info | Object Id 2, Connection broken.
TdsParserStateObjectManaged.ReadAsyncCallback | Info | State Object Id 1, Session Id 5432bbf2-3dac-498d-aad5-3a184f995dcc, Error code returned 1
<sc.SqlInternalConnectionTds.BreakConnection|RES|CPOOL> 4, Breaking connection.
TdsParserStateObjectManaged.Dispose | Info | State Object Id 1, sessionHandle not available, could not dispose session.
Microsoft.Data.SqlClient.SqlException (0x80131904): A transport-level error has occurred when receiving results from the server. (provider: TCP Provider, error: 35 - An internal exception was caught)
---> System.IO.IOException: Unable to read data from the transport connection: An existing connection was forcibly closed by the remote host..
---> System.Net.Sockets.SocketException (10054): An existing connection was forcibly closed by the remote host.
--- End of inner exception stack trace ---
at System.Net.Sockets.Socket.AwaitableSocketAsyncEventArgs.ThrowException(SocketError error, CancellationToken cancellationToken)
at System.Net.Sockets.Socket.AwaitableSocketAsyncEventArgs.System.Threading.Tasks.Sources.IValueTaskSource<System.Int32>.GetResult(Int16 token)
at Microsoft.Data.SqlClient.SNI.SNINetworkStream.ReadAsync(Memory1 buffer, CancellationToken cancellationToken) at Microsoft.Data.SqlClient.SNI.SslOverTdsStream.ReadAsync(Memory1 buffer, CancellationToken cancellationToken)
at System.Net.Security.SslStream.EnsureFullTlsFrameAsync[TIOAdapter](CancellationToken cancellationToken, Int32 estimatedSize)
at System.Runtime.CompilerServices.PoolingAsyncValueTaskMethodBuilder1.StateMachineBox1.System.Threading.Tasks.Sources.IValueTaskSource.GetResult(Int16 token)
at System.Net.Security.SslStream.ReadAsyncInternal[TIOAdapter](Memory1 buffer, CancellationToken cancellationToken) at System.Runtime.CompilerServices.PoolingAsyncValueTaskMethodBuilder1.StateMachineBox1.System.Threading.Tasks.Sources.IValueTaskSource<TResult>.GetResult(Int16 token) at Microsoft.Data.SqlClient.SNI.SNISslStream.ReadAsync(Memory1 buffer, CancellationToken cancellationToken)
at Microsoft.Data.SqlClient.SqlCommand.EndExecuteNonQueryAsync(IAsyncResult asyncResult)
at System.Threading.Tasks.TaskFactory1.FromAsyncCoreLogic(IAsyncResult iar, Func2 endFunction, Action1 endAction, Task1 promise, Boolean requiresSynchronization)
--- End of stack trace from previous location ---
at Insight.Database.DBConnectionExtensions.<>c__DisplayClass0_0.<b__1>d.MoveNext()
--- End of stack trace from previous location ---
at Insight.Database.DBConnectionExtensions.ExecuteAsyncAndAutoClose[T](IDbConnection connection, Object parameters, Func2 getCommand, Boolean callGetReader, Func3 translate, CommandBehavior commandBehavior, CancellationToken cancellationToken, Object outputParameters)
at POCVersionNET.Program.Main(String[] args) in C:\POC\microsoft data sql client\MicrosoftDataSqlClientPOC\POCVersionNET\Program.cs:line 112
ClientConnectionId:5432bbf2-3dac-498d-aad5-3a184f995dcc

[arellegue]

Thank you for providing the event source trace log. We will investigate and inform you of our findings.

[JRahnama]

for (int i = 0; i < 2000000; i++)
{
await sqlConnection.ExecuteSqlAsync(string.Format(QueryInsert, 1));
Console.WriteLine(i.ToString());
}

I think your issue is here. You are trying to open so many connections and after that performing the reader. That will cause the issue. Try extending your for loop till the end of SqlDataReader operation.

This does not seem like a SqlClient issue.

[David-Engel]

A severed connection is not a transient error that the driver can retry.

Keep in mind, connecting and executing are independent retriable things to the driver. If a connection is broken, a statement can't be retried against it. In-use connections have too many unknowns to the driver for safe, transparent reconnect and retry of statements when a connection is dropped mid-use. For example: Was there an open transaction (perhaps done in T-SQL in a procedure)? Were partial results received (how would the driver restart at a specific point)? Where some records updated but not others (again, stored procedures could have done things the driver isn't aware of)?

[avillemaire]

IT's possible I end up with the wrong exception which is why it won't retry. In my case i was trying to reproduce this error with Microsoft.data.sqlclient instead of the old system.data.sqlclient :

System.Data.SqlClient.SqlException
The service has encountered an error processing your request. Please try again.
Error code 40540. A severe error occurred on the current command.
The results, if any, should be discarded.
Class 20
Number 40197

System.Data.SqlClient.SqlException (0x80131904): A transport-level error has occurred when receiving results from the server.
(provider: Session Provider, error: 19 - Physical connection is not usable)

I went and checked in the documentation of Microsoft to see what would cause this issue and it seems this error happens when maintenance is done on the database and the connection is not available for a bit (normally it would take about 5 seconds to be available again, but at that time the exception has already been thrown). What I want to test and ensure with this Proof of concept is that the connection can be retried if ever a maintenance is happening while the connection is reading or writing.

I know this is not exactly the same error as what i showed earlier, but I was under the impression with the documentation on how to test transient errors with a database we could use PowerShell command "Invoke-AzSqlDatabaseFailover".

I'm open to any suggestion if you have an idea on how else I could try and simulate a maintenance of a database or an elastic pool for my POC.

[David-Engel]

It sounds like the built-in retry logic won't be sufficient for what you are trying to do. If database maintenance results in a broken connection during read/write operations, your application needs to decide what set of actions constitutes an atomic operation that can be safely retried. The built-in retry logic can only retry connection open for specific errors OR statement execution for specific errors. It can't retry both connection open and statement execution as part of one statement execution operation for specific errors.

[avillemaire]

Thank you for all your help.
I would just have a last few questions to ask:

1. Is it possible to get the list of errors and statement where the retry might work?
2. Is the list of transient error codes found here is correct and will be supported by the SQLRetryLogicProvider?: https://learn.microsoft.com/en-us/azure/azure-sql/database/troubleshoot-common-errors-issues?view=azuresql#list-of-transient-fault-error-codes

We have two scenarios in which we have most of our issues:

• We are in Azure and using SQL Elastic pool. During maintenance, we have a 3 secs downtime, and all our open connections are broken.
• We have a customer that is using SQL Server on Premise, and he has an error 4014. Based on the list in the above link it is a transient error and will the retry fix it?

We are going to go forward with the implementation, but we just want to be sure and properly document the use cases where the retry will work vs when the connection will be severed.

[David-Engel]

• We are in Azure and using SQL Elastic pool. During maintenance, we have a 3 secs downtime, and all our open connections are broken.

Depending on the state of each connection, some may be automatically restored by the driver. Idle connections (no open transactions or result sets, idle for over 30 seconds)) should be transparently restored as long as connectRetryCount > 0. Connections that aren't idle and are broken will have to be handled by the application.

• We have a customer that is using SQL Server on Premise, and he has an error 4014. Based on the list in the above link it is a transient error and will the retry fix it?

4014 isn't in the list of transient errors and won't trigger a retry, by default. I'm not familiar with that error, but since it's a fatal error, the connection will have to be closed and restored/retried by the application, as appropriate, when it happens.