Geeks With Blogs

News Stack Overflow It's the reason my blog posts have trickled off. Blame it!


Lessons Learned Preserved for Posterity
Normally I very much agree with the things Jeff Atwood has to say. In the case of this post, however, I must take issue. What begins with a decent intro into the concept of deadlocks eventually ends with Jeff deciding to use the NOLOCK table hint to allow dirty reads of a table that had been locked by an update statement. He concludes with a complaint that Microsoft seems to think that Stack Overflow is a banking institution and therefore worthy of data integrity instead of the mere programmers blog/wiki/link exchange that it really is.

Jeff wildly misses the mark with his conclusion, in my opinion.

Not only is NOLOCK deprecated, Jeff seems to believe that data integrity is something one can safely do without "in certain scenarios". He is wrong. Every database not only deserves but requires data integrity.

Of the dozens of posts that replied to Jeff's article, a few diagnosed the real issue (long updating transactions) but only one (at least as of the time of this post) supplied the correct solution (in my opinion) to Jeff's problem. The poster's name was Filip and his blog is worth adding to your feed reader. The solution Filip pointed out is simple: Lock all your resources *before* you update them and put a waiting lock on them (HOLDLOCK) so that queries that want to use the resource wait until the update is done rather than dying petulantly.

UPDATE:

Sam Saffron (also another great addition to your feed reader) has posted an excellent rebuttal of Jeff's post explaining how and why deadlocks occur when selecting against an update and how to deal with them. Read it and remember it. Posted on Thursday, August 28, 2008 9:36 AM Lessons Learned | Back to top

Related Posts on Geeks With Blogs Matching Categories

Comments on this post: Don't Use NOLOCK, Use HOLDLOCK

# re: Don't Use NOLOCK, Use HOLDLOCK
Requesting Gravatar...
Thank you for your nice comment.
Left by Filip on Jun 12, 2009 11:39 AM

# re: Don't Use NOLOCK, Use HOLDLOCK
Requesting Gravatar...
My pleasure. Thank you for a good blog!
Left by Randolpho on Oct 05, 2009 8:43 AM

# re: Don't Use NOLOCK, Use HOLDLOCK
Requesting Gravatar...
I think that your assumption that NOLOCK is deprecated is only partly correct.

The deprecated features for SQL 2008 explain that the use of NOLOCK as part of the FROM clause for UPDATE and DELETE statements is what is being deprecated.

http://msdn.microsoft.com/en-us/library/ms143729.aspx
Left by rbs on Feb 05, 2010 12:23 PM

# re: Don't Use NOLOCK, Use HOLDLOCK
Requesting Gravatar...
The surface isn't slippery and you can easily clean too. The rubberized grass matting is actually safe to become used along with areas which have kids. RUBBER GRASS MATTING
Left by jassica on Apr 30, 2015 5:07 PM

Your comment:
 (will show your gravatar)


Copyright © Randolpho St. John | Powered by: GeeksWithBlogs.net