Using NOLOCK Hint in EF4?

Question

We're evaluating EF4 and my DBA says we must use the NOLOCK hint in all our SELECT statements. So I'm looking into how to make this happen when using EF4.

I've read the different ideas on how to make this happen in EF4, but all seem like a work around and not sanctioned by Microsoft or EF4. What is the "official Microsoft" response to someone who wants their SELECT statement(s) to include the NOLOCK hint when using LINQ-to-SQL / LINQ-to-Entities and EF4?

By the way, the absolute best information I have found was right here and I encourage everyone interested in this topic to read this thread.

Thanks.

Typecase

I agree with the advice in the thread you link: Use SNAPSHOT/READ_COMMITTED, not NOLOCK. I consider NOLOCK mostly obsolete -- not that it was ever a good idea, as it makes your transactions non-ACID. See also #1453496 Also, this article summarizes the advantages and disadvantages sqlmag.com/Articles/ArticleID/93075/93075.html?Ad=1 – Cunning

Answer 1

NOLOCK = "READ UNCOMMITTED" = dirty reads

I'd assume MS knows why they chose the default isolation level as "READ COMMITTED"

NOLOCK, in fact any hint, should be used very judiciously: not by default.

Your DBA is a muppet. See this (SO): What can happen as a result of using (nolock) on every SELECT in SQL Sever?. If you happen to work at a bank, or any institution where I may have an account please let me know so I can close it.

Stulin

+1 I was about to post some nasty comments about the dba myself, bu I think 'muppet' summs it up just fine :) – Consumedly

I think you are a muppet if you want to do every select in the database with a lock. I am 100% for locking tables that requires transactional consistency, but if I have a status table, or a user table, any table that is more static than dynamic, why do I want to prevent others from reading it while I am reading it? And yes, a read/select places a lock on the table if you did not know that. So even though READ COMMITTED is the default, and I 100% agree with that, there are the occasions and situations that warrants for a nolock, and they are not that uncommon. Not all queries, but some at least – Lebna

@Ryk: Does it make any difference if you don't block other readers? Truly static tables would be better on a read only filegroup. For non-static tables, you could read data that could be changing eventually. It only takes one dirty read that gives an incorrect query result that could have massive consequences. But hey, you obviously don't work on systems where accuracy is paramount – Stulin

@Ryk; Please read the question again. gbn suggests that that DBA is a muppet because that "DBA says we must use the NOLOCK hint in all our SELECT statements". He (gbn) also advises that any hint (NOLOCK included) should be used judiciously. Not never. Neither to do every select in the database with a lock. – Darwindarwinian

@Lebna Also feel free to study gbn's contributions so you can assess what he knows and what not. (Read: no need to insult.) – Odontoid

@Stulin Just read past my first 5 words and you will see that I was not insulting you, but the statement muppet referred to stereotypes of accepting that the default of "READ COMMITTED" is the only true solution, and you cannot deviate from it. Of course the default will work for 98% of people out there, but I am working on databases that does in the order of 90K transactions p/s, and if you optimistically block other readers, the result can be processing done in 2 days vs. 2 hours. So NO insult meant, or aimed at gbn, sorry if it came out wrong – Lebna

Answer 2

I'm a developer on a tools team in the SQL org at Microsoft. I'm in no way authorized to make any official statement, and I'm sure there are people on SO who know more about these things than I do. Nevertheless, I'll offer a friendly rule of thumb, along the theme of "Premature optimization is the root of all evil":

Don't use NOLOCK (or any other query hint for that matter), until you have to. If you have a select statement which has a decent query plan, and it runs fine when there is very little other load on the system, but then it slows down when other queries are accessing the same table, try adding some NOLOCK hints. But always understand that when you do, you run the risk of getting inconsistent data. If you are writing some mission critical app that does online banking or controls an aircraft, this may be unacceptable. However, for many applications the perf speedup is worth the risk. Evaluate on a case-by-case basis, though. Don't just use them willy nilly all over the place.

If you do choose to use NOLOCK, I have blogged a solution in C# using extension methods, so that you can easily change a LINQ query to use NOLOCK hints. If you can adapt this to EF4, please post your adaptation.

Sideline

Answer 3

EF4 does not currently have a built in way to do it IF ef4 is generating all your queries.

There are ways around this such as using stored procedures or a more extended inline query model, however, this can be time consuming to say the least.

I believe (and I don't speak for Microsoft on this) that caching is Microsoft's intended solution for lightening the load on the server in EF4 sites. Having read uncommitted (or nolock) built into a framework would create unpredictable issues for the expected behaviour of EF4 when 2 contexts are run at the same time. That doesn't mean your situation needs that level of concurrency.

It sounds like you were asked for nolock on ALL selects. While I agree with earlier poster that this can be dangerous if you have ANY transactions that need to be transactions, I don't agree that automatically makes the DBA a muppet. You might just be running a CMS which is totally cool for dirty reads. You can change the ISOLATION LEVEL on your whole database which can have the same effect.

The DBA may have recommended nolock for operations that were ONLY selects (which is fine, especially if there's an ORM being misuesd and doing some dodgy data dumps). The funniest thing about that muppet comment is that Stack Overflow itself runs SQL server in a READ UNCOMMITTED mode. Guess you need to find somewhere else to get answers for your problems then?

Talk to your DBA about the posibility of setting this on a database level or consider a caching strategy if you only need it in a few places. The web is stateless after all so concurrency can often be an illusion anyway unless you address it direclty.

Info about isolation levels

Escapement

Answer 4

Having worked with EF4 for over a year now, I will offer that using stored procedures for specific tasks is not a hack and absolutely necessary for performance under certain situations.

Our platform gets a lot of traffic through our web site, APIs and ETL data feeds. We use EF primarily on our web side, but also for some back-end processes. Sometimes EF does a great job with its query generation, sometimes it is terrible. You need to look at the queries being generated, load them into query analyzer, and decide whether you might be better off writing the operation in another way (stored procedure, etc.).

If you find that you need to make data available via EF and need NOLOCKs, you can always create views with the NOLOCK hints included, and expose the view to EF instead of the underlying table. The same can be done with Stored Procedures. These methods are probably a bit easier when you are using the Code First approach.

But I think that one mistake a lot of people make with EF is believing that the EF object model has to map directly to the physical (table) model in the database. It doesn't and this is where your DBA comes into play. Let him design your physical model and you work together to abstract your logical data model which is mapped to your object model in EF.

Planetary

Answer 5

Although this would be a major PITA to do, you can always drop your SQL in a stored procedure and get the functionality you need (or are forced into). It's definitely a hack though!

Tomokotomorrow

Answer 6

I know this isn't an answer to your question, but I just wanted to throw this in.

It seems to me that this is (at least partially) the DBA's job. It's fine to say that an application should behave a certain way, and you can and should certainly attempt to program it the way that he would like.

The only way to be sure though, is for the DBA to work on the application with you and construct the DB surface that he would like to present to the app. If he wants critical tables to be queried as READ UNCOMMITTED, then he should help to provide a set of stored procedures with the correct access and isolation level.

Relying on the application code to construct every ad-hoc query correctly is not a scalable approach.

Carley