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.

Be careful, a stored procedure can define its own isolation level. Also, mystoredprocedure can call other stored procedures that execute with another level. — Ross Bush, Nov 10 '17 at 18:18
Also, setting NOLOCK's across the board does not guarantee that you will avoid deadlocks. — Ross Bush, Nov 10 '17 at 18:21
The two statements run their own sessions. Also, I wouldn't run any database access code in a context's constructor. — Gert Arnold, Nov 10 '17 at 20:49

score 2 · Accepted Answer · edited Jun 20 '20 at 09:12

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

Running stored procedure in a SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

1 Answers1