[BUG] Sporadic timeouts in SqlServerChangeTrackingSource

[s-vitaliy]

Description

Sometimes SqlServerChangeTrackingSource fails with timeout on transaction commit when data extracted from SQL server and emited.
See stack trace below:

Microsoft.Data.SqlClient.SqlException (0x80131904): Execution Timeout Expired.  The timeout period elapsed prior to completion of the operation or the server is not responding.
 ---> System.ComponentModel.Win32Exception (258): Unknown error 258
   at Microsoft.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
   at Microsoft.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, SqlCommand command, Boolean callerHasConnectionLock, Boolean asyncClose)
   at Microsoft.Data.SqlClient.TdsParserStateObject.ThrowExceptionAndWarning(Boolean callerHasConnectionLock, Boolean asyncClose)
   at Microsoft.Data.SqlClient.TdsParserStateObject.ReadSniError(TdsParserStateObject stateObj, UInt32 error)
   at Microsoft.Data.SqlClient.TdsParserStateObject.ReadSniSyncOverAsync()
   at Microsoft.Data.SqlClient.TdsParserStateObject.TryPrepareBuffer()
   at Microsoft.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady)
   at Microsoft.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj)
   at Microsoft.Data.SqlClient.TdsParser.TdsExecuteTransactionManagerRequest(Byte[] buffer, TransactionManagerRequestType request, String transactionName, TransactionManagerIsolationLevel isoLevel, Int32 timeout, SqlInternalTransaction transaction, TdsParserStateObject stateObj, Boolean isDelegateControlRequest)
   at Microsoft.Data.SqlClient.SqlInternalConnectionTds.ExecuteTransaction2005(TransactionRequest transactionRequest, String transactionName, IsolationLevel iso, SqlInternalTransaction internalTransaction, Boolean isDelegateControlRequest)
   at Microsoft.Data.SqlClient.SqlInternalTransaction.Commit()
   at Microsoft.Data.SqlClient.SqlTransaction.Commit()
   at Arcane.Framework.Sources.SqlServer.SqlServerChangeTrackingSource.SourceLogic.OnRecordReceived(Task`1 readTask)
   at Akka.Streams.Stage.GraphStageLogic.<>c__DisplayClass94_0`1.<GetAsyncCallback>b__1(Object x)
   at Akka.Streams.Implementation.Fusing.GraphInterpreter.RunAsyncInput(GraphStageLogic logic, Object evt, Action`1 handler)
--- End of stack trace from previous location ---
   at Arcane.Framework.Providers.Hosting.HostBuilderExtensions.RunStream[TContext](IHost host, ILogger logger, Func`3 handleUnknownException)
ClientConnectionId:418abcab-86e2-48e9-a229-48541d5ca28b
Error Number:-2,State:0,Class:11

Steps to reproduce the issue

1. Start a streaming job targeting a MS SQL Server table.
2. Wait couple of days
3. Pod running a stream runner fails

Describe the results you expected

Stable SQL Server streams.

System information

all versions of Arcane.Framework

[s-vitaliy]

• Connection Timout
• Dispose SqlCommand: Execution Timeout Expired Error (258, ReadSniSyncOverAsync) dotnet/SqlClient#647 (comment)
• Increase number of threads: Execution Timeout Expired Error (258, ReadSniSyncOverAsync) dotnet/SqlClient#647 (comment)
• Set packet size to 512: Execution Timeout Expired Error (258, ReadSniSyncOverAsync) dotnet/SqlClient#647 (comment)