Knowing what your doing with NOLOCK...

Tuesday, August 16, 2011

Knowing what your doing with NOLOCK...

MS SQL Tips - Understanding the SQL Server NOLOCK hint

"Problem

I see the use of the NOLOCK hint in existing code for my stored procedures and I am not exactly sure if this is helpful or not. It seems like this has been a practice that was put in place and now is throughout all of the code wherever there are SELECT statements. Can you explain the what NOLOCK does and whether this is a good practice or not?

Solution

It seems that in some SQL Server shops the use of the NOLOCK (aka READUNCOMMITED) hint is used throughout the application. In this tip we take a closer look at how this works and what the issues maybe when using NOLOCK.

Example

Let's walk through some simple examples to see how this works. (These queries are run against the AdventureWorks database.)

...

..."

My name is Greg and I'm a NOLOCK user... Yeah, NOLOCK is a hammer I use way to often. It's an old habit and one that I mean to break, but it's like finger nail biting, it's just so darn hard to stop... But I guess the first step to recovery is acknowledging the problem. :|

The main issue is that others see me using it and use it themselves without really understanding it. THAT's what keeps me up at night (well not really, but you get the idea...).

So the next time I get a "what's this NOLOCK thing" or see it used in a scary manner I can refer back to this article (or those below)...