我们尝试在事务范围内运行并行查询,以提高代码的性能。我们需要对数据库进行多个无关的更改。我们可以像这样运行代码:
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以避免此问题,但这会导致不同的错误:
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。
我不确定为什么会出现第二个错误,但我有一种感觉我走错了方向。在事务范围内执行并行查询是否可行?如果可以,应该采取哪种正确的方法?