Use of TransactionScope with read uncommitted - is with (nolock) in SQL necessary?

Question

I am using FluentNHibernate, and I have a list of records, mapped to an SQL Server 2008 view. Dirty reads are OK with me, not locking the tables is a priority.

The SQL Query inside the view, does not have any with (nolock), however, I am using the following approach...

using (var txScope = new TransactionScope(TransactionScopeOption.Suppress, new TransactionOptions() { IsolationLevel = System.Transactions.IsolationLevel.ReadUncommitted }))
{
   ... The reading of records from the view is done here, through Fluent NHibernate...
}

Does setting the isolation level at application layer to read uncommitted, apply with (nolock) to the queries which are generated within that context?

Solution

Short answer: No

Long answer:

Just defining the TransactionScope does not define that any read or write will be invoked within a transaction.

To run something within a transaction, you still have to open and commit a transaction!

The TransactionOptions of the TransactionScope for Timeout and IsolationLevel just define the defaults for any transaction created within the scope without those options explicitly set. Actually the TransactionScope does create a Transaction but it will not be active without opening a new Transaction. Internally this will do some complex stuff, cloning the transaction etc... so lets ignore this...

Without a transaction you cannot define the isolation level, any select statement will be run with IsolationLevel.ReadCommitted because this is the default of SQL Server.

You can also query session.Transaction.IsActive to see if a transaction is currently active for the session!

Lets have a look at the following code, I put some comments to make it a little bit more clear

using (var scope = new TransactionScope(TransactionScopeOption.Required,
                    new TransactionOptions()
                    {
                        IsolationLevel = IsolationLevel.ReadUncommitted
                    }))
{

    using (var session = sessionFactory.OpenSession())
    {
        // outside any transaction...
        var x = session.Transaction.IsActive; // false;

        // read will be done with SQL Server default (ReadCommited)
        var pp = session.Query<Page>().Where(p => p.Photos.Count() > 1).ToList();

        using (var transaction = session.BeginTransaction())
        {
            // will use ReadUncommitted according to the scope
            var y = session.Transaction.IsActive; // true;

            var p1 = session.Get<Page>(1);

            transaction.Commit();
        }
        using (var transaction = session.BeginTransaction(System.Data.IsolationLevel.ReadCommitted))
        {
            // will use ReadCommitted according to the transaction initialization
            var y = session.Transaction.IsActive; // true;

            var p1 = session.Get<Page>(1);

            transaction.Commit();
        }

        scope.Complete();
    }
}

You can also watch how SQL Server reacts to those settings by using the SQL Server Profiler.

Just create a new Trace and watch out for the Audit Login event, the text of the event will include the isolation level and you can see that it actually does a Audit Login each time a transaction is created, for example

 set transaction isolation level read uncommitted

--

Please correct me if any of this information might be wrong, I just figured this out by myself so there might be some potential of failure ;)

Comments

[DeveloperRob]

The Audit Login is not a reliable way to view the current isolation level of commands being executed. See my comment at LINQ + TransactionScope will not change isolation level in SQL Server Profiler. The changing of the isolation level is difficult to view in Profiler and can happen between the Audit Login line and the SQL statement under consideration. This is best explained in the comments from divega at entityframework.codeplex.com/workitem/1712.

[Jess]

Ha! Funny when this happens. Turns out I had a transaction that was too big in my batch job. I batched the transaction, and now I don't need a dirty read anymore. (ReadUncommitted)