C# TransactionScope 在某些机器上自动升级到 MSDTC？

TransactionScope automatically escalating to MSDTC on some machines?

提问by Yoopergeek

In our project we're using TransactionScope's to ensure our data access layer performs it's actions in a transaction. We're aiming to notrequire the MSDTC service to be enabled on our end-user's machines.

在我们的项目中，我们使用 TransactionScope 来确保我们的数据访问层在事务中执行它的操作。我们的目标是不要求在最终用户的计算机上启用 MSDTC 服务。

Trouble is, on half of our developers machines, we can run with MSDTC disabled. The other half must have it enabled or they get the "MSDTC on [SERVER] is unavailable"error message.

问题是，在我们一半的开发人员机器上，我们可以在禁用 MSDTC 的情况下运行。另一半必须启用它，否则他们会收到“[SERVER] 上的MSDTC 不可用”错误消息。

It's really got me scratching my head and has me seriously considering rolling back to a home-spun TransactionScope-like solution based on ADO.NET transaction objects. It's seemingly insane - the same code that works (and does not escalate) on half of our developer's doesescalate on the other developer's.

这真的让我摸不着头脑，让我认真考虑回滚到基于 ADO.NET 事务对象的类似 TransactionScope 的家庭解决方案。这看起来很疯狂——同样的代码在我们一半的开发人员身上工作（并且不会升级），但在其他开发人员的身上也会升级。

I was hoping for a better answer to Trace why a transaction is escalated to DTCbut unfortunately it doesn't.

我希望有一个更好的答案来跟踪为什么交易被升级到 DTC但不幸的是它没有。

Here's a sample bit of code that will cause the trouble, on the machines that try to escalate, it tries to escalate on the second connection.Open() (and yes, there is no other connection open at the time.)

这是一个会导致问题的示例代码，在尝试升级的机器上，它尝试在第二个 connection.Open() 上升级（是的，当时没有其他连接打开。）

using (TransactionScope transactionScope = new TransactionScope() {
   using (SqlConnection connection = new SqlConnection(_ConStr)) {
      using (SqlCommand command = connection.CreateCommand()) {
         // prep the command
         connection.Open();
         using (SqlDataReader reader = command.ExecuteReader()) {
            // use the reader
            connection.Close();
         }
      }
   }

   // Do other stuff here that may or may not involve enlisting 
   // in the ambient transaction

   using (SqlConnection connection = new SqlConnection(_ConStr)) {
      using (SqlCommand command = connection.CreateCommand()) {
         // prep the command
         connection.Open();  // Throws "MSDTC on [SERVER] is unavailable" on some...

         // gets here on only half of the developer machines.
      }
      connection.Close();
   }

   transactionScope.Complete();
}

We've really dug in and tried to figure this out. Here's some info on the machines that it works on:

我们真的深入挖掘并试图解决这个问题。以下是有关它工作的机器的一些信息：

• Dev 1: Windows 7 x64 SQL2008
• Dev 2: Windows 7 x86 SQL2008
• Dev 3: Windows 7 x64 SQL2005SQL2008

• 开发 1：Windows 7 x64 SQL2008
• 开发 2：Windows 7 x86 SQL2008
• 开发 3：Windows 7 x64 SQL2005 SQL2008

Developers it doesn't work on:

它不适用于的开发人员：

• Dev 4: Windows 7 x64, SQL2008SQL2005
• Dev 5: Windows Vista x86, SQL2005
• Dev 6: Windows XP X86, SQL2005
• My Home PC : Windows Vista Home Premium, x86, SQL2005

• 开发 4：Windows 7 x64、SQL2008 SQL2005
• 开发人员 5：Windows Vista x86、SQL2005
• 开发 6：Windows XP X86、SQL2005
• 我的家用电脑：Windows Vista Home Premium、x86、SQL2005

I should add that all machines, in an effort to hunt down the problem, have been fully patched with everything that's available from Microsoft Update.

我应该补充一点，为了追查问题，所有机器都已完全修补了 Microsoft 更新中可用的所有内容。

Update 1:

更新 1：

• http://social.msdn.microsoft.com/forums/en-US/windowstransactionsprogramming/thread/a5462509-8d6d-4828-aefa-a197456081d3/describes a similar problem...back in 2006!
• http://msdn.microsoft.com/en-us/library/system.transactions.transactionscope%28VS.80%29.aspx- read that code sample, it clearly demonstrates a nested-second connection (to a second SQL server, actually) which will escalate to DTC. We are not doing this in our code- we're not using different SQL servers, nor different connection strings, nor do we have nested secondary connections opening - there should not be escalation to DTC.
• http://davidhayden.com/blog/dave/archive/2005/12/09/2615.aspx(from 2005) talks about how escalation to DTC will always happen when connecting to SQL2000. We're using SQL2005/2008
• http://msdn.microsoft.com/en-us/library/ms229978.aspxMSDN on transaction escalation.

• http://social.msdn.microsoft.com/forums/en-US/windowstransactionsprogramming/thread/a5462509-8d6d-4828-aefa-a197456081d3/描述了一个类似的问题......早在 2006 年！
• http://msdn.microsoft.com/en-us/library/system.transactions.transactionscope%28VS.80%29.aspx- 阅读该代码示例，它清楚地展示了嵌套的第二个连接（到第二个 SQL 服务器，实际上）这将升级为 DTC。 我们没有在我们的代码中这样做- 我们没有使用不同的 SQL 服务器，也没有不同的连接字符串，也没有嵌套的辅助连接打开 -不应该升级到 DTC。
• http://davidhayden.com/blog/dave/archive/2005/12/09/2615.aspx（从 2005 年开始）讨论了在连接到 SQL2000 时如何始终升级到 DTC。我们使用的是 SQL2005/2008
• http://msdn.microsoft.com/en-us/library/ms229978.aspxMSDN 关于事务升级。

That MSDN transaction-escalation page states that the following conditions will cause a transaction to escalate to DTC:

该 MSDN 事务升级页面指出以下情况将导致事务升级为 DTC：

1. At least one durable resource that does not support single-phase notifications is enlisted in the transaction.
2. At least two durable resources that support single-phase notifications are enlisted in the transaction. For example, enlisting a single connection with does not cause a transaction to be promoted. However, whenever you open a second connection to a database causing the database to enlist, the System.Transactions infrastructure detects that it is the second durable resource in the transaction, and escalates it to an MSDTC transaction.
3. A request to "marshal" the transaction to a different application domain or different process is invoked. For example, the serialization of the transaction object across an application domain boundary. The transaction object is marshaled-by-value, meaning that any attempt to pass it across an application domain boundary (even in the same process) results in serialization of the transaction object. You can pass the transaction objects by making a call on a remote method that takes a Transaction as a parameter or you can try to access a remote transactional-serviced component. This serializes the transaction object and results in an escalation, as when a transaction is serialized across an application domain. It is being distributed and the local transaction manager is no longer adequate.

1. 至少一个不支持单阶段通知的持久资源被登记在事务中。
2. 事务中至少征用了两个支持单阶段通知的持久资源。例如，登记单个连接不会导致事务被提升。但是，每当您打开与数据库的第二个连接导致数据库登记时，System.Transactions 基础结构会检测到它是事务中的第二个持久资源，并将其升级为 MSDTC 事务。
3. 调用将事务“编组”到不同应用程序域或不同进程的请求。例如，跨应用程序域边界的事务对象的序列化。事务对象是按值编组的，这意味着任何跨应用程序域边界（即使在同一进程中）传递它的尝试都会导致事务对象的序列化。您可以通过调用将事务作为参数的远程方法来传递事务对象，也可以尝试访问远程事务服务组件。这会序列化事务对象并导致升级，就像跨应用程序域序列化事务一样。它正在分发，本地事务管理器不再适用。

We're not experiencing #3. #2 is not happening because there is only ever one connection at a time, and it's also to a single 'durable resource'. Is there any way that #1 could be happening? Some SQL2005/8 configuration that causes it to not support single-phase notifications?

我们没有遇到#3。#2 没有发生，因为一次只有一个连接，而且它也是一个“持久资源”。有没有办法让#1 发生？某些 SQL2005/8 配置导致它不支持单阶段通知？

Update 2:

更新 2：

Re-investigated, personally, everyone's SQL Server versions - "Dev 3" actually has SQL2008, and "Dev 4" is actually SQL2005. That'll teach me to never trust my coworkers again. ;) Because of this change in data, I'm pretty sure we've found our problem. Our SQL2008 developers weren't experiencing the problem because SQL2008 has copious amounts of awesome included that SQL2005 does not have.

个人重新调查了一下，大家的SQL Server版本——“Dev 3”其实是SQL2008，“Dev 4”其实是SQL2005。这将教会我永远不要再相信我的同事。;) 由于数据的这种变化，我很确定我们已经找到了我们的问题。我们的 SQL2008 开发人员没有遇到这个问题，因为 SQL2008 包含了大量 SQL2005 没有的功能。

It also tells me that because we're going to be supporting SQL2005 that we can't use TransactionScope like we have been, and if we want to use TransactionScope we're going to need to be passing a single SqlConnection object around...which seems problematic in situations where the SqlConnection can't easily be passed around...it just smells of global-SqlConnection instance. Pew!

它还告诉我，因为我们将支持 SQL2005，所以我们不能像以前那样使用 TransactionScope，如果我们想使用 TransactionScope，我们将需要传递一个 SqlConnection 对象......这在 SqlConnection 不能轻易传递的情况下似乎有问题......它只是全局 SqlConnection 实例的味道。座位！

Update 3

更新 3

Just to clarify up here in the question:

只是为了在问题中澄清：

SQL2008:

SQL2008：

• Allows multiple connections within a single TransactionScope (as demonstrated in the above sample code.)
• Caveat #1: If those multiple SqlConnections are nested, that is, two or more SqlConnections are opened at the same time, TransactionScope will immediately escalate to DTC.
• Caveat #2: If an additional SqlConnection is opened to a different 'durable resource'(ie: a different SQL Server,) it will immediately escalate to DTC

• 允许单个 TransactionScope 内的多个连接（如上面的示例代码所示。）
• 警告 #1：如果那些多个 SqlConnection 是嵌套的，即同时打开两个或多个 SqlConnection，TransactionScope 将立即升级为 DTC。
• 警告 #2：如果一个额外的 SqlConnection 被打开到不同的“持久资源”（即：不同的 SQL Server），它将立即升级为 DTC

SQL2005:

SQL2005：

• Does not allow multiple connections within a single TransactionScope, period. It will escalate when/if a second SqlConnection is opened.

• 不允许在单个 TransactionScope 期间内有多个连接。当/如果打开第二个 SqlConnection 时，它会升级。

Update 4

更新 4

In the interest of making this question even more of a messuseful, and just for more clarity's sake, here's how you can get SQL2005 to escalate to DTC with a singleSqlConnection:

在做出这个问题更加的利益乱七八糟的有用的，只是为了更清晰起见，这里是你如何能得到SQL2005升级到DTC带有单SqlConnection：

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

This just seems broken to me, but I guess I can understand if every call to SqlConnection.Open()is grabbing from the connection pool.

这对我来说似乎已经坏了，但我想我能理解是否每个调用SqlConnection.Open()都是从连接池中获取的。

"Why might this happen, though?"Well, if you use a SqlTableAdapter against that connection before it's opened, the SqlTableAdapter will open and close the connection, effectively finishing the transaction for you because you now can't re-open it.

“可是，为什么会这样呢？” 好吧，如果您在该连接打开之前对其使用 SqlTableAdapter，SqlTableAdapter 将打开和关闭连接，有效地为您完成事务，因为您现在无法重新打开它。

So, basically, in order to successfully use TransactionScope with SQL2005 you need to have some sort of global connection object that remains open from the point of the first TransactionScope is instantiated until it's no longer needed. Besides the code-smell of a global connection object, opening the connection first and closing it last is at odds against the logic of opening a connection as late as possible and closing it as soon as possible.

因此，基本上，为了成功地将 TransactionScope 与 SQL2005 一起使用，您需要有某种全局连接对象，该对象从第一个 TransactionScope 实例化开始一直保持打开状态，直到不再需要它为止。除了全局连接对象的代码味道之外，首先打开连接并最后关闭连接与尽可能晚地打开连接并尽快关闭连接的逻辑不一致。

采纳答案by Joe

SQL Server 2008 can use multiple SQLConnections in one TransactionScopewithout escalating, provided the connections are not open at the same time, which would result in multiple "physical" TCP connections and thus require escalation.

SQL Server 2008 可以SQLConnection在TransactionScope不升级的情况下使用多个s ，前提是连接不是同时打开的，这会导致多个“物理”TCP 连接，因此需要升级。

I see some of your developers have SQL Server 2005 and others have SQL Server 2008. Are you sure you have correctly identified which ones are escalating and which not?

我看到您的一些开发人员使用 SQL Server 2005，而其他开发人员使用 SQL Server 2008。您确定正确识别了哪些正在升级，哪些没有？

The most obvious explanation would be that developers with SQL Server 2008 are the ones that aren't escalating.

最明显的解释是使用 SQL Server 2008 的开发人员没有升级。

回答by amateur

TransactionScope always escalates to DTC transaction, if you use access more than 1 connection inside. The only way the code above can work with DTC disabled is if by a huge chance you get the same connection from the connection pool both times.

TransactionScope 总是升级为 DTC 事务，如果您在内部使用访问超过 1 个连接。上面的代码可以在禁用 DTC 的情况下工作的唯一方法是，如果您很有可能两次从连接池中获得相同的连接。

"Trouble is, on half of our developers machines, we can run with MSDTC disabled." Are you sure sure it's disabled ;)

“问题是，在我们一半的开发人员机器上，我们可以在禁用 MSDTC 的情况下运行。” 你确定它被禁用了吗;)

回答by hwiechers

That code willcause an escalation when connecting to 2005.

当连接到 2005 时，该代码将导致升级。

Check the documentation on MSDN - http://msdn.microsoft.com/en-us/library/ms172070.aspx

检查 MSDN 上的文档 - http://msdn.microsoft.com/en-us/library/ms172070.aspx

Promotable Transactions in SQL Server 2008

In version 2.0 of the .NET Framework and SQL Server 2005, opening a second connection inside a TransactionScope would automatically promote the transaction to a full distributed transaction, even if both connections were using identical connection strings. In this case, a distributed transaction adds unnecessary overhead that decreases performance.

Starting with SQL Server 2008 and version 3.5 of the .NET Framework, local transactions are no longer promoted to distributed transactions if another connection is opened in the transaction after the previous transaction is closed. This requires no changes to your code if you are already using connection pooling and enlisting in transactions.

SQL Server 2008 中的可提升事务

在 .NET Framework 和 SQL Server 2005 的 2.0 版中，在 TransactionScope 内打开第二个连接会自动将事务提升为完全分布式事务，即使两个连接使用相同的连接字符串也是如此。在这种情况下，分布式事务会增加不必要的开销，从而降低性能。

从 SQL Server 2008 和 .NET Framework 3.5 版开始，如果在前一个事务关闭后在事务中打开另一个连接，本地事务不再提升为分布式事务。如果您已经在使用连接池并在事务中登记，则无需更改您的代码。

I can't explain why Dev 3: Windows 7 x64, SQL2005 succeeds and Dev 4: Windows 7 x64 fails. Are you sure that is not the other way round?

我无法解释为什么 Dev 3: Windows 7 x64, SQL2005 成功而 Dev 4: Windows 7 x64 失败。你确定这不是相反的吗？

回答by Peter Meinl

The result of my research on the topic:

我对该主题的研究结果：

See Avoid unwanted Escalation to Distributed Transactions

请参见避免不必要的升级到分布式事务

I am still investigating Oracle's escalation behavior: Do transactions spanning multiple connections to same DB escalate to DTC?

我仍在调查 Oracle 的升级行为： 跨越多个连接到同一数据库的事务是否升级为 DTC？

回答by Iftikhar Ali

I'm not too sure if nested connection is the issue. I'm calling a local instance of SQL server and it doesn't generate the DTC??

我不太确定嵌套连接是否是问题所在。我正在调用 SQL 服务器的本地实例，但它不生成 DTC？？

    public void DoWork2()
    {
        using (TransactionScope ts2 = new TransactionScope())
        {
            using (SqlConnection conn1 = new SqlConnection("Data Source=Iftikhar-PC;Initial Catalog=LogDB;Integrated Security=SSPI;"))
            {
                SqlCommand cmd = new SqlCommand("Insert into Log values(newid(),'" + "Dowork2()" + "','Info',getDate())");
                cmd.Connection = conn1;
                cmd.Connection.Open();
                cmd.ExecuteNonQuery();

                using (SqlConnection conn2 = new SqlConnection("Data Source=Iftikhar-PC;Initial Catalog=LogDB;Integrated Security=SSPI;Connection Timeout=100"))
                {
                    cmd = new SqlCommand("Insert into Log values(newid(),'" + "Dowork2()" + "','Info',getDate())");
                    cmd.Connection = conn2;
                    cmd.Connection.Open();
                    cmd.ExecuteNonQuery();
                }
            }

            ts2.Complete();
        }
    }

回答by Chris Marisic

I don't know why this answer was deleted but this seems to have some relevant information.

我不知道为什么这个答案被删除了，但这似乎有一些相关信息。

answered Aug 4 '10 at 17:42 Eduardo

回答 2010 年 8 月 4 日 17:42爱德华多

1. Set Enlist=falseon connection string to avoid auto enlistment on transaction.

2. Manually enlist connection as participantsin transaction scope. [original articleoutdated] or do this: How to prevent automatic MSDTC promotion [archive.is]

1. 在连接字符串上设置Enlist=false以避免自动登记事务。

2. 手动将连接登记为事务范围内的参与者。[原创文章过时]或这样做：如何防止自动MSDTC提升[archive.is]

回答by FreddyV

Make sure your connectionString does not set pooling to false. This will result in a new connection for each new SqlConnection in the TransactionScope and escalates it to DTC.

确保您的 connectionString 没有将 pooling 设置为 false。这将为 TransactionScope 中的每个新 SqlConnection 生成一个新连接，并将其升级为 DTC。