Get Entity Framework 6 use NOLOCK in its underneath SELECT statements

Question

I am using Entity Framework 6 in an MVC 5 project. As you're aware of, SELECT queries in SQL Server perform faster and more efficient if we use WITH (NOLOCK) in them. I checked out a few SQL SELECT statements generated by Entity Framework 6 and realized that none of them contain NOLOCK.

I do not want to use transactions in my fetch operations to read from uncommitted transactions.

How can I enforce EF 6 to use NOLOCK in the underneath generated SELECT statements?

Solution 1

First of all... You should NEVER EVER use NOLOCK for each and every SQL Statement. It could compromise the integrity of your data.

It’s like any other query hint a mechanism you should only use when you do something out of the ordinary.

There is no way to tell the EF Provider to render the NoLock hint. If you really need to read uncommitted data you have the following option.

1. Write your own EntityFramework Provider.

2. Use a Command Interceptor to modify the statement before it is executed. http://msdn.microsoft.com/en-us/data/dn469464.aspx

3. Use a TransactionScope with IsolationLevel.ReadUncommited.

I know you said you do not want to use Transactions but it's the only out-of-the box way to read uncommitted data. Also it does not produce much overhead as each statement in SQL Server “implicitly” runs in a transaction.

using (new TransactionScope(
                    TransactionScopeOption.Required, 
                    new TransactionOptions 
                    { 
                         IsolationLevel = IsolationLevel.ReadUncommitted 
                    })) 
{
        using (var db = new MyDbContext()) { 
            // query
        }
}

EDIT: It's important to note also that NOLOCK for Updates and Deletes (selects remain intact) has been Deprecated by Microsoft as of SQL Server 2016 and and will be removed in 'a' future release.

https://docs.microsoft.com/en-us/sql/database-engine/deprecated-database-engine-features-in-sql-server-2016?view=sql-server-2017

Solution 2

You can use a workaround that don't use transaction scopes for each query. If you run the code below, ef will use the same transaction isolation level for the same Server Process ID. Since Server Process ID does not change in same request, only one call for each request is sufficient. This also works in EF Core.

this.Database.ExecuteSqlCommand("SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;");

Solution 3

I agree with what codeworx says in a way that Read Uncommitted can be dangerous. If you can live with dirty reads, go for it.

I found a way to make this work without changing anything in the current queries.

You need to create a DbCommandInterceptor like this:

public class IsolationLevelInterceptor : DbCommandInterceptor
{
    private IsolationLevel _isolationLevel;

    public IsolationLevelInterceptor(IsolationLevel level)
    {
        _isolationLevel = level;
    }



    //[ThreadStatic]
    //private DbCommand _command;


    public override void ReaderExecuting(DbCommand command, DbCommandInterceptionContext<DbDataReader> interceptionContext)
    {
        SetTransaction(command);

    }

    public override void ScalarExecuting(DbCommand command, DbCommandInterceptionContext<object> interceptionContext)
    {
        SetTransaction(command);
    }

    public override void NonQueryExecuting(DbCommand command, DbCommandInterceptionContext<int> interceptionContext)
    {
        SetTransaction(command);
    }




    private void SetTransaction(DbCommand command)
    {
        if (command != null)
        {
            if (command.Transaction == null)
            {
                var t = command.Connection.BeginTransaction(_isolationLevel);
                command.Transaction = t;
                //_command = command;
            }
        }
    }

}

then at the cctor (static contructor of your dbcontext) just add the interceptor to the DbInfrastructure of entity framework collections.

DbInterception.Add(new IsolationLevelInterceptor(System.Data.IsolationLevel.ReadUncommitted));

this will for each command that EF sends to the store, wraps over a transaction with that Isolation Level.

In my case worked well, since we write data through an API where that data is not based on the readings from the database. (data can be corrupted because of the dirty reads) and so worked fine.

Solution 4

In our project we use combination of the second and third solutions, suggested by @Cem Mutlu and @anotherNeo.

Experiment with Sql Profiler showed that we have to use pair of commands:

• READ UNCOMMITTED
• READ COMMITTED

because NET reuse connections via SqlConnectionPool

internal class NoLockInterceptor : DbCommandInterceptor
{
    public static readonly string SET_READ_UNCOMMITED = "SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED";
    public static readonly string SET_READ_COMMITED = "SET TRANSACTION ISOLATION LEVEL READ COMMITTED";

    public override void ScalarExecuting(DbCommand command, DbCommandInterceptionContext<object> interceptionContext)
    {
        if (!interceptionContext.DbContexts.OfType<IFortisDataStoreNoLockContext>().Any())
        {
            return;
        }

        ExecutingBase(command);
    }

    public override void ReaderExecuting(DbCommand command, DbCommandInterceptionContext<DbDataReader> interceptionContext)
    {
        if (!interceptionContext.DbContexts.OfType<IFortisDataStoreNoLockContext>().Any())
        {
            return;
        }

        ExecutingBase(command);
    }

    private static void ExecutingBase(DbCommand command)
    {
        var text = command.CommandText;
        command.CommandText = $"{SET_READ_UNCOMMITED} {Environment.NewLine} {text} {Environment.NewLine} {SET_READ_COMMITED}";
    }
}

Solution 5

First off, please upvote gds03's answer. Because I would not have gotten this far without it.

I have contributed to "closing out the transaction" and getting the timing right for IDataReader/DbDataReader situations too. Basically, with IDataReader/DbDataReader situations, you do NOT close the transaction on the "ReaderExecuted"(Async) methods (emphasis on the "ed" of Executed), but let it "fall through" to (override of) DataReaderDisposing.

But if you read some of the other answers (here) (and comments), I think SetEndTransaction is an important part of .. not getting voodoo from the connection-pool (if you don't probably close out the transaction with the (for me) read-uncommitted).

using System.Data;
using System.Data.Common;
using System.Threading;
using System.Threading.Tasks;
using Microsoft.EntityFrameworkCore.Diagnostics;

namespace My.Interceptors
{
    public class IsolationLevelInterceptor : DbCommandInterceptor
    {
        private IsolationLevel _isolationLevel;

        public IsolationLevelInterceptor(IsolationLevel level)
        {
            _isolationLevel = level;
        }

        //[ThreadStatic]
        //private DbCommand _command;


        public override InterceptionResult DataReaderDisposing(DbCommand command, DataReaderDisposingEventData eventData, InterceptionResult result)
        {
            InterceptionResult returnItem = base.DataReaderDisposing(command, eventData, result);
            SetEndTransaction(command);
            return returnItem;
        }




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


        public override DbDataReader ReaderExecuted(DbCommand command, CommandExecutedEventData eventData, DbDataReader result)
        {
            DbDataReader returnItem = base.ReaderExecuted(command, eventData, result);
            //SetEndTransaction(command); // DO NOT DO THIS HERE .. datareader still open and working .. fall back on DataReaderDisposing... you don't really need this override, but left in to show the possible issue.
            return returnItem;
        }


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


        public override ValueTask<DbDataReader> ReaderExecutedAsync(DbCommand command, CommandExecutedEventData eventData, DbDataReader result, CancellationToken cancellationToken = default)
        {
            ValueTask<DbDataReader> returnItem = base.ReaderExecutedAsync(command, eventData, result, cancellationToken);
            //SetEndTransaction(command); // DO NOT DO THIS HERE .. datareader still open and working .. fall back on DataReaderDisposing... you don't really need this override, but left in to show the possible issue.
            return returnItem;
        }


        public override InterceptionResult<object> ScalarExecuting(DbCommand command, CommandEventData eventData, InterceptionResult<object> interceptionContext)
        {
            SetStartTransaction(command);
            InterceptionResult<object> returnItem = base.ScalarExecuting(command, eventData, interceptionContext);
            return returnItem;
        }

        public override object ScalarExecuted(DbCommand command, CommandExecutedEventData eventData, object result)
        {
            SetEndTransaction(command);
            object returnItem = base.ScalarExecuted(command, eventData, result);
            return returnItem;
        }


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


        public override ValueTask<object> ScalarExecutedAsync(DbCommand command, CommandExecutedEventData eventData, object result, CancellationToken cancellationToken = default)
        {
            SetEndTransaction(command);
            ValueTask<object> returnItem = base.ScalarExecutedAsync(command, eventData, result, cancellationToken);
            return returnItem;
        }


        /* start maybe not needed on queries that only do "reading", but listed here anyways */

        public override InterceptionResult<int> NonQueryExecuting(DbCommand command, CommandEventData eventData, InterceptionResult<int> result)
        {
            SetStartTransaction(command);
            InterceptionResult<int> returnItem = base.NonQueryExecuting(command, eventData, result);
            return returnItem;
        }

        public override int NonQueryExecuted(DbCommand command, CommandExecutedEventData eventData, int result)
        {
            int returnValue = base.NonQueryExecuted(command, eventData, result);
            SetEndTransaction(command);
            return returnValue;
        }

        public override ValueTask<InterceptionResult<int>> NonQueryExecutingAsync(DbCommand command, CommandEventData eventData, InterceptionResult<int> result, CancellationToken cancellationToken = default)
        {
            SetStartTransaction(command);
            ValueTask<InterceptionResult<int>> returnItem = base.NonQueryExecutingAsync(command, eventData, result, cancellationToken);
            return returnItem;
        }

        public override ValueTask<int> NonQueryExecutedAsync(DbCommand command, CommandExecutedEventData eventData, int result, CancellationToken cancellationToken = default)
        {
            ValueTask<int> returnValue = base.NonQueryExecutedAsync(command, eventData, result, cancellationToken);
            SetEndTransaction(command);
            return returnValue;
        }

        /* end maybe not needed on queries that only do "reading", but listed here anyways */

        private void SetStartTransaction(DbCommand command)
        {
            if (command != null)
            {
                if (command.Transaction == null)
                {
                    DbTransaction t = command.Connection.BeginTransaction(_isolationLevel);
                    command.Transaction = t;
                    //_command = command;
                }
            }
        }

        private void SetEndTransaction(DbCommand command)
        {
            if (command != null)
            {
                if (command.Transaction != null)
                {
                    command.Transaction.Commit();
                    //_command = command;
                }

                command.Dispose();
            }
        }

    }
}

This article was helpful in "seeing" all the "ing" and "ed" methods.

https://lizzy-gallagher.github.io/query-interception-entity-framework/

Please note my answer is EntityFrameworkCore (3.1.+), but I think it will "back port" to EF-for-DN-Framework.

The more important parts of my answer is the "timing" of certain methods...especially the IDataReader/DbDataReader methods.

View more solutions

Comments

[Arash]

Yes, I totally understand the possible consequences of using NOLOCK in SELECT statements. We are totally fine with using NOLOCK against a table retaining a huge chunk of data because the WHERE clause knows where to pick up data from the "already-committed" portion. That particular table is used to INSERT/UPDATE a very huge chunk of data in an ongoing transaction that might take several minutes to finish. However we'd like to use another area of the data table while the transaction is in progress. Our bottom line is that we do not want to lose speed of our application.

[Arash]

@ErikFunkenbusch that's not an option Erik. Thanks though.

[erikkallen]

@Arash If that is your problem, why don't you disable table-level (and page-level) locking instead of using NOLOCK? The effect should be the same if the read and the write don't touch the same data.

[Raghu A]

For "Option 3" mentioned above as solutions which is probably easier than other two has example in other quesiton: stackoverflow.com/questions/926656/entity-framework-with-nol‌​ock

[Roman Starkov]

Do note though that "read uncommitted" and "nolock" are more or less totally different things - in particular, the "read uncommitted" example in this answer can still take locks.

[Justin Fisher]

I had to change the SetTransaction to also check the connection for an existing transaction because EF can use the same connection for multiple commands. Used this example to get the transaction from the connection: stackoverflow.com/a/12284267/169060

[i486]

"...NEVER EVER..." and "any other query hint a mechanism you should only use when you do something out of the ordinary" - who says that he is not doing something out of the ordinary? Hints have to be used carefully and with understanding, but they are not evil.

[AXMIM]

There is a big risk involved with this solution if your version of SQL Server is older than Sql Server 2014 because this set the Isolation Level for the whole connection. Once done with the connection, it will return to the pool of connections and the next call that will grab this specific connection will execute with that isolation level as well, which can be bad. Sql Server 2014 and above reset the isolation level when returning the connection to the pool.

[jtate]

wouldn't this make it so every query is reading uncommitted rows? how can you make this work for only specific queries?

[Dmitry]

about "NEVER EVER". You should use it in SELECT statements when you read data which might be temporary locked by heavy operations like nightly updates etc. If you are not in the situation when exact data is very important (stock market trading currency value), and you just need data without waiting locks -- use it. It is normal practice.

[codeworx]

Since some people seem to have a problem with my usage of NEVER EVER in the answer, I would like to clarify what I exactly meant. I used this phrase in the same way as It is used in the following Sentence: You should NEVER EVER attempt to land a plane in the Hudson river, UNLESS both engines are down and the alternative is crashing into midtown Manhattan.

[granadaCoder]

axmim. thank you for this hint/reminder. i think my answer and "SetEndTransaction" addresses this. at the same time, I would have found this later-than-sooner without your comment.

[Mick]

A bit overly dramatic. I'd say don't attempt to create something that causes every read to be a dirty read. Sometimes you don't care if data is dirty or not. E.g when populating a drop down, it doesn't matter if every entry in the drop is valid, as long as you have validation on what's selected from the drop down. Even if you don't use NOLOCK to read such data, you'll still have concurrency issues with the data loaded becoming out dated in-between the load and use of the data.