SqlConnection和避免推广MSDTC [英] SqlConnection and avoiding promotion to MSDTC

问题描述

当我们需要做的数据库访问的应用程序，我们使用以下方式：

When we need to do database access in our application, we use the following patterns:

• 有关查询，我们有一个静态工厂类的方法 CreateOpenConnection 它什么也不做超过新的SqlConnection（myConnectionString）并要求打开（）就可以了。调用此方法之前，我们做了查询和连接设置在查询返回后。
• 对于插入/更新/删除，我们使用的是更改是成批起来，并调用提交到数据库中，以 work.Commit（）像工作模式的单位因此：

• For querying, we have a static factory class with a method CreateOpenConnection which does nothing more than new SqlConnection(myConnectionString) and calls Open() on it. This method gets called before we do a query and the connection is disposed after the query returns.
• For inserts/updates/deletes we use a Unit of Work pattern where the changes are batched up and submitted to the database with a call to work.Commit() like so:

work.Commit：

work.Commit:

using (var tranScope = new TransactionScope(TransactionScopeOption.RequiresNew))
{
    using (var conn = DapperFactory.CreateOpenConnection())
    {
      var count = _changeTracker.CommitChanges(conn);

      tranScope.Complete();

      return count;
    }
}

这似乎工作伟大的普遍使用，因为一个Web服务的组成部分，但目前给我的MSDTC麻烦，当我尝试使用这种结合Rebus的。

This seems to work great for general usage as part of a webservice, but is currently giving me MSDTC trouble when I try to use this in combination with Rebus.

据我所知，Rebus的（当它处理一个消息队列）创建了一个新的的TransactionScope ，以便在发生故障时处理该消息，东东可以回滚。现在，这本身已能正常工作为止。我可以打开一个新的的SqlConnection 没有任何问题（然而，用我们传统的实体框架查询时Rebus的消息处理函数中的和的手动相同的Rebus内SqlConnections 的TransactionScope 不工作，但我不认为一个问题现在）。不过，昨天我提出以下问题：

From what I can tell, Rebus (when it handles a message in the queue) creates a new TransactionScope so that in case of a failure to handle the message, stuff can be rolled back. Now, this in itself has worked fine so far. I can open a new SqlConnection inside a Rebus message handler without any issues (however, using our legacy Entity Framework queries and manual SqlConnections inside the same Rebus TransactionScope doesn't work, but I don't consider that an issue right now). But yesterday I asked the following question:

<一个href="http://stackoverflow.com/questions/16837668/serial-processing-of-a-certain-message-type-in-rebus/16843938#16843938">Serial在Rebus的某消息类型的处理

这个问题的答案似乎是要使用的Rebus的传奇特性。我尝试实施这一并配置它，这样的Rebus的传奇被保存到一个新的SQL Server数据库（具有鲜明的连接字符串）。 presumably，使用SQL Server的持久性打开了一个的SqlConnection 它自己的，因为任何时候我尝试创建一个的SqlConnection 现在，我得到了以下异常：

To which the answer seems to be to use the saga feature of Rebus. I tried implementing this and configured it so that the Rebus saga gets persisted to a new SQL Server database (with a distinct connection string). Presumably, using that SQL Server persistence opens a SqlConnection of its own, because any time I try to create a SqlConnection now, I get the following exception:

有关分布式事务管理器（MSDTC）的网络访问已被禁用。请启用DTC中使用的组件服务管理工具MSDTC安全配置的网络访问。

Network access for Distributed Transaction Manager (MSDTC) has been disabled. Please enable DTC for network access in the security configuration for MSDTC using the Component Services Administrative tool.

启用MSDTC是我会非常的非常的多少希望避免这样做，关于配置和性能开销。我可能是错的，但它也只是似乎没有必要。

Enabling MSDTC is something I would very, very much like to avoid doing, with regards to configuration and performance overhead. And I may be wrong, but it also just doesn't seem necessary.

我的presume这里发生的是，Rebus的创建环境的TransactionScope 和该的SqlConnection 它创建征集到的范围。当我尝试创建自己的的SqlConnection 也试图争取到周围的范围和因为多个连接都参与其中，它被提升到MSDTC，这将失败。

What I presume is happening here is that Rebus creates an ambient TransactionScope and that the SqlConnection it creates enlists to that scope. And when I try to create my own SqlConnection it also tries to enlist to that ambient scope and because multiple connections are involved, it gets promoted to MSDTC, which fails.

我对如何解决此问题的想法，但我不知道这是否是正确的事情。我会做的是：

I have an idea on how to fix this, but I don't know if it's the right thing to do. What I would do is:

• 添加招募=假来我的应用程序的连接字符串，使其永远不会征到环境事务。
• 修改提交方法，以便它不创建一个新的的TransactionScope （我的连接不会其中订阅了，因为我只是告诉它不应该），但它使用 conn.BeginTransaction 。

• Add Enlist=false to my application's connection string so that it never enlists to ambient transactions.
• Modify the Commit method so that it doesn't create a new TransactionScope (which my connection won't subscribe to any more because I just told that it shouldn't) but that it uses conn.BeginTransaction.

像这样：

var transaction = conn.BeginTransaction();

try
{
  var count = _changeTracker.CommitChanges(conn);
  transaction.Commit();
  return count;
}
catch
{
  transaction.Rollback();
  throw;
}
finally
{
  transaction.Dispose();
}

我只是不知道这是正确的做法，什么可能的缺点。

I'm just not sure if this is the right approach and what the possible drawbacks are.

任何提示？

更新：：要澄清，这不是 work.Commit（）一个已经给我的问题，我敢肯定，这将的工作，但我从来没有到那里，因为我的查询的是失败。

UPDATE: To clarify, it's not the work.Commit() that's been giving me problems, I'm quite sure that it would work, but I never get there because my querying is what fails.

什么失败的例子：

public int? GetWarehouseID(int appID)
{
  var query = @"
select top 1 ID from OrganizationUnits o
where TypeID & 16 = 16 /* warehouse */";

  using (var conn = _dapper.OpenConnection())
  {
    var id = conn.Query<int?>(query).FirstOrDefault();

    return id;
  }
}

本时调用的TransactionScope 已被Rebus的创建，以及之后的SqlConnection 被打开画谜。开幕后的我的的SqlConnection ，它试图争取和崩溃。

This gets called when a TransactionScope has been created by Rebus, as well as after a SqlConnection is opened by Rebus. Upon opening my SqlConnection, it tries to enlist and crashes.

我们使用的是SQL Server 2012的

We're using SQL Server 2012.

推荐答案

我有些惊讶的是，你看到这一点，因为 RequiresNew 的应意味着它是从其他事务隔离;通常情况下，此消息意味着2连接已经被内部的事务范围激活 - 你的确定的没有其他的code创建/打开该块内部的连接

I am somewhat surprised that you're seeing this, because the RequiresNew should mean that it is isolated from the other transaction; usually, this message means that 2 connection have been activated inside a transaction scope - are you sure there is no other code creating / opening a connection inside that block?

您建议的解决方案应该工作 - 虽然在某些方面 TransactionScopeOption.Sup preSS 可能不是改变你的配置比较方便（但任选其一）。但是，有一个问题：ADO.NET数据必须被传递到各个命令，所以你需要（也整理了codeA位）：

Your proposed solution should work - although in some ways TransactionScopeOption.Suppress may be more convenient than changing your config (but either should work). However, there's a problem: ADO.NET transactions must be passed to the individual commands, so you would need (also tidying up the code a bit):

using(var transaction = conn.BeginTransaction()) {
    try {
        var count = _changeTracker.CommitChanges(conn, transaction);
        transaction.Commit();
        return count;
    } catch {
        transaction.Rollback();
        throw;
    }
}

其中，的CommitChanges 接受一个交易 - 或许使用可选的参数：

where CommitChanges accepts a transaction - perhaps using optional parameters:

int CommitChanges(DbConnection connection, DbTransaction transaction = null)
{ ... }

您正在使用短小精悍

您 DapperFactory 的命名暗示 - 在这种情况下，你可以传递到短小精悍无论是空或不是，即

Your naming of DapperFactory suggests you are using "dapper" - in which case, you can just pass that into "dapper" whether it is null or not, i.e.

conn.Execute(sql, args, transaction: transaction);

这篇关于SqlConnection和避免推广MSDTC的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！