How to set timeout for SqlTransaction.Save()?

Question

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()?

Sounds like you might have a deadlock in your SQL, I don't think doing a `SAVE TRANSACTION` in sql normally takes too long. — Scott Chamberlain, Mar 14 '16 at 16:49
@ScottChamberlain Depending on size of the database and number of transactions. — TN., Mar 14 '16 at 16:51
Possible duplicate of [SQL Server, C#: Timeout exception on Transaction Rollback](http://stackoverflow.com/questions/3977219/sql-server-c-timeout-exception-on-transaction-rollback) — Hamid Pourjam, Mar 14 '16 at 16:57
@dotctor It's hard to test, as it occurs seldom. The referred question speaks about rollback and begin transaction. — TN., Mar 14 '16 at 17:01
@TN. I have been googling and also have been noticing a trend talking about the [ConnectionTimeout on the connection object being used as the commmit timeout on a `SqlTransaction.Commit()`](https://social.msdn.microsoft.com/Forums/en-US/26d54516-74b7-4a54-b879-85536e4b605f/where-to-set-the-timeout-for-a-sqltransactioncommit?forum=adodotnetdataproviders) however no mention of `.Save()` and the timeout. It might be worth a try. (Now I wish I did not encourage PSVSupporter to delete his answer) — Scott Chamberlain, Mar 14 '16 at 17:14
@ScottChamberlain I have found this thread before, then I decided to create this question, to verify this behavior. — TN., Mar 15 '16 at 11:09

Scott Chamberlain · Accepted Answer · 2016-03-14T17:43:06.540

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
    }

I understand, but PSVSupporter was not providing a prove of his answer. Since the timeout occurs really seldom, it's hard to check whether their answer is correct (maybe just accidentally true) or wrong. Thank you for the investigation. — TN., Mar 15 '16 at 11:12

How to set timeout for SqlTransaction.Save()?

1 Answers1