Entity Framework TransactionScope rollback issue

Question

I'm using Entity Framework with a TransactionScope. It looks like I'm having a problem involving all of my changes not rolling back if there is a deadlock issue once calling the Complete() method for the TransactionScope. Note, within this TransactionScope I am updating two separate databases, so could the issue be that it will only rollback the changes for the database that got the deadlock issue?

Code example:

try
{
    using (TransactionScope scope = new TransactionScope())
    {
        //Insert into database1 (getting the deadlock issue)
        database1.SaveChanges();
        //Update ExternalId (Identity PK from database1) in database2
        database2.SaveChanges();
        scope.Complete();
    }
}
catch (Exception ex)
{
    throw;
}

In the above example, database2 gets the ExternalId column updated but the record is never inserted into database1, which is weird because the ExternalId is the identity record that we get when inserting the record into database1.

are you creating the database1 and database2 after you create the TransactionScope? — Hasani Blackwell, Jul 22 '14 at 00:45
It might be. The DbContext will join the TransactionScope the first time a connection is made to the database. Executing linq, calling SaveChanges and setting some connection options such as CommandTimeout are some ways of opening a database connection. I don't recommend making a dbcontext as a class variable because you want to open and close it immediately, similar to how people don't usually make SqlConnection as class level fields/properties. So just like how you have a using block for the TransactionScope, you should be creating dbcontextes below it and disposing them in the using block. — Hasani Blackwell, Jul 22 '14 at 22:27

score 0 · Answer 1 · answered Jul 09 '15 at 02:46

If your connections are created (and opened?) outside the TransactionScope, then it needs to be manually enlisted into the Transaction with a call to Connection.EnlistTransaction(Transaction.Current);. e.g.

try
{
    using (TransactionScope scope = new TransactionScope())
    {
        database1.Connection.EnlistTransaction(Transaction.Current);
        database2.Connection.EnlistTransaction(Transaction.Current);

        //Insert into database1 (getting the deadlock issue)
        database1.SaveChanges();
        //Update ExternalId (Identity PK from database1) in database2
        database2.SaveChanges();
        scope.Complete();
    }
}
catch (Exception ex)
{
    throw;
}

Entity Framework TransactionScope rollback issue

1 Answers1