CODEWITHSUNDEEP
c#entity-frameworkdbcontexttransactionscope
Vladi Pavelka
Vladi Pavelka

Reputation: 926

DbContext.SaveChanges() visibility within a TransactionScope

Given a TransactionScope with 2 subsequently opened DbContexts, are changes saved by the first context guaranteed to be visible within the scope of second context?

var txOptions = new TransactionOptions { IsolationLevel = IsolationLevel.ReadCommitted };
using (var transaction = new TransactionScope(TransactionScopeOption.Required, txOptions))
{
    using (var context1 = new MyDbContext())
    {
        context1.Employees.Single(e => e.Id == 1).Salary += 1; // Update
        context1.Employees.Remove(context1.Employees.Single(e => e.Id == 2)); // Delete
        context1.Employees.Add(new Employee { Id = 3 }); // Add

        context1.SaveChanges();
    }

    using (var context2 = new MyDbContext())
    {
        // are changes saved by context1 guaranteed to be visible here?
    }

    transaction.Complete();
}

Normally I would expect they are, but then I saw "No, this was a coincidence because the 2nd context reused the connection of the 1st from the connection pool. This is not guaranteed and will break under load." which made me puzzled. Could anyone please confirm or disprove this?

Upvotes: 1

Views: 351

Answers (1)

StuartLC
StuartLC

Reputation: 107407

My understanding is that all efforts will be made to ensure that your thread receives the same connection from the pool, provided that you close each connection prior to requesting another, and provided that the connection strings are identical. Reusing the same connection will prevent the transaction from escalating to DTC.

However if you still want further guarantees, there is an overload of the DbContext constructor which takes an existing connection. In this way, you would be able to guarantee that the two Contexts use the same Connection. This way you don't need to worry about the behaviour of the lightweight transaction manager. IMO you'd be best off opening your own connection, and passing this to both contexts, with the contextOwnsConnection flag set to false.

(Hopefully the below is all Hypothetical)

The consequences of not reusing the connection are dire - if context2 was not able to reuse the same connection as context1, the bounding TransactionScope would escalate into a distributed transaction, which would in any case cause further potential lock + deadlock issues.

i.e. in your example, the 3 rows associated with the modified employees in context1 would be blocked to a second database connection on context2 with a ReadCommited isolation level, until the Transaction is either committed, or rolled back (i.e. your program could hang until the transaction, or command, timed out).

I guess one burning question - since both contexts use the same database, if possible, try to combine the two contexts. This way you can avoid the bounding TransactionScope altogether - SaveChanges() acts as a single phase transaction against a single connection.

Upvotes: 1

Related Questions