Is the NOLOCK (Sql Server hint) bad practice?

Question

Prior to working on Stack Overflow, I was against NOLOCK on the principal that you could potentially perform a SELECT with NOLOCK and get back results with data that may be out of date or inconsistent. A factor to think about is how many records may be inserted/updated at the same time another process may be selecting data from the same table. If this happens a lot then there's a high probability of deadlocks unless you use a database mode such as READ COMMITED SNAPSHOT.

I have since changed my perspective on the use of NOLOCK after witnessing how it can improve SELECT performance as well as eliminate deadlocks on a massively loaded SQL Server. There are times that you may not care that your data isn't exactly 100% committed and you need results back quickly even though they may be out of date.

Ask yourself a question when thinking of using NOLOCK:

Does my query include a table that has a high number of INSERT/UPDATE commands and do I care if the data returned from a query may be missing these changes at a given moment?

If the answer is no, then use NOLOCK to improve performance.

---

I just performed a quick search for the NOLOCK keyword within the code base for Stack Overflow and found 138 instances, so we use it in quite a few places.

With NOLOCK hint, the transaction isolation level for the SELECT statement is READ UNCOMMITTED. This means that the query may see dirty and inconsistent data.

This is not a good idea to apply as a rule. Even if this dirty read behavior is OK for your mission critical web based application, a NOLOCK scan can cause 601 error which will terminate the query due to data movement as a result of lack of locking protection.

I suggest reading When Snapshot Isolation Helps and When It Hurts - the MSDN recommends using READ COMMITTED SNAPSHOT rather than SNAPSHOT under most circumstances.

If you don't care about dirty reads (i.e. in a predominately READ situation), then NOLOCK is fine.

BUT, be aware that the majority of locking problems are due to not having the 'correct' indexes for your query workload (assuming the hardware is up to the task).

And the guru's explanation was correct. It is usually a band-aid solution to a more serious problem.

Edit: I'm definitely not suggesting that NOLOCK should be used. I guess I should have made that obviously clear. (I would only ever use it, in extreme circumstances where I had analysed that it was OK). AS an example, a while back I worked on some TSQL that had been sprinkled with NOLOCK to try and alleviate locking problems. I removed them all, implemented the correct indexes, and ALL of the deadlocks went away.

Doubt it was a "guru" who'd had any experience in high traffic...

Websites are usually "dirty" by the time the person is viewing the completely loaded page. Consider a form that loads from the database and then saves the data that's edited?? It's idiotic the way people go on about dirty reads being such a no no.

That said, if you have a number of layers building on your selects, you could be building in a dangerous redundancy. If you're dealing in money or status scenarios, then you need not only transactional data read/writes, but a proper concurrency solution (something most "gurus" don't bother with).

On the other hand, if you have an advanced product search for a website (ie something that likely won't be cached and be a little intensive) and you've ever built a site with more than a few concurrent users (phenominal how many "experts" haven't), it is rediculous to bottle neck every other process behind it.

Know what it means and use it when appropriate. Your database will almost always be your main bottle neck these days and being smart about using NOLOCK can save you thousands in infrastructure.

EDIT: It's not just deadlocks it helps with, it's also how much you are going to make everybody else wait until you're finished, or vice versa.

Using NOLOCK Hint in EF4?