在没有MS DTC的同一服务器上的多个数据库（数据报文） [英] Multiple databases(datacontext) on same server without MS DTC

问题描述

我使用EF5.0与SQL Server 2008.我有两个数据库在同一服务器实例上。我需要更新两个数据库上的表，并希望它们是同一个事务。所以我使用TransactionScope。下面是代码 -

I'm using EF5.0 with SQL server 2008. I have two databases on the same server instance. I need to update tables on both databases and want them to be same transaction. So I used the TransactionScope. Below is the code -

public void Save()
{
        var MSObjectContext = ((IObjectContextAdapter)MSDataContext).ObjectContext;
        var AWObjectContext = ((IObjectContextAdapter)AwContext).ObjectContext;

        using (var scope = new TransactionScope(TransactionScopeOption.Required,
                                             new TransactionOptions
                                                 {
                                                     IsolationLevel = IsolationLevel.ReadUncommitted
                                                 }))
        {               
            MSObjectContext.SaveChanges(SaveOptions.DetectChangesBeforeSave);
            AWObjectContext.SaveChanges(SaveOptions.DetectChangesBeforeSave);

            scope.Complete();
        }
    }

当我使用上述代码时事务被提升为DTC中。在互联网上搜索后，我发现这是因为两个不同的连接串/连接。但是我不明白是如果我在一个数据库上写一个存储过程，更新不同数据库中的表（在同一服务器上），不需要DTC。那么为什么EF或TransactionScope将此推广到DTC？

When I use the above code Transaction gets promoted to DTC. After searching on internet I found that this happens because of two different connectionstrings / connections. But what I dont understand is if I write a stored procedure on one database which updates table in a different database (on same server) no DTC is required. Then why EF or TransactionScope is promoting this to DTC? Is there any other work around for this?

请提供

提前感谢

Sai

推荐答案

使用简单 DbConnection s，您可以通过使用相同的连接字符串（与您喜欢的任何数据库）并手动更改打开的连接对象上的数据库，从而防止同一服务器上的多个数据库的DTC上报：

With plain DbConnections, you can prevent DTC escalation for multiple databases on the same server by using the same connection string (with any database you like) and manually change the database on the opened connection object like so:

using (var tx = new TransactionScope())
{
    using (var conn = new SqlConnection(connectStr))
    {
        conn.Open();
        new SqlCommand("INSERT INTO atest VALUES (1)", conn).ExecuteNonQuery();
    }
    using (var conn = new SqlConnection(connectStr))
    {
        conn.Open();
        conn.ChangeDatabase("OtherDB");
        new SqlCommand("INSERT INTO btest VALUES (2)", conn).ExecuteNonQuery();
    }
    tx.Complete();
}

这不会升级到DTC，但如果使用不同的值 connectStr 。

This will not escalate to DTC, but it would, if you used different values for connectStr.

我不熟悉EF以及如何管理连接和上下文，洞察，您可以通过执行 conn.ChangeDatabase（..），然后创建您的上下文，如 new DbContext（conn，...） 。

I'm not familiar with EF and how it manages connections and contexts, but using the above insight, you might be able to avoid DTC escalation by doing a conn.ChangeDatabase(..) and then creating your context like new DbContext(conn, ...).

但是请注意，即使有一个共享的连接字符串，只要有两个连接同时打开，DTC将参与，就像这个修改的例子：

But please note that even with a shared connect string, as soon as you have two connections open at the same time, the DTC will get involved, like in this modified example:

using (var tx = new TransactionScope())
{
    using (var conn = new SqlConnection(mssqldb))
    {
        conn.Open();
        new SqlCommand("INSERT INTO atest VALUES (1)", conn).ExecuteNonQuery();
        using (var conn2 = new SqlConnection(mssqldb))
        {
            conn2.Open();
            conn2.ChangeDatabase("otherdatabase");
            new SqlCommand("INSERT INTO btest VALUES (2)", conn2).ExecuteNonQuery();
        }
    }
    tx.Complete();
}

这篇关于在没有MS DTC的同一服务器上的多个数据库（数据报文）的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！