c# - 如何设置SqlTransaction.Save()的超时时间？

我正进入（状态：

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)

如何增加SqlTransaction.Save()的超时时间？

最佳答案

现在，我真的希望我不要鼓励PSVSupporter删除他的答案（如果他删除它，请对他投票）。

在内部，当您发出任何事务请求时，该请求将基础连接的ConnectionTimeout属性用作查询的CommandTimeout。

您可以在源代码中遵循此过程，重要的部分在SqlInternalConnectionTds.ExecuteTransactionYukon和SqlInternalConnectionTds.ExecuteTransactionPreYukon中，它们都将ConnectionOptions.ConnectTimeout传入timeout参数传递给它们调用的函数。因此，要解决您的问题，请在创建连接时使用较大的ConnectionTimeout。

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
    }