Running stored procedure in a SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

Question

I setup a DbContext where I have this code:

base.Database.ExecuteSqlCommand("SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;");

in the context constructor. I execute a stored procedure like this:

return base.Database.ExecuteSqlCommand("EXEC mystoredprocedure");

but I'm getting a deadlock because this executes in a read committed connection.

I'm asking is there any reason why the stored procedure would run in a read committed fashion when it is part of the same context that has the read uncommitted connection.

Answer 1

Try using TransactionScope instead as follows:

using (var txn = new TransactionScope(
                TransactionScopeOption.RequiresNew, 
                new TransactionOptions { IsolationLevel = IsolationLevel.ReadUncommitted })
{
    return base.Database.ExecuteSqlCommand("EXEC mystoredprocedure");
}

Or using Database.BeginTransaction:

using (var txn = base.Database.BeginTransaction(IsolationLevel.ReadUncommitted))
{
    return base.Database.ExecuteSqlCommand("EXEC mystoredprocedure");
}

EF wraps each ExecuteSqlCommand in its own transaction by default. That's why the first call to SET ISOLATION LEVEL does not extend the transaction to the 2nd call to EXEC proc. More about the working with transactions in EF in the link below:

Entity Framework Working with Transactions

https://msdn.microsoft.com/en-us/library/dn456843(v=vs.113).aspx