Running stored procedure in a SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

c# entity-framework-6 read-uncommitted sql-server stored-procedures

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.

1
0
11/10/2017 6:16:38 PM

Accepted Answer

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

1
11/11/2017 12:51:54 AM


Related Questions





Related

Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow