EF 6 suspend Execution Strategy and overlapping executions - "does not support user initiated transactions" exception

Question

I implemented suspension of the EF 6 execution strategy wherever I need to use distributed transaction to avoid "'SqlAzureExecutionStrategy' does not support user initiated transactions" exceptions, following these examples:

https://romiller.com/2013/08/19/ef6-suspendable-execution-strategy/ https://msdn.microsoft.com/en-us/library/dn307226(v=vs.113).aspx

However, recently I got two failures of this during a bulk data management session, resulting in getting the above exception after all.

If I understand it correctly, the given examples enable/disable the execution strategy on a global level, which would mean that if actions are executed simultaneously on multiple threads one action can end the suspension before another one is completed. The effect is probably most noticeable if using .NET 4.6.1 for transactions spanning multiple SQL Azure DBs, which can take a while to complete.

To avoid this I resorted to creating a global transaction counter, which is incremented and decremented in a thread safe way, and lifting the suspension only if there are no transactions pending anymore, like:

    public class MyConfiguration : DbConfiguration 
    { 
        public MyConfiguration() 
        { 
            this.SetExecutionStrategy("System.Data.SqlClient", () => SuspendExecutionStrategy 
              ? (IDbExecutionStrategy)new DefaultExecutionStrategy() 
              : new SqlAzureExecutionStrategy()); 
        } 

        public static bool SuspendExecutionStrategy 
        { 
            get 
            { 
                return (bool?)CallContext.LogicalGetData("SuspendExecutionStrategy") ?? false; 
            } 
            set 
            { 
                CallContext.LogicalSetData("SuspendExecutionStrategy", value); 
            } 
        } 
    } 

And then:

public class ExecutionHelper
{
    private static int _pendingTransactions;

    public void ExecuteUsingTransaction(Action action)
    {
        SuspendExeutionStrategy();
        try
        {
            using (var transaction = new TransactionScope())
            {
                action();
                transaction.Complete();
            }
            ResetSuspension();
        }
        catch (Exception ex)
        {
            ResetSuspension();
            throw ex;
        }
    }

    private void SuspendExeutionStrategy()
    {
        Interlocked.Increment(ref _pendingTransactions);           
        MyConfiguration.SuspendExecutionStrategy = true;
    }


    private void ResetSuspension()
    {
        Interlocked.Decrement(ref _pendingTransactions);
        if (_pendingTransactions < 1 )
        {
            MyConfiguration.SuspendExecutionStrategy = false;
        }
    }
}

I am still puzzled though that the example given on MSDN does not take this into account. Is there something I overlooked?

Answer 1

I got the same error:
" 'SqlAzureExecutionStrategy' does not support user initiated transactions"
And I rewrote my code like this and it works:

var strategy = _context.Database.CreateExecutionStrategy();
await strategy.ExecuteAsync(async () =>
  {
    using (var dbContextTransaction = _context.Database.BeginTransaction())
    {
        // Your code here                    
        dbContextTransaction.Commit();
    }
    catch (Exception ex)
    {
        dbContextTransaction.Rollback();
        throw;
    }
  }
});