6

I have a question about the use of NOLOCK.

I understand that NOLOCK hint is not always the best approach, but in some case it is very helpful. I am not trying to build a bad habit of using it all the time

I just want to understand the exact behavior of it. Have this unrealistic assumtion that a process that is updating record where id = 10 UPDATE table1 SET status = 2 WHERE id = 10 which is taking 30 seconds to update. And at the same time I execute SELECT * FROM table1 WITH NOLOCK where id = 10

Will my select statement read the row even though my first query had an exclusive lock on the record or will my select query wait until no locks what so ever on the record before it allows the read?

I want to know if the use of NOLOCK could be causing delays or not.

Sanushi Salgado
  • 1,195
  • 1
  • 11
  • 18
Junior
  • 11,602
  • 27
  • 106
  • 212
  • Possible duplicate of [Effect of NOLOCK hint in SELECT statements](http://stackoverflow.com/questions/210171/effect-of-nolock-hint-in-select-statements) – DhruvJoshi Mar 04 '16 at 17:32
  • 3
    On the contrary, it will potentially run a little quicker because it doesn't have to be concerned with locks. And yes your select query will read that row...most of the time. It is possible it might miss it, or it might even get returned twice. Check out this article. http://blogs.sqlsentry.com/aaronbertrand/bad-habits-nolock-everywhere/ It is geared towards steering people away from using that hint but it also has a LOT of very valuable information. If think you need to read it so you understand what that hint really does. – Sean Lange Mar 04 '16 at 17:32
  • 3
    The `SELECT` might or might not read the row. This is called a dirty read, and such a row might be read zero, one, or two times. I noticed that Sean's comment references Aaron Bertrand's blog on this topic (which I was about to reference myself). I second encouraging you to read it. – Gordon Linoff Mar 04 '16 at 17:32
  • `select` may miss the row or may read it twice. It does not look like you realize what `nolock` is. I'd suggest you to stop using it no matter that you find it "in some cases helpful". – Ivan Starostin Mar 04 '16 at 17:34
  • Rather than `NOLOCK`, use `READ COMMITTED SNAPSHOT` or `SNAPSHOT` isolation levels. `SELECT` statements don't request locks when using those isolation levels, and they return consistent results. – Jesús López Mar 04 '16 at 17:55
  • You may find this of interest https://dba.stackexchange.com/questions/207422/why-does-nolock-make-a-scan-with-variable-assignment-slower – d219 Oct 14 '20 at 15:27

1 Answers1

4

The short answer to the question as stated is: "No."

In most cases the NOLOCK hint will speed up the query in question, as well as, any other queries operating against the specified table at the same time. The reason is that no locks are checked or obtained. You've listed the possible side effects in your question so I won't cover those here.

At the end of the day the query will be faster, but the results will be suspect.

Josh
  • 1,724
  • 13
  • 15
  • 3
    People always say stuff like "the results will be suspect" or "you can't trust the data returned from a NOLOCK query" etc. But the only time it would ever even be an issue is if someone was writing *RELEVANT* data at the same time as the query. In which case, if you'd run your query 10 seconds earlier, you'd get a different result to if you ran it 10 seconds later anyway, with or without NOLOCK. If your results have to be accurate to the point that one extra row of data will cause an issue, and you're reading from a table currently being written to, you're doing it wrong in the first place. – Geoff Griswald Aug 30 '19 at 16:10