Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

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

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?

like image 235
Mez Avatar asked Sep 30 '13 09:09

Mez


People also ask

Is read uncommitted the same as Nolock?

In a nutshell, nolock (read uncommitted) takes no shared locks to prevent other transactions from modifying data read by this transaction. It also effectively ignores exclusive locks taken by other transactions when they have added or changed data but not committed it yet.

Should I use read uncommitted?

Read uncommitted is the weakest isolation level because it can read the data which are acquired exclusive lock to the resources by the other transactions. So, it might help to avoid locks and deadlock problems for the data reading operations.

How do you use set transaction isolation level read uncommitted?

The isolation level of the transactional support is default to READ UNCOMMITTED. You can change it to READ COMMITTED SNAPSHOT ISOLATION by turning ON the READ_COMMITTED_SNAPSHOT database option for a user database when connected to the master database.

What is read committed isolation level in SQL Server?

READ COMMITTED is the default isolation level for SQL Server. It prevents dirty reads by specifying that statements cannot read data values that have been modified but not yet committed by other transactions.


1 Answers

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 ;)

like image 142
MichaC Avatar answered Oct 26 '22 09:10

MichaC