How can I prove NOLOCK is the source of deadlocking problems?

Question

I'm not trying to start a windows/mac type discussion.

Personally, I don't need any convincing that NOLOCK is not a good idea as a reflexive practice. It seems when you're developing everything should be purposeful not reactionary (/amen)

So... programmer-in-charge insists NOLOCK is the way to go. Recommends with all ad-hoc queries and whenever querying production. I haven't seen a stored procedure without nolock hints on every table.

Don't want to be that guy who comes in and tells everyone a core belief is all wrong without something to back it up.

Just looking at the comment sessions under the various blog posts, sending a link may not be enough. Long-held beliefs etc... Some people aren't convinced it's a problem. See: Comments section under every nolock blog post I've read.

Currently some other DBAs are wrestling with some mysterious deadlocking. How does one determine whether NOLOCKs are the source?

It's been suggested looking at XML from traces etc, but this won't explicitly state that the deadlocks are causing the problem, will it? I've never seen error messages that straight forward. is that true?

How else could these deadlocks be pinned on this?

DDL statements like CREATE would be a clue. Is there any output that I could point to or some piece of data I could find that would help corroborate my theory before I raise the alarm?

Or am I running trace flags or extended events to identify what's running when the deadlocks occur and then deducing from DDL statements?

Looking at all the different ways data can get messed up with nolock hints, it seems like a hard problem to decisively pin down.

Answer 1

The architect-in-charge how insists NOLOCK is the way to go. Recommends with all ad-hoc queries and whenever querying production. I haven't seen a sproc without nolock hints on every table.

Sorry to hear that. This is pretty well universally regarded as an anti-pattern among expert SQL Server practitioners, but there may be nothing you can do to change the facts on the ground if the practice is truly ingrained, and based on one person's sincerely and deeply-held beliefs.

The question says that "sending a link may not be enough", but it's unclear what you want instead. We could write the most compelling argument in the world in an answer here, and you would still be left "sending a link" to it. Ultimately, no one here can know which set of arguments will be successful in your specific situation (if any).

Nevertheless, the following cover most points that some people find persuasive enough to change a previously common practice:

• Bad habits : Putting NOLOCK everywhere by Aaron Bertrand
• SQL Server NOLOCK Hint & other poor ideas By David Lean (Microsoft)
• The Read Uncommitted Isolation Level by me
• Justify NOT using the (nolock) hint in every query (related Q & A here)

Even so, you may not be able to 'win' this battle. I have worked in an environment that did this, understood the risks and reasons not to do it, but continued with it anyway. These were bright, logical people, but in the end it was an environment I could not be happy working in.

Currently some other DBAs are wrestling with some mysterious deadlocking. How does one determine that NOLOCKs are the source.

The more usual pattern (perhaps more prevalent in the past) in that NOLOCK hints are introduced in an attempt to reduce the incidence of deadlocks. This can 'work' in that reducing the number of shared locks taken naturally reduces the chances of incompatible locks being taken, but it is not a good solution to the underlying problem, and comes with all the caveats noted in the previous section.

Nevertheless, NOLOCK hints can introduce new ways to deadlock. Using read uncommitted isolation can remove a transient blocking condition (resolved when the contended resource becomes available) into an unresolvable deadlock (where neither waiter can make progress) simply because the contention now happens at a different point, where e.g. write operations in a different order overlap. Dave Ballantyne has an example here:

• Increasing deadlocks with NOLOCK

For more general advice about dealing with deadlocks, I recommend the following as a starting point:

• The Difficulty with Deadlocks by Jeremiah Peschka

You should also familiarise yourself with the documentation:

• Deadlocking (and all three sub-topics there)

Other useful resources:

• SQL Server Deadlocks by Example by Gail Shaw
• Deadlock troubleshooting by Bart Duncan