views:

3264

answers:

4

Hi folks,

I'm in the business of making website and applicatins that are not mission critical -> eg. banking software, space flight, intensive care monitoring application, etc. You get the idea.

So, with that massive disclaimer, is it bad using the NOLOCK hint in some Sql statement? A number of years ago, it was suggested by a fellow Sql Administrator that I should use NOLOCK if I'm happy with a dirty read .. which will give me a bit more performance out of my system because each read doesn't lock the table/row/whatever.

I was also told that it's a great solution if I'm experiencing dead-locks (cringe). So, I started following that thought for a few years until a Sql guru was helping me with some random code and noticed all the NOLOCKS in my sql code. I was politely scolded and he tried to explain it to me (why it's not a good thing) and I sorta got lost :( I felt that the essence of his explanation was 'it's a band-aid solution to a more serious problem .. especially if you're experiencing deadlocking. As such, fix the root of the problem`.

I did some googling recently about it and came across this post.

So, can some sql db guru sensei's please enlighten me? I be nice .. i'm blond :( so take it slowly, kind sir/madam.

cheers :)

+1  A: 

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.

Mitch Wheat
Hmm.. I still don't get it. So it's fine, but it's also poor form .. is that what you're saying?
Pure.Krome
On the assumption that you NEVER care about dirty reads, then it won't hurt. BUT it is usually a case of treating the symptom and not the cause...
Mitch Wheat
would the downvoter please leave a comment. THanks.
Mitch Wheat
Well I do not think its fair rexem was just downvoted, I think you did not address the arbitrary errors that just float up when you use nolock. Its not fine to get a blank error page once in a while on your website, it is really poor form. I dislike the assertion that "if you dont care about dirty reads its fine" ... it is not fine, even if you dont care about dirty reads
Sam Saffron
The blank page you get when a query generates an exception which you didn't implement retry logic for. What happens on your sites when query execution results with an exception, do you have retry logic everywhere?
Sam Saffron
@Sam Saffron: not sure I appreciate your inference there.
Mitch Wheat
+1  A: 

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 SHAPSHOT under most circumstances.

OMG Ponies
Rex, please feel free to add a note about snapshot isolation.
Sam Saffron
Yeah, Sam is saying Snapshot isolation and you're suggesting Read Committed Snapshot. i'm getting so confused :P (and i've yet to delve into the articles, also!)
Pure.Krome
+5  A: 

Prior to working on Stack Overflow I was against NOLOCKS 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 INSERTs / UPDATEs 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 than you may 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.

Geoff Dalgas
IMO, This is a bit simplistic. Deadlocks can be removed by using covering indexes, taking the pressure off the Clustered index.
Mitch Wheat
I do not wish to diminish the importance of good index coverage. There are times that queries using NOLOCK can add additional performance on top of gains realized by indexes on tables with a high number of inserts / updates. Query speed on Stack Overflow is paramount even at the cost of inaccurate or missing data.
Geoff Dalgas
Thanks heaps Geoff for the answer. I need to digest it but i've been hanging for some answers about how SO delt with their deadlocking issues from the early days. I never read any 'results' of their research or problem solving, for it.
Pure.Krome
http://www.codinghorror.com/blog/2008/08/deadlocked.html
Anton
Can you estimate the performance gain? There a no statistics or sample data.
Jenea
A: 

None of the answers is wrong, however a little confusing maybe.

  • When querying single values/rows it's always bad practise to use NOLOCK -- you probably never want to display incorrect information or maybe even take any action on incorrect data.
  • When displaying rough statistical information, NOLOCK can be very useful. Take SO as an example: It would be nonsense to take locks to read the exact number of views of a question, or the exact number of questions for a tag. Nobody cares if you incorrectly state 3360 questions tagged with "sql-server" now, and because of a transaction rollback, 3359 questions one second later.
markus

related questions