2

Can anyone suggest a good reason to issue a NOLOCK on a SELECT statement?

I am re-factoring some stored procedures that are littered with them and it is my understanding that a NOLOCK on a SELECT statement is all but useless.

rstuppi
  • 361
  • 1
  • 3
  • 11

4 Answers4

6

I see some bad info flying around already. WITH(NOLOCK) on a select is not a performance tool to be used wherever you need speed. Read this article it dumps on the answer provided by eSamuel because that IS what most people think, as you can see by the votes. People who don't know but regurgitate what they've heard as how it should be done. Learn to optimize your data and queries first. The NOLOCK hint should only be used in systems where you are consistently dealing with records being deadlocked or another issue that NOLOCK provides a solution for.

I work at a financial institution developing in an OLTP system which processes numerous transactions a minute and provides real-time reporting capabilities to clients and customers. These people perform reads on our fresh data all day long and if we don't do report queries using NOLOCK it's only a matter of time before a deadlock occurs.

Do your due diligence and read from reputable, professional sources before jumping on NOLOCK as the holy graile to SQL query speed because there's much more to it.

Just Aguy
  • 327
  • 1
  • 7
5

So the SELECT isn't blocked by concurrent data modification queries.

Some people believe it is a magic go faster button and apply it liberally. It can be worse than useless unless your use case is accepting of the possibility of reading dirty (uncommitted) data that may be rolled back after you have read it (and hence never logically existed) and the greater possibility of certain types of anomaly.

Compared to read committed isolation level nolock will give you a greater probability of your scan missing data, reading it twice or completely failing with a data movement error.

Martin Smith
  • 438,706
  • 87
  • 741
  • 845
1

NOLOCK is equivalent to READ-UNCOMMITTED on the transaction.

In short, using NOLOCK ensures your SELECT statements are fast because it doesn't have to wait for existing locks or transactions to complete before returning the results of your query. The down side is that you can end up pulling back "dirty" data - things that might have been rolled back before being committed but after your SELECT statement was run.

Additional information can be found in Using NOLOCK and READPAST table hints in SQL Server.

eSamuel
  • 109
  • 5
  • Good catch Martin, you're right. Answer updated. – eSamuel Mar 08 '13 at 23:16
  • 2
    Sorry bud, -1 for you. Don't promote NOLOCK as ensuring fast queries because it doesn't in every scenario and it should only be used in circumstances where NOLOCK is the only solution you have to work with. – Just Aguy Mar 08 '13 at 23:50
0

NoLocks are especially useful if you are reading from lookup tables where the content rarely changes.

The problem with nolock is,of course, that you may potentially get bad data if the table is being updated. There are some scenarios for tables that are constantly updated where nolock can be used correctly. For instance, you have the table:

UserConfig { id int PRIMARY KEY, is_active_flag bit, allow_x_feature bit, allow_y_feature bit }

If you are running select queries and filtering by id, then you want to use nolock even if this table is frequently updated.

cclusetti
  • 343
  • 1
  • 2
  • 11
  • If the table isn't being updated then SQL Server can avoid taking shared row locks on it anyway so not sure how much benefit this would actually have. (Ref: [The Case of the Missing Shared Locks](http://sqlblog.com/blogs/paul_white/archive/2010/11/01/read-committed-shared-locks-and-rollbacks.aspx)) It might be better to put such unchanging data in a read only file group. – Martin Smith Mar 08 '13 at 23:18