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
– Ross Bush, Commented Nov 10, 2017 at 18:18
Also, setting NOLOCK's across the board does not guarantee that you will avoid deadlocks. — Ross Bush
– Ross Bush, Commented Nov 10, 2017 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
– Gert Arnold, Commented Nov 10, 2017 at 20:49

Community · Accepted Answer · 2020-06-20 09:12:55Z

3

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

edited Jun 20, 2020 at 9:12

CommunityBot

11 silver badge

answered Nov 11, 2017 at 0:38

Wagner DosAnjos

6,3841 gold badge18 silver badges30 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Running stored procedure in a SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related