Nested Transaction Behavior in EF6

Question

I'm currently using TransactionScope to manage transactions in my data layer, but I've been running into issues with nested transactions and async whereby the connection seems to close during the nested transaction or the transaction is promoted to MSDTC. I've not found the exact problem but after reading around it looks like this scenario isn't particuarly well supported and that I should be using Database.BeginTransaction() instead.

My problem is that I can't find information on how Database.BeginTransaction() works with nested transactions, particularly in my scenario where i'm wanting to use the ambient transaction rather than create a new one. My suspicion is that it isn't intended to work this way and if I want to manage nested transactions I should abstract out transaction management to give me more control.

Not wanting to add in unnecessary layers of abstractions I wanted to know if anyone has experience in this area and could confirm the behavior of Database.BeginTransaction() when nested inside another transaction?

Additional information about my DAL: Based on CQS pattern, I tend to encapsulate Db related code in command or query handlers, so a simplified/contrived example of how this nesting occurs would be:

public class AddBlogPostHandler
{
    private readonly MyDbContext _myDbContext;

    public AddBlogPostHandler(MyDbContext myDbContext)
    {
        _myDbContext = myDbContext;
    }

    public async Task ExecuteAsync(AddBlogPostCommand command)
    {
        using (var scope = new TransactionScope(TransactionScopeAsyncFlowOption.Enabled))
        {
            // .. code to create and add a draft blog post to the context
            await _myDbContext.SaveChangesAsync();

            var publishBlogPostCommand = new PublishBlogPostCommand();
            // ..set some variables on the PublishBlogPostCommand
            await PublishBlogPostAsync(command);

            scope.Complete();
        }
    }
}

public class PublishBlogPostHandler
{
    private readonly MyDbContext _myDbContext;

    public PublishBlogPostHandler(MyDbContext myDbContext)
    {
        _myDbContext = myDbContext;
    }

    public async Task ExecuteAsync(PublishBlogPostCommand command)
    {
        using (var scope = new TransactionScope(TransactionScopeAsyncFlowOption.Enabled))
        {
            // .. some code to do one set of update
            await _myDbContext.SaveChangesAsync();

            // .. some other db updates that need to be run separately
            await _myDbContext.SaveChangesAsync();

            scope.Complete();
        }
    }
}

Solution

There is no such thing as nested transactions in the sense that the inner one can commit or rollback independently. Nested transactions really only maintain a ref count. At the last commit we get a physical commit. At the first rollback we get a physical rollback. Just making sure you are aware of that.

It is important to avoid MSDTC usage. This is possible both with TransactionScope and with BeginTransaction. With the former you need to explicitly Open the connection inside the scope so that EF does not open new connections all the time.

As you have read in the issue this is a flaw in EF (which L2S did not have). Please take the time to comment on the issue to make sure the team is aware that customers are running into this problem.

particularly in my scenario where i'm wanting to use the ambient transaction rather than create a new one.

This is perfect for TransactionScope. I think your switch to BeginTransaction is based on a misunderstanding. Maybe you can clarify in the comments.

confirm the behavior of Database.BeginTransaction() when nested inside another transaction

Explained in the first paragraph.

Additional information about my DAL: Based on CQS pattern, I tend to encapsulate Db related code in command or query handlers, so a simplified/contrived example of how this nesting occurs would be:

The code looks fine except for the missing db.Connection.Open() call (as explained above).

This pattern will support executing multiple queries and commands in the same transaction. Just wrap another scope around it. Make sure to not open connections twice, e.g. check conn.State before taking action.

Comments

[Joel Mitchell]

I think i'm really asking if a nested call to BeginTransaction creates a new transaction (equivalent to TransactionScopeOption.RequiresNew) or uses the ambient transaction (Equivalent to TransactionScopeOption.Required)? Aside from my inability to get it working, the switch to BeginTransaction was based on it being their recommendation for EF6+. I'm open to trying to get it working with TransactionScope, but if I open the connection manually i get an exception before the outer transaction has completed (transaction "completed but has not been disposed")

[usr]

BeginTransaction is like Required. There is no way to have multiple trans on the same connection. Therefore, it can't possibly behave like RequiresNew. being their recommendation for EF6 I read that too but I think no reasoning was given. Strange advice. i get an exception before the outer transaction has completed I guess you should investigate and fix the bug. Why drop the entire approach if all you need to do is fix a minor bug?

[usr]

@Triynko are you sure BeginTransaction throws when called twice? I do not think so and it would not make sense to do that. It would break nesting. RequiresNew definitely does not throw. It creates a new Transaction and restores the old one on scope exit.