为何涉及MSDTC？

Question

据我所知，MSDTC只在下列情况下才参与：

• 在链接到另一台服务器的事务中查询视图/表。
• 在单个SqlConnections中使用两个TransactionScope (或NHibernate使用的任何东西)
• 您正在TransactionScope中登记另一个事务性组件(如MSMQ或事务性文件系统)。
• 未提及的其他情况。

如果禁用MSDTC并运行以下代码，则会得到一个错误(服务器上的MSDTC不可用)。

public bool Add(PurchaseOrderInfo purchaseOrderInfo)
{
    using (TransactionScope ts = new TransactionScope())
    {
        using (SqlConnection Cnn = new SqlConnection(SqlHelper.ConnStr))
        {
            Cnn.Open();

                try
                {
                    using (SqlDataReader rdr = SqlHelper.ExecuteReader(/*Tr*/Cnn, "spPurchaseOrderAdd", purchaseOrderInfo.ExpectedShipment.ShipmentID, purchaseOrderInfo.CreateDate, purchaseOrderInfo.CustomerNotes, purchaseOrderInfo.Status, purchaseOrderInfo.PurchaseOrderNumber))
                    {
                        if (rdr.Read())
                            FillPurchaseOrderInfo(rdr, ref purchaseOrderInfo, GettingDepthEnum.Level_0);
                        else
                        {
                            return false;
                        }
                    }

                    foreach (PurchaseOrderDetailInfo detailInfo in purchaseOrderInfo.Details)
                    {
                        throw new Exception("Test");
                        //if (!AddPurchaseOrderDetail(Tr, purchaseOrderInfo, detailInfo))
                        {
                            //Tr.Rollback();
                            return false;
                        }
                    }

                    return true;
                }
                catch (Exception ex)
                {
                    throw ex;
                }
                ts.Complete();
            }
        }
    }

我有遗漏什么吗？

更新：存储过程包含一个简单的insert语句：

INSERT INTO tblPurchaseOrder
(ShipmentID, CreateDate, CustomerNotes, PurchaseOrderState, PurchaseOrderNumber, LastActivityDate)
VALUES
(@ShipmentID, @CreateDate, @CustomerNotes, @PurchaseOrderState, @PurchaseOrderNumber, GETDATE());

--Step 2: return row that INSERTED to Client Computer.
SELECT dbo.viwGetPurchaseOrderWeight.Weight,* FROM tblPurchaseOrder LEFT OUTER JOIN viwGetPurchaseOrderWeight ON viwGetPurchaseOrderWeight.PurchaseOrderID =  tblPurchaseOrder.PurchaseOrderID WHERE (tblPurchaseOrder.PurchaseOrderID = Scope_Identity());

UPDATE2:当关闭MSDTC时，当执行到达该行时抛出异常：

using (SqlDataReader rdr = SqlHelper.ExecuteReader(/*Tr*/Cnn, "spPurchaseOrderAdd", purchaseOrderInfo.ExpectedShipment.ShipmentID, purchaseOrderInfo.CreateDate, purchaseOrderInfo.CustomerNotes, purchaseOrderInfo.Status, purchaseOrderInfo.PurchaseOrderNumber))

这意味着后续行没有任何影响。

Answer 1

确保不要再打开连接，

using (TransactionScope transactionScope = new TransactionScope()) {
    using (SqlConnection connection = new SqlConnection(connectionString)) {
    connection.Open();
    connection.Close();
    connection.Open(); // escalates to DTC
      }
}

Answer 2

根据您的文章和堆栈跟踪，EntLib类正在打开另一个连接。我们需要把它排除在交易之外。将对ExecuteReader的调用包装在抑制事务范围内：

SqlDataReader rdr;
using (var tsSuppress = new TransactionScope(TransactionScopeOption.Suppress))
    rdr = SqlHelper.ExecuteReader(...)

这将暂时将Transaction.Current设置为null，以便新连接不会接收事务。

顺便提一句，堆栈跟踪允许我们找到问题的根源。

Answer 3

在检查堆栈跟踪之后，SQLHelper类似乎打开了一个连接，以便从数据库中获取存储过程所需的参数列表：

   at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection)
   at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection)
   at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj)
   at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj)
   at System.Data.SqlClient.SqlDataReader.ConsumeMetaData()
   at System.Data.SqlClient.SqlDataReader.get_MetaData()
   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.TdsParser.GetDTCAddress(Int32 timeout, TdsParserStateObject stateObj)
   at System.Data.SqlClient.SqlInternalConnectionTds.GetDTCAddress()
   at System.Data.SqlClient.SqlInternalConnection.EnlistNonNull(Transaction tx)
   at System.Data.SqlClient.SqlInternalConnection.Enlist(Transaction tx)
   at System.Data.SqlClient.SqlInternalConnectionTds.Activate(Transaction transaction)
   at System.Data.ProviderBase.DbConnectionInternal.ActivateConnection(Transaction transaction)
   at System.Data.ProviderBase.DbConnectionPool.GetConnection(DbConnection owningObject)
   at System.Data.ProviderBase.DbConnectionFactory.GetConnection(DbConnection owningConnection)
   at System.Data.ProviderBase.DbConnectionClosed.OpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory)
   at System.Data.SqlClient.SqlConnection.Open()
   at Microsoft.ApplicationBlocks.Data.SqlHelperParameterCache.DiscoverSpParameterSet(SqlConnection connection, String spName, Boolean includeReturnValueParameter)
   at Microsoft.ApplicationBlocks.Data.SqlHelperParameterCache.GetSpParameterSetInternal(SqlConnection connection, String spName, Boolean includeReturnValueParameter)
   at Microsoft.ApplicationBlocks.Data.SqlHelperParameterCache.GetSpParameterSet(SqlConnection connection, String spName, Boolean includeReturnValueParameter)
   at Microsoft.ApplicationBlocks.Data.SqlHelperParameterCache.GetSpParameterSet(SqlConnection connection, String spName)
   at Microsoft.ApplicationBlocks.Data.SqlHelper.ExecuteReader(SqlConnection connection, String spName, Object[] parameterValues)
   at AlefTextileProduction.SQLServerDAL.PurchaseOrder.Add(PurchaseOrderInfo purchaseOrderInfo) 

我想找个解决办法!！