CODEWITHSUNDEEP
c#entity-framework-4transactionscope
Alfred
Alfred

Reputation: 800

TransactionScope, EF DbContext, and Dirty Read

I've been Googling for an answer for the past few hours, but haven't found an answer, so I'm hoping someone here can point me in the right direction.

I am wondering how to do a dirty read with EF DbContext (Code-First) within a TransactionScope. For example

DbContext context = new MyEntities();
using(TransactionScope scope = new TransactionScope())
{
    context.SomeTable.Add(someObject);
    context.SaveChanges();

    var objects = context.SomeTable.Where(...).Select(...); //times out here on the read, because the write above locks the tables
    //modify each object
    context.SaveChanges();

    scope.Complete(); //transaction is a success only if the entire batch succeeds
}

I have tried wrapping the read call with the following:

using(TransactionScope scope2 = new TransactionScope(TransactionScopeOption.RequiresNew, new TransactionOptions{IsolationLEvel = IsolationLevel.ReadUncommitted}))
{
    var objects = context.SomeTable.Where(...).Select(...); //times out here on the 
}

What is the proper approach?

Upvotes: 1

Views: 2624

Answers (3)

MarkB
MarkB

Reputation: 174

You can set the IsolationLevel for TransactionScope like this...

var transactionOptions = new System.Transactions.TransactionOptions();
transactionOptions.Timeout = new TimeSpan(0, 0, 30);
transactionOptions.IsolationLevel = System.Transactions.IsolationLevel.ReadUncommitted;
using (var transactionScope = new System.Transactions.TransactionScope(System.Transactions.TransactionScopeOption.Required, transactionOptions))
{
...
}

Upvotes: 1

usr
usr

Reputation: 171168

It has finally clicked for me what the problem is: EF is not integrating with TransactionScope the way L2S nicely does. This means that EF opens and closes the connection for each operation requiring the server (saving changes or querying).

This gets you distributed transactions and a distributed deadlock.

To solve this, manually open and close the EF StoreConnection to ensure that there is exactly one connection for the duration of the transaction.

Upvotes: 1

a1ex07
a1ex07

Reputation: 37354

You can just execute alter database your_db_name set READ_COMMITTED_SNAPSHOT on;(available from SQLServer 2005) on the server, and readers won't be blocked by writters (assuming reading transaction in read committed isolation level).

Upvotes: 0

Related Questions