SqlConnection 并避免升级到 MSDTC [英] SqlConnection and avoiding promotion to MSDTC

问题描述

当我们需要在我们的应用程序中进行数据库访问时，我们使用以下模式:

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

• 为了查询，我们有一个带有方法 CreateOpenConnection 的静态工厂类，它只执行 new SqlConnection(myConnectionString) 并调用 Open()代码>就可以了.在我们执行查询之前调用此方法，并在查询返回后处理连接.
• 对于插入/更新/删除，我们使用工作单元模式，其中更改被批处理并通过调用 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:

工作.提交:

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

      tranScope.Complete();

      return count;
    }
}

这对于作为网络服务的一部分的一般用途似乎非常有用，但是当我尝试将它与 Rebus 结合使用时，目前给我带来了 MSDTC 问题.

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，以便在处理消息失败的情况下，可以回滚内容.现在，到目前为止，这本身运行良好.我可以在 Rebus 消息处理程序中打开一个新的 SqlConnection 而没有任何问题(但是，使用我们的旧实体框架查询和在同一个 Rebus TransactionScope 中手动 SqlConnectionscode> 不起作用，但我现在不认为这是一个问题).但昨天我问了以下问题:

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:

某种消息类型的串行处理在雷布斯

答案似乎是使用 Rebus 的传奇功能.我尝试实现并配置它，以便 Rebus 传奇被持久化到一个新的 SQL Server 数据库(具有不同的连接字符串).据推测，使用 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) 的网络访问已被禁用.请使用组件服务管理工具在 MSDTC 的安全配置中为网络访问启用 DTC.

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.

我认为这里发生的是 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:

• 将 Enlist=false 添加到我的应用程序的连接字符串中，以便它永远不会注册到环境事务中.
• 修改 Commit 方法，使其不会创建新的 TransactionScope(我的连接不会再订阅它，因为我只是告诉它应该't) 但它使用 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;
  }
}

当 Rebus 创建了 TransactionScope 时，以及在 Rebus 打开 SqlConnection 之后，它会被调用.在打开 my 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

推荐答案

你看到这个我有点惊讶，因为 RequiresNew 应该 意味着它是与其他交易隔离；通常，此消息意味着在事务范围内已激活 2 个连接 - 您确定在该块内没有其他代码创建/打开连接吗?

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.Suppress 可能比更改配置更方便(但两者都应该可行).但是，有一个问题:ADO.NET 事务必须传递给各个命令，因此您需要(也稍微​​整理一下代码):

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 的命名表明您正在使用dapper"——在这种情况下，您可以将它传递给dapper"，无论它是否为空，即

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屋！