Entity Framework with NOLOCK

Question

How can I use the NOLOCK function on Entity Framework? Is XML the only way to do this?

Wilber

Answer 1

No, but you can start a transaction and set the isolation level to read uncommited. This essentially does the same as NOLOCK, but instead of doing it on a per table basis, it will do it for everything within the scope of the transaction.

If that sounds like what you want, here's how you could go about doing it...

//declare the transaction options
var transactionOptions = new System.Transactions.TransactionOptions();
//set it to read uncommited
transactionOptions.IsolationLevel = System.Transactions.IsolationLevel.ReadUncommitted;
//create the transaction scope, passing our options in
using (var transactionScope = new System.Transactions.TransactionScope(
    System.Transactions.TransactionScopeOption.Required, 
    transactionOptions)
)

//declare our context
using (var context = new MyEntityConnection())
{
    //any reads we do here will also read uncomitted data
    //...
    //...
    //don't forget to complete the transaction scope
    transactionScope.Complete();
}

Hummel

Excellent @DoctaJonez Was anything new introduced in EF4 for this? – Thacker

@Thacker I don't know if anything new was introduced for EF4. I do know that the above code works with EFv1 and above though. – Hummel

what would be the consequence? if someone omits the transactionScope.Complete() in the block mentioned above? Do you think I should file another question for this? – Garber

@EakanGopalakrishnan Failing to call this method aborts the transaction, because the transaction manager interprets this as a system failure, or exceptions thrown within the scope of transaction. (Taken from MSDN msdn.microsoft.com/en-us/library/…) – Hummel

Yes I found that link saying that it aborts the transaction. But I also realized that if the transaction was just for read only purposes, then it doesn't really harm anything. But I wasn't sure if there was any side effects. The reason I posted that as a question was that I honestly forgot to add transactionScope.Complete in my code and yet the stuff actually worked fine as I didn't really make any changes to the DB. But was wondering if there was any undesired effect on the db because of me not closing the scope. – Garber

@EakanGopalakrishnan OK, I understand. I'd recommend asking that question over on dba.stackexchange.com. – Hummel

@DoctorJones Do you really need ` transactionScope.Complete();`? I'm used to most DB related using scopes doing that sort of thing auto-magically. – Calaverite

@Calaverite yes. The documentation says specifically that you have to: Failing to call this method aborts the transaction, because the transaction manager interprets this as a system failure, or exceptions thrown within the scope of transaction. However, you should also note that calling this method does not guarantee a commit of the transaction. It is merely a way of informing the transaction manager of your status. See msdn.microsoft.com/en-us/library/… – Hummel

This is extremely slow. Performance degraded immediately using this, need to give further investigation, but cannot use it – Jennine

this results in NotSupportedException: Enlisting in Ambient transactions is not supported. – Hilburn

@Hilburn this is a known issue in .Net Core 2.0 github.com/dotnet/corefx/issues/24282 – Hummel

@DoctorJones so read uncommitted cannot be done in .Net core 2? – Hilburn

@Hilburn it has been added in this pull request, which is for milestone 2.1.0 – Hummel

Answer 2

Extension methods can make this easier

public static List<T> ToListReadUncommitted<T>(this IQueryable<T> query)
{
    using (var scope = new TransactionScope(
        TransactionScopeOption.Required, 
        new TransactionOptions() { 
            IsolationLevel = System.Transactions.IsolationLevel.ReadUncommitted }))
    {
        List<T> toReturn = query.ToList();
        scope.Complete();
        return toReturn;
    }
}

public static int CountReadUncommitted<T>(this IQueryable<T> query)
{
    using (var scope = new TransactionScope(
        TransactionScopeOption.Required, 
        new TransactionOptions() { 
            IsolationLevel = System.Transactions.IsolationLevel.ReadUncommitted }))
    {
        int toReturn = query.Count();
        scope.Complete();
        return toReturn;
    }
}

Firman

Using this in my project results in connection pool being completely utilized resulting in exception. can't figure out why. Any one else having this issues? Any suggestions? – Ripp

No issues Ben, do not forget to ALWAYS dispose your connection context. – Firman

Was able to narrow down the issue to exclude the transaction scope as a possible cause. Thanks. Had something to do with some connection retry stuff I had in my constructor. – Ripp

I believe the scope should be TransactionScopeOption.Suppress – Underexposure

@Firman What would happen if I did this within another ReadCommitted Transaction? For instance i spawned a transaction to start saving data but now i am querying more data and therefore spawining a ReadUncommitted Transaction within? Will calling this "Complete" also complete my outer transaction? Kindly advise :) – Cheung

Answer 3

If you need something at large, the best way we found which less intrusive than actually starting a transactionscope each time, is to simply set the default transaction isolation level on your connection after you've created your object context by running this simple command:

this.context.ExecuteStoreCommand("SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;");

http://msdn.microsoft.com/en-us/library/aa259216(v=sql.80).aspx

With this technique, we were able to create a simple EF provider that creates the context for us and actually runs this command each time for all of our context so that we're always in "read uncommitted" by default.

Abdul

Setting the transaction isolation level alone will not have any effect. You actually need to be running within a transaction for it to have any effect. The MSDN documentation for READ UNCOMMITTED states Transactions running at the READ UNCOMMITTED level do not issue shared locks. This implies that you must be running within a transaction to get the benefit. (taken from msdn.microsoft.com/en-gb/library/ms173763.aspx). Your approach may be less intrusive, but it won't achieve anything if you don't use a transaction. – Hummel

The MSDN documentation says: "Controls the locking and row versioning behavior of Transact-SQL statements issued by a connection to SQL Server." and "Specifies that statements can read rows that have been modified by other transactions but not yet committed." This statement I wrote affects EVERY SQL statements, it being inside a transaction or not. I don't like to contradict people online but you are clearly wrong on that one based on our use of this statement in a large production environment. Don't assume things, TRY THEM! – Abdul

I have tried them, we've got a high load environment where not performing queries within one of these transaction scopes (and a matching transaction) will result in a deadlock. My observations were made on a SQL 2005 server, so I don't know if the behaviour has changed since. I'd therefore recommend this; if you specify a read uncommitted isolation level but continue to experience deadlocks, try putting your queries within a transaction. If you don't experience deadlocks without creating a transaction, then fair enough. – Hummel

@DoctorJones - with regards to Microsoft SQL Server, all queries are inherently transactions. Specifying an explicit transaction is just a means of grouping 2 or more statements into the same transaction so that they can be considered an atomic unit of work. The SET TRANSACTION ISOLATION LEVEL... command affects a connection-level property and hence affects all SQL statements made from that point forward (for THAT connection), unless overridden by a query hint. This behavior has been around since at least SQL Server 2000, and likely before. – Overcheck

@DoctorJones - Check out: msdn.microsoft.com/en-us/library/ms173763.aspx . Here is a test. In SSMS, open a query (#1) and run: CREATE TABLE ##Test(Col1 INT); BEGIN TRAN; SELECT * FROM ##Test WITH (TABLOCK, XLOCK);. Open another query (#2) and run: SELECT * FROM ##Test;. The SELECT won't return as it is being blocked by the still open transaction in tab #1 that is using an exclusive lock. Cancel the SELECT in #2. Run SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED once in tab #2. Run just the SELECT again in tab #2 and it will come back. Be sure to run ROLLBACK in tab #1. – Overcheck

+1 As a final note on this, I suggested this as a possible solution to another question, along with the TransactionScope idea (both credited to their respective answers here). I listed this idea first as it was simpler and should have produced the desired effect. The user tried this idea and it worked. #26616035 – Overcheck

Answer 4

Though I absolutely agreed that using Read Uncommitted transaction isolation level is the best choice, but some time you forced to use NOLOCK hint by request of manager or client and no reasons against this accepted.

With Entity Framework 6 you can implement own DbCommandInterceptor like this:

public class NoLockInterceptor : DbCommandInterceptor
{
    private static readonly Regex _tableAliasRegex = 
        new Regex(@"(?<tableAlias>AS \[Extent\d+\](?! WITH \(NOLOCK\)))", 
            RegexOptions.Multiline | RegexOptions.IgnoreCase);

    [ThreadStatic]
    public static bool SuppressNoLock;

    public override void ScalarExecuting(DbCommand command, 
        DbCommandInterceptionContext<object> interceptionContext)
    {
        if (!SuppressNoLock)
        {
            command.CommandText = 
                _tableAliasRegex.Replace(command.CommandText, "${tableAlias} WITH (NOLOCK)");
        }
    }

    public override void ReaderExecuting(DbCommand command, DbCommandInterceptionContext<DbDataReader> interceptionContext)
    {
        if (!SuppressNoLock)
        {
            command.CommandText = 
                _tableAliasRegex.Replace(command.CommandText, "${tableAlias} WITH (NOLOCK)");
        }
    }
}

With this class in place, you can apply it on application start:

DbInterception.Add(new NoLockInterceptor());

And conditionally turn off adding of NOLOCK hint into queries for current thread:

NoLockInterceptor.SuppressNoLock = true;

Dispirit

I like this solution although I altered the regex slightly to: – Carom

(?<tableAlias>] AS [Extent\d+](?! WITH (NOLOCK))) to prevent adding nolock to derived table which causes an error.:) – Carom

Setting SuppressNoLock at the thread level is a convenient way, but it's easy to forget to unset the boolean, you should use a function that returns IDisposable, the Dispose method can just set the bool to false again. Also, ThreadStatic is not really compatible with async/await: #13011063 – Dukedom

Or, if you would rather use ISOLATION LEVEL: public override void ReaderExecuting(DbCommand command, DbCommandInterceptionContext<DbDataReader> interceptionContext) { if (!SuppressNoLock) command.CommandText = $"SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;{Environment.NewLine}{command.CommandText}"; base.ReaderExecuting(command, interceptionContext); } – Syntax

It is appending nolock to database functions as well. How to avoid for functions? – Rallentando

This is a great solution, thank you! – Importunate

Answer 5

Enhancing on Doctor Jones's accepted answer and using PostSharp;

First "ReadUncommitedTransactionScopeAttribute"

[Serializable]
public class ReadUncommitedTransactionScopeAttribute : MethodInterceptionAspect
{
    public override void OnInvoke(MethodInterceptionArgs args)
    {
        //declare the transaction options
        var transactionOptions = new TransactionOptions();
        //set it to read uncommited
        transactionOptions.IsolationLevel = IsolationLevel.ReadUncommitted;
        //create the transaction scope, passing our options in
        using (var transactionScope = new TransactionScope(TransactionScopeOption.Required, transactionOptions))
        {
            //declare our context
            using (var scope = new TransactionScope())
            {
                args.Proceed();
                scope.Complete();
            }
        }
    }
}

Then whenever you need it,

    [ReadUncommitedTransactionScope()]
    public static SomeEntities[] GetSomeEntities()
    {
        using (var context = new MyEntityConnection())
        {
            //any reads we do here will also read uncomitted data
            //...
            //...

        }
    }

Being Able to add "NOLOCK" with an interceptor is also nice but will not work when connecting to other database systems like Oracle as such.

Ozellaozen

Answer 6

To get round this I create a view on the database and apply NOLOCK on the view's query. I then treat the view as a table within EF.

Oesophagus

Answer 7

With the introduction of EF6, Microsoft recommends using BeginTransaction() method.

You can use BeginTransaction instead of TransactionScope in EF6+ and EF Core

using (var ctx = new ContractDbContext())
using (var transaction = ctx.Database.BeginTransaction(System.Data.IsolationLevel.ReadUncommitted))
{
    //any reads we do here will also read uncommitted data
}

Gaudette

Answer 8

No, not really - Entity Framework is basically a fairly strict layer above your actual database. Your queries are formulated in ESQL - Entity SQL - which is first of all targeted towards your entity model, and since EF supports multiple database backends, you can't really send "native" SQL directly to your backend.

The NOLOCK query hint is a SQL Server specific thing and won't work on any of the other supported databases (unless they've also implemented the same hint - which I strongly doubt).

Marc

Helping

This answer is out of date - you can use NOLOCK as others have mentioned, and you can execute "native" SQL using Database.ExecuteSqlCommand() or DbSet<T>.SqlQuery(). – Demogorgon

@Dunc: thanks for the downvote - btw: you should NOT use (NOLOCK) anyway - see Bad Habits to kick - putting NOLOCK everywhere - it is NOT RECOMMENDED to use this everywhere - quite the contrary! – Helping

Answer 9

One option is to use a stored procedure (similar to the view solution proposed by Ryan) and then execute the stored procedure from EF. This way the stored procedure performs the dirty read while EF just pipes the results.

Dvinsk

Answer 10

Necro-ing this thread because it is the first that comes up in a google search. There have been changes to how EF structures its queries. This regex will also cover joins.

as an aside, transaction level read uncommitted works (examples are above with transactionscope), however it will still block updates. Using the table level nolock will allow for updates to continue. Be very aware and do some research on the effects of using dirty reads before doing this

(?<tableAlias>((FROM)|(JOIN))\s\[([^\s]+)\]\sAS\s\[([^\s]+)\](?!\sWITH\s\(NOLOCK\)))

you can test it with any regex online tester (like regex101.com) with this sample

FROM [table1] AS [t]
INNER JOIN [table2] AS [t2] ON ([t].[ID] = [t2].[ID])
INNER JOIN [table3] AS [t3] WITH (NOLOCK) ON ([t].[ID] = [t3].[ID])

I also cleaned up the example but left out the bool flag for trigger. feel free to add if you want

public class NoLockInterceptor : DbCommandInterceptor
    {       

        public override InterceptionResult<DbDataReader> ReaderExecuting(DbCommand command, CommandEventData eventData, InterceptionResult<DbDataReader> result)
        {           
            
            var finishedresult = base.ReaderExecuting(command.NoLockCommand(), eventData, result);    
            return finishedresult;  
        }

        public override ValueTask<InterceptionResult<DbDataReader>> ReaderExecutingAsync(DbCommand command, CommandEventData eventData, InterceptionResult<DbDataReader> result, CancellationToken cancellationToken = default)
        {
            
            var finishedresult = base.ReaderExecutingAsync(command.NoLockCommand(), eventData, result, cancellationToken);
            return finishedresult; 
        }

        public override InterceptionResult<object> ScalarExecuting(DbCommand command, CommandEventData eventData, InterceptionResult<object> result)
        {
            var finishedresult = base.ScalarExecuting(command.NoLockCommand(), eventData, result);
            return finishedresult;
        }

        public override ValueTask<InterceptionResult<object>> ScalarExecutingAsync(DbCommand command, CommandEventData eventData, InterceptionResult<object> result, CancellationToken cancellationToken = default)
        {
            var finishedresult = base.ScalarExecutingAsync(command.NoLockCommand(), eventData, result, cancellationToken);
            return finishedresult;
        }

        
    } 

    public static class DbCommandExtensions
    {
        private static Regex _tableAliasRegex = new Regex(@"(?<tableAlias>((FROM)|(JOIN))\s\[([^\s]+)\]\sAS\s\[([^\s]+)\](?!\sWITH\s\(NOLOCK\)))",
                RegexOptions.Multiline | RegexOptions.IgnoreCase);

        public static DbCommand NoLockCommand(this DbCommand command)        {          

            string updatedCommandText = _tableAliasRegex.Replace(command.CommandText, "${tableAlias} WITH (NOLOCK)");

            command.CommandText = updatedCommandText;
            return command;
        }
    }

Invest