CODEWITHSUNDEEP
c#sql.net
TN.
TN.

Reputation: 19820

How to set timeout for SqlTransaction.Save()?

I am getting:

System.Data.SqlClient.SqlException (0x80131904): Timeout expired.  The timeout period elapsed prior to completion of the operation or the server is not responding. ---> System.ComponentModel.Win32Exception (0x80004005): The wait operation timed out
at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
   at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose)
   at System.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady)
   at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj)
   at System.Data.SqlClient.TdsParser.TdsExecuteTransactionManagerRequest(Byte[] buffer, TransactionManagerRequestType request, String transactionName, TransactionManagerIsolationLevel isoLevel, Int32 timeout, SqlInternalTransaction transaction, TdsParserStateObject stateObj, Boolean isDelegateControlRequest)
   at System.Data.SqlClient.SqlInternalConnectionTds.ExecuteTransactionYukon(TransactionRequest transactionRequest, String transactionName, IsolationLevel iso, SqlInternalTransaction internalTransaction, Boolean isDelegateControlRequest)
   at System.Data.SqlClient.SqlInternalTransaction.Save(String savePointName)
   at System.Data.SqlClient.SqlTransaction.Save(String savePointName)

How to increase timeout of SqlTransaction.Save()?

Upvotes: 3

Views: 2994

Answers (1)

Scott Chamberlain
Scott Chamberlain

Reputation: 127573

Now I really wished I had not encouraged PSVSupporter to delete his answer (if he undeletes it, please vote on him).

Internally when you make any transaction request the request uses the ConnectionTimeout property of the underlying connection as the CommandTimeout of the query.

You can follow this process in the source, the important part is in SqlInternalConnectionTds.ExecuteTransactionYukon and SqlInternalConnectionTds.ExecuteTransactionPreYukon both pass ConnectionOptions.ConnectTimeout in to the timeout parameter to the functions that they call. So to solve your issue, use a larger ConnectionTimeout when creating the connection.

SqlTransaction

    public void Save(string savePointName) {
        //SNIP
                _internalTransaction.Save(savePointName);
        //SNIP
    }

SqlInternalTransaction

   internal void Save(string savePointName) {
        //SNIP
             _innerConnection.ExecuteTransaction(SqlInternalConnection.TransactionRequest.Save, savePointName, IsolationLevel.Unspecified, null, false);
        //SNIP
    }

SqlInternalConnectionTds

    override internal void ExecuteTransaction(TransactionRequest transactionRequest, string name, IsolationLevel iso, SqlInternalTransaction internalTransaction, bool isDelegateControlRequest) {
        //SNIP

        if (!_parser.IsYukonOrNewer) {
            ExecuteTransactionPreYukon(transactionRequest, transactionName, iso, internalTransaction);
        }
        else {
            ExecuteTransactionYukon(transactionRequest, transactionName, iso, internalTransaction, isDelegateControlRequest);
        }
    }

    internal void ExecuteTransactionPreYukon(
                TransactionRequest      transactionRequest, 
                string                  transactionName, 
                IsolationLevel          iso, 
                SqlInternalTransaction  internalTransaction) {
        //SNIP

        Threading.Tasks.Task executeTask = _parser.TdsExecuteSQLBatch(sqlBatch.ToString(), 
             ConnectionOptions.ConnectTimeout, null, _parser._physicalStateObj, sync: true);

        //SNIP
    }

    internal void ExecuteTransactionYukon(
                TransactionRequest      transactionRequest, 
                string                  transactionName, 
                IsolationLevel          iso, 
                SqlInternalTransaction  internalTransaction, 
                bool                    isDelegateControlRequest) {
            //SNIP

            _parser.TdsExecuteTransactionManagerRequest(null, requestType, transactionName, isoLevel,
                ConnectionOptions.ConnectTimeout, internalTransaction, stateObj, isDelegateControlRequest);

            //SNIP
    }

Upvotes: 4

Related Questions