5
  1. If I have a table where the data is guaranteed to never change, is it completely safe to add the NOLOCK hint to my SELECT queries against that table?
  2. If I have a table where some of the rows will be updated, but I'm only selecting rows that are guaranteed not to change, is this also a completely safe scenario to use the NOLOCK hint?
  • what issue are you trying to solve ? NOLOCK allows to read uncommitted data. In your case, the data does not change... more it is readonly data. Its one of the bad habbits and a poor idea that you should avoid. – Kin Shah Nov 4 at 18:35
  • 2
    Obligatory HNQ meme – Peter Vandivier Nov 5 at 10:46
  • When you say "change", do you only mean updates, or inserts too? – GSerg Nov 5 at 12:41
  • In the first scenario I mean no updates, inserts, schema or index changes. In the second scenario the same but only applied to the data I'd be querying against. – J.D. Nov 5 at 13:28
  • See also here where NOLOCK queries can run slower in some circumstances. – Michael Green Nov 6 at 10:28
13

If I have a table where the data is guaranteed to never change, is it completely safe to add the NOLOCK hint to my SELECT queries against that table?

In practice, yes (although not documented as such), but also useless. If your data never changes, then using dirty reads won't cause any observable behavior or material performance difference.

If I have a table where some of the rows will be updated, but I'm only selecting rows that are guaranteed not to change, is this also a completely safe scenario to use the NOLOCK hint?

No. Absolutely not. The query plan to read the target rows may read changing data structures without proper concurrency controls. So you are back to hoping that your queries don't return bizarre, incorrect results or simply fail.

  • Is there any (even negligible) performance differences in using the NOLOCK hint in the first scenario? Also, in the second scenario when you say "changing data structures", are you referring to the underlying pages that the data is stored on (because it can be sharing a page with rows that are changing)? – J.D. Nov 4 at 18:49
  • 2
    In the first scenario you might see some cost for row locking if you're not using READ COMMITTED SNAPSHOT. But using TABLOCK instead of NOLOCK in that scenario would force a single S lock for the query. And the "changing data structures" might include the data pages and indexes. The lack of coordination between data page reads and index reads is a main cause of NOLOCK badness. – David Browne - Microsoft Nov 4 at 18:52
  • 1
    >>>but also useless. If your data never changes, then using dirty reads won't cause any observable behavior or material performance difference.<<< Cannot agree with this. Server can choose allocation order scan when see NOLOCK hint, this can be more efficient and this scan is not affected by logical fragmentation – sepupic Nov 5 at 8:43
  • 2
    Good point, but again TABLOCK enables allocation-order scans without dirty reads. – David Browne - Microsoft Nov 5 at 13:00
  • 1
    the cost for row locking should be mitigated if the data is unchanging too sql.kiwi/2010/11/read-committed-shared-locks-and-rollbacks.html – Martin Smith Nov 5 at 13:45

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.