Multiple databases(datacontext) on same server without MS DTC

Question

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();
        }
    }

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?

Please advise

Thanks in advance

Sai

Evgeniy Berezovsky · Answer 1 · 2020-10-08T21:53:40.000

10

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();
}

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

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

But please note that even with a shared connect string, as soon as you have more than one connection 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();
}

edited Oct 08 '20 at 21:53

answered Oct 11 '13 at 08:01

Evgeniy Berezovsky

18,571
13
82
156

I want to commit transactions on two completely different dbs on same server under one transaction scope. I could do that through stored procedures but not through EF. Thanks for your answer though. – Sai Oct 11 '13 at 13:19
My answer does show how to use a `TransactionScope` to do that without resorting to stored procs. The implicit `tx.Dispose()` that occurs after the `tx.Complete()` line will commit the single transaction which encompasses both INSERTs to two different databases. As mentioned, with EF there are ways to provide connections where you manually called `ChangeDatabase()`, but depending on your code base, that might result in more tighly coupled code (while the point of `TransactionScoep` is to decouple code), only you can tell. – Evgeniy Berezovsky Oct 15 '13 at 01:34
I dont think I could use ChangeDatabase() as I'm using Databasefirst approach and my dbcontext is tightly bound to the entities. If I change the connectionstring at runtime to a completely different database, it will throw error. – Sai Oct 15 '13 at 12:26
`calling ChangeDatabase()` and *changing the connectionString at runtime* are two different things. But if you don't create `DbContext`s yourself, you might indeed be out of luck. – Evgeniy Berezovsky Oct 16 '13 at 01:09
Just want to say this is an extremely useful answer that so far I have only found here. I would mark it as correct if I could! – Josh Feb 12 '15 at 15:14
@Josh Sai was asking how to do it in EF and, lacking EF knowledge, I cannot tell if my generic recipe can be realized in EF. So maybe it's not an answer for him. But I'm glad it was helpful to you. – Evgeniy Berezovsky Feb 13 '15 at 01:22

Multiple databases(datacontext) on same server without MS DTC

1 Answers1

Linked