TransactionScope 在某些机器上自动升级到 MSDTC? [英] TransactionScope automatically escalating to MSDTC on some machines?

问题描述

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

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 not require the MSDTC service to be enabled on our end-user's machines.

问题是，在我们一半的开发人员机器上，我们可以在禁用 MSDTC 的情况下运行.另一半必须启用它，否则他们会收到 [SERVER] 上的 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.

这真的让我摸不着头脑，让我认真考虑回滚到基于 ADO.NET 事务对象的类似 TransactionScope 的家庭解决方案.这看起来很疯狂 - 相同的代码在我们开发者的一半确实上运行(并且不会升级)在其他开发者的上运行.

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 does escalate on the other developer's.

我希望有一个更好的答案来跟踪事务为何升级为 DTC，但不幸的是它没有.

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

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

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.)

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:

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

它不适用于开发者:

• 开发 4:Windows 7 x64，SQL2008 SQL2005
• 开发 5:Windows Vista x86、SQL2005
• 开发 6:Windows XP X86、SQL2005
• 我的家用电脑:Windows Vista 家庭高级版、x86、SQL2005

我应该补充一点，为了查明问题，所有机器都已完全修补了 Microsoft Update 中提供的所有内容.

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.

• 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.aspx 关于事务升级的 MSDN.

• 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.aspx MSDN on transaction escalation.

那个 MSDN 事务升级页面指出以下情况将导致事务升级为 DTC:

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

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

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

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?

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

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.

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

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!

只是为了澄清问题:

SQL2008:

• 允许在单个 TransactionScope 内进行多个连接(如上面的示例代码所示.)
• 警告#1:如果嵌套多个SqlConnection，即同时打开两个或多个SqlConnection，TransactionScope 将立即升级为DTC.
• 警告 #2:如果一个额外的 SqlConnection 被打开到不同的持久资源"(即:不同的 SQL Server，)，它将立即升级为 DTC

• 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

SQL2005:

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

为了使这个问题更加一团糟有用，并且只是为了更清楚起见，以下是您如何使用单个让 SQL2005 升级到 DTC> SqlConnection:

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

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

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

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

为什么会发生这种情况呢?"好吧，如果您在连接打开之前使用 SqlTableAdapter，SqlTableAdapter 将打开和关闭连接，有效地为您完成事务，因为您现在无法重新打开它.

"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.

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

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.

推荐答案

SQL Server 2008 可以在一个 TransactionScope 中使用多个 SQLConnection 而无需升级，前提是连接不是同时打开，这将导致多个物理"TCP 连接，因此需要升级.

SQL Server 2008 can use multiple SQLConnections in one TransactionScope without 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 2005，其他开发人员使用 SQL Server 2008.您确定您确定正确识别了哪些正在升级，哪些没有?

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 2008 的开发人员没有升级.

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

这篇关于TransactionScope 在某些机器上自动升级到 MSDTC?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！