Use TransactionScope & SqlCommand to keep databases in sync

Question

We track the same information across two databases in tables that have a similar (enough) schema. When we update the data in one database we want to make sure the data stays in sync with the table in the other database.

We use Entity Framework 5 in both databases, so I had originally wanted to simply import a DbContext of the secondary database and use TransactionsScope to make sure the Create/Updates were atomic.

However, I quickly found out that would be a pain to code, since the table names are the same (anyone working in this controller would have to refer to the Product table as <Conext>.Product), so I used a SqlConnection object for the secondary table, but received some results I don't quite undestand.

If I use the syntax below, the two tables will update atomically/everything goes as planned.

var scopeOptions = new TransactionOptions();
scopeOptions.IsolationLevel = System.Transactions.IsolationLevel.ReadCommitted;
scopeOptions.Timeout = TimeSpan.MaxValue;

var sqlConn = new SqlConnection(ConfigurationManager.ConnectionStrings["Monet"].ConnectionString);
sqlConn.Open();
SqlCommand sqlCommand = sqlConn.CreateCommand();
sqlCommand.CommandText = InsertMonetProduct(product);

using (var ts = new TransactionScope(TransactionScopeOption.Required, scopeOptions))
{
    db.Product.Add(product);
    db.SaveChanges(); 
    sqlCommand.ExecuteNonQuery();
    ts.Complete();
}

However if I use this syntax below the code crashes on the db.SaveChanges() command with the following message:

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.

var scopeOptions = new TransactionOptions();
scopeOptions.IsolationLevel = System.Transactions.IsolationLevel.ReadCommitted;
scopeOptions.Timeout = TimeSpan.MaxValue;

using (var ts = new TransactionScope(TransactionScopeOption.Required, scopeOptions))
{
    using(var sqlConn = new SqlConnection(ConfigurationManager.ConnectionStrings["Monet"].ConnectionString))
    {
        sqlConn.Open();
        using (SqlCommand sqlCommand = sqlConn.CreateCommand())
        {
            sqlCommand.CommandText = InsertMonetProduct(product);
            sqlCommand.ExecuteNonQuery();

            db.Product.Add(product);
            db.SaveChanges();                                
        }
        ts.Complete();
    }
}

Any idea why the first syntax works and the second crashes? From what I've read online this is supposed to be a change made on the database/database server itself.

Have you tried to put the `ts.Complete` inside the `using (SqlCommand sqlCommand...` block? Could it be, that the transaction somehow wraps/needs the command and because the command is destroyed on completion (because of the using block) it fails? — keenthinker, Nov 18 '15 at 20:28
It has something to do with the isolation level of EF. Maybe overwriting the [SaveChanges method](http://stackoverflow.com/questions/5657145/what-is-the-default-transaction-isolation-level-in-entity-framework-when-i-issue) or changing the isolation level could solve the problem. — keenthinker, Nov 18 '15 at 20:33
Have a look at point 17. int this CodeProject article [All About TransactionScope](http://www.codeproject.com/Articles/690136/All-About-TransactionScope). It looks similiar to your case. — keenthinker, Nov 18 '15 at 20:35
@pasty: Took a look and that seems to also suggest editing the Security Configuration on the database server. Excellent resource for TransactionScope in general, though, thanks! — NealR, Nov 18 '15 at 21:21

score 1 · Answer 1 · edited May 23 '17 at 12:23

The second bit of code is causing an error because it is opening multiple database connections within a single TransactionScope. When a program opens a second database connection inside of a single scope, it gets promoted to a distributed transaction. You can read more information about distributed transactions here.

Searching for "multiple database connections in one transaction scope" is going to help you find a lot more StackOverflow posts. Here are two relevant ones:

Before you walk off into the land of distributed transactions though, there may be a simpler solution for this case. Transaction scopes can be nested, and parent scopes will rollback if any of their nested scopes fail. Each scope only has to worry about one connection or just nested scopes, so we may not run into the MSDTC issue.

Give this a try:

var scopeOptions = new TransactionOptions();
scopeOptions.IsolationLevel = System.Transactions.IsolationLevel.ReadCommitted;
scopeOptions.Timeout = TimeSpan.MaxValue;

using (var ts = new TransactionScope(TransactionScopeOption.Required, scopeOptions))
{
    using (var scope1 = new TransactionScope(TransactionScopeOption.Required))
    {
        // if you can wrap a using statment around the db context here that would be good
        db.Product.Add(product);
        db.SaveChanges();
        scope1.Complete();
    }
    using (var scope2 = new TransactionScope(TransactionScopeOption.Required))
    {
        // omitted the other "using" statments for the connection/command part for brevity
        var sqlConn = new SqlConnection(ConfigurationManager.ConnectionStrings["Monet"].ConnectionString);
        sqlConn.Open();
        SqlCommand sqlCommand = sqlConn.CreateCommand();
        sqlCommand.CommandText = InsertMonetProduct(product);
        sqlCommand.ExecuteNonQuery(); // if this fails, the parent scope will roll everything back
        scope2.Complete();
    }
    ts.Complete();
}

Still ran into the same MSDTC error on the `sqlConn.Open()` command — NealR, Nov 19 '15 at 18:34

Use TransactionScope & SqlCommand to keep databases in sync

1 Answers1