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
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.
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.