Issue with TransactionScope and async calls with EF6

Question

I have the following code, intended to break a bulk EF save into smaller chunks, ostensibly to improve performance.

var allTasks = arrayOfConfigLists
        .Select(configList =>
            Task.Run(() => SaveConfigurations(configList))
        .ToArray();

Task.WaitAll(allTasks);

Each call to SaveConfigurations creates a new context that runs to completion.

private static void SaveConfigurations(List<Configuration> configs)
{
    using (var dc = new ConfigContext())
    {
        dc.Configuration.AutoDetectChangesEnabled = false;
        dc.SaveConfigurations(configs);
    }
}

As it stands, the code runs relatively efficiently, considering this might not be the optimal way of doing things. If one of the SaveConfigurations fails, however, I realized I would need to roll back any other configurations that were saved to the database.

After doing some research, I upgraded my existing frameworks to 4.5.1 and utilized the new TransactionScopeAsyncFlowOption.Enabled option to deal with async calls. I made the following change:

using (var scope = 
    new TransactionScope(TransactionScopeAsyncFlowOption.Enabled))
{
    //... allTasks code snippet from above
    scope.Complete();
}

At this point, I started aggregating all kinds of interesting errors:

The operation is not valid for the state of the transaction.

The underlying provider failed on Open.

Network access for Distributed Transaction Manager (MSDTC) has been disabled.

The transaction manager has disabled its support for remote/network transactions.

What I don't understand is why introducing TransactionScope would create so many issues. I assume I have a fundamental misunderstanding of how async calls interact with EF, and how TransactionScope wraps those calls, but I can't figure it out. And I really have no clue what the MSDTC exception pertains to.

Any thoughts as to how I could have rollback functionality with asynchronous calls made to the same database? Is there a better way to handle this situation?

Update: After reviewing the documentation here, I see that Database.BeginTransaction() is the preferred EF call. However, this assumes that all of my changes will occur within the same context, which it won't. Short of creating a dummy context and passing around a transaction, I don't believe this solves my issue.

score 3 · Accepted Answer · answered Nov 11 '15 at 11:21

3

This has nothing to do with async. You are writing on multiple connections and want that to be atomic. That requires distributed transactions. There is no way around that.

You also might run into distributed deadlocks this way that will only be resolved by timeouts.

Probably, the best approach is to stop using multiple connections. If performance is such a concern consider making the writes using one of the well known bulk DML techniques which do not involve EF.

You can use MARS to make concurrent writes on the same connection but they are really executed serially on the server. This would provide a small speedup though due to pipelining effects. Likely not worth the trouble

answered Nov 11 '15 at 11:21

usr

162,013
33
219
345

Thanks for the suggestions. We don't have a dedicated DB programmer in the office, so all of these issues are new to me. I guess the TransactionScope was a red herring? It seemed like the multiple context calls were working without it, but SQL Server must have been handling them behind the scenes. I'll run some tests using just one connection and see how it goes. I'm only inserting ~10,000 rows at worst, so I feel like processing times > 60 seconds is unreasonable. – MadHenchbot Nov 11 '15 at 16:53
1

No, TransactionScope is involved. Without it the changes are not atomic which is incorrect. With it the changes are atomic but require MSDTC (which is best avoided for a few reasons). – usr Nov 11 '15 at 17:28
`I'm only inserting ~10,000 rows at worst, so I feel like processing times > 60 seconds is unreasonable.` If you want that fixed ask a new question and leave a link here. Use SQL Profiler to capture the SQL executed and also post actual execution plans. – usr Nov 11 '15 at 17:29

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

How about this

This will only create one context i.e, and attach entities to the context. See entity framework bulk insertion

If there is anything goes wrong in the insertion the entire transaction will be roll-backed. If you want more transaction like pattern implement Unit of work pattern

as far as i know Entity framework itself has Unit of work pattern.

 public SaveConfigurations(List<Configuration> configs)
    {
        try
        {

            using (var dc = new ConfigContext())
            {
               dc.Configuration.AutoDetectChangesEnabled = false;
               foreach(var singleConfig in configs)
               {
                 //Donot invoke dc.SaveChanges on the loop.
                 //Assuming the SaveConfiguration is your table.
                 //This will add the entity to DbSet<T> , Will not insert to Db until you invoke SaveChanges
                 dc.SaveConfiguration.Add(singleConfig);
               } 
               dc.Configuration.AutoDetectChangesEnabled = true;
               dc.SaveChanges();
            }

        }
        catch (Exception exception)
        {
           throw exception
        }

    }

Thanks for your suggestion! I'll do some research on the Unit of work pattern and give this a try. — MadHenchbot, Nov 11 '15 at 16:54

Issue with TransactionScope and async calls with EF6

2 Answers2