TransactionScope() 和并行查询执行

我们尝试在事务范围内运行并行查询以提高代码的性能。我们要在数据库中进行几项彼此没有连接的更改。我们可以这样运行代码：

using(var tran = new System.Transactions.TransactionScope())
{
    await queryMethod1Async();
    await queryMethod2Async();
    await queryMethod3Async();

    tran.Complete();
}

然而，由于这些方法是相互独立的，我们希望像这样运行代码：

using(var tran = new System.Transactions.TransactionScope())
{
    var tasks = new List<Task>();

    tasks.Add(queryMethod1Async());
    tasks.Add(queryMethod2Async());
    tasks.Add(queryMethod3Async());

    await Task.WhenAll(tasks);

    tran.Complete();
}

我们在并行执行方面遇到了一些问题：

• 并行运行查询似乎会升级事务。在升级过程中，有时会出现错误：

The wait operation timed out  --> There is already an open DataReader associated with this Command which must be closed first.    
at System.Data.SqlClient.SqlInternalConnectionTds.ExecuteTransactionYukon(TransactionRequest transactionRequest, String transactionName, IsolationLevel iso, SqlInternalTransaction internalTransaction, Boolean isDelegateControlRequest)     
at System.Data.SqlClient.SqlDelegatedTransaction.Promote() --> Failure while attempting to promote transaction.    
at System.Data.SqlClient.SqlDelegatedTransaction.Promote()     
at System.Transactions.TransactionStatePSPEOperation.PSPEPromote(InternalTransaction tx)     
at System.Transactions.TransactionStateDelegatedBase.EnterState(InternalTransaction tx) --> The transaction has aborted.    
at System.Transactions.TransactionStateAborted.CheckForFinishedTransaction(InternalTransaction tx)     
at System.Transactions.Transaction.Promote()     
at System.Transactions.TransactionInterop.ConvertToOletxTransaction(Transaction transaction)    
at System.Transactions.TransactionInterop.GetExportCookie(Transaction transaction, Byte[] whereabouts)     
at System.Data.SqlClient.SqlInternalConnection.GetTransactionCookie(Transaction transaction, Byte[] whereAbouts)     
at System.Data.SqlClient.SqlInternalConnection.EnlistNonNull(Transaction tx)     
at System.Data.SqlClient.SqlInternalConnection.Enlist(Transaction tx)     
at System.Data.ProviderBase.DbConnectionInternal.ActivateConnection(Transaction transaction)   

经过一番调查，这似乎是因为在升级期间，使用了事务的原始连接，但在并行执行期间，该连接可能正在使用中。 我尝试启用MARS https://learn.microsoft.com/en-us/sql/relational-databases/native-client/features/using-multiple-active-result-sets-mars?view=sql-server-ver15以避免此问题，但这会导致不同的错误：

Current Microsoft Distributed Transaction Coordinator (MS DTC) transaction must be committed by remote client.    
at System.Data.SqlClient.SqlInternalConnection.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.SqlDelegatedTransaction.SinglePhaseCommit(SinglePhaseEnlistment enlistment) --> The transaction has aborted.    

为了澄清，上述每个方法都会创建一个新的sqlconnection，并且我们已经正确配置了MSDTC

我不确定为什么会发生第二个错误，但我有一种感觉，我正在以错误的方式处理这个问题。是否可以在事务范围内进行并行查询执行，如果可以，正确的方法是什么？

是否可以在事务范围内进行并行查询执行，如果可以，正确的方法是什么？

是的。您必须为每个查询创建一个单独的 SqlConnection，并正确配置和运行 MSDTC。但这样做很少有用，因为每个命令都可以在 SQL Server 上并行运行，因此同时运行多个命令通常不会减少总体运行时间。