Does changing isolation level using TransactionScope in LINQ to SQL for NOLOCK impact the connection?

Question

I'm testing out using TransactionScope with options to set the isolation level to ReadUncommitted to execute specific queries with as such. What I'm seeing, however, is that because the isolation level is set on the connection, when the connection is reused for other queries the isolation level is still ReadUncommitted instead of resetting to the default ReadCommitted.

Per many suggestions, I abstracted new NoLock methods as extensions as follows:

public static class QueryableExtensions
{
    static TransactionScope CreateNoLockTransaction()
    {
        return new TransactionScope(TransactionScopeOption.Required, new TransactionOptions
        {
            IsolationLevel = IsolationLevel.ReadUncommitted
        });
    }

    public static T[] ToNoLockArray<T>(this IEnumerable<T> query)
    {
        using (var ts = CreateNoLockTransaction())
        {
            return query.ToArray();
        }
    }

    public static List<T> ToNoLockList<T>(this IEnumerable<T> query)
    {
        using (var ts = CreateNoLockTransaction())
        {
            return query.ToList();
        }
    }

    public static int NoLockCount<T>(this IEnumerable<T> query)
    {
        using (var ts = CreateNoLockTransaction())
        {
            return query.Count();
        }
    }
}

I then wanted to verify the isolation level on the various queries I run both within the transaction scope and without. To do that I started executing the following query using the context of the query:

db.ExecuteQuery<int>("select cast(transaction_isolation_level as int) from sys.dm_exec_sessions where session_id = @@SPID").FirstOrDefault();

Running the above before executing the NoLock extension methods returns the isolation level as 2. After running the NoLock extension methods and checking the isolation level of queries outside the transaction scope then returns 1.

Does this mean that when changing the isolation level using TransactionScope the impacted connection, when reused for other queries and data contexts, continues to use the ReadUncommitted isolation level? Doesn't that defeat the purpose of using a transaction to temporarily change isolation level for a specific query since it impacts all queries thereafter?

Off topic - As I'm sure you know `READ UNCOMMITTED` can return dirty reads. These can include records that technically were never part of the database. [Source - MSDN](https://msdn.microsoft.com/en-GB/library/ms173763.aspx). For more see [this blog entry](http://blogs.sqlsentry.com/aaronbertrand/bad-habits-nolock-everywhere/). — David Rushton, Mar 22 '16 at 15:28
Right and that's precisely the reason I want to execute only certain queries that I'm ok with accounting for dirty reads with READ UNCOMMITTED isolation level. The problem is the solution to make that happen in LINQ to SQL and perhaps even EF is impacting other queries as well which is not desired behavior. — Huzaifa Tapal, Mar 22 '16 at 15:40
This must be related to the real connection in the connection pool living somewhere between [4 minutes and 7 minutes, 40 seconds](http://www2.sys-con.com/itsg/virtualcd/dotnet/archives/0112/smith/index.html). — Gert Arnold, Nov 02 '17 at 15:10

score 0 · Answer 1 · answered Aug 26 '16 at 14:12

0

you need to close the scope using

ts.Complete()

after the query and before the return

answered Aug 26 '16 at 14:12

Calimero100582

832
1
7
13

score 0 · Answer 2 · answered Oct 30 '17 at 19:35

We have the exact same problem as this. We use LINQ to SQL and using the TransactionScope object causes the entire sql spid to be read uncommited.

This is the only way I could find to only do the current query read uncommited:

dbContext.ExecuteCommand("SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED");

var Data = query.ToList();

dbContext.ExecuteCommand("SET TRANSACTION ISOLATION LEVEL READ COMMITTED");

This is obviously not a great way to do it as other queries executing in the sql process could be affected in the few ms this is open and it generates two additional calls to the DB service.

If there is a way to have LINQ generate the transaction isolation level inside the select statement that would be a lot better.

Does changing isolation level using TransactionScope in LINQ to SQL for NOLOCK impact the connection?

2 Answers2