Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I am a Report Developer who wants to make my queries as efficient as possible. I used to work with a DBA who told me - I believe because I was always dealing with reports on a Production Server - to use NOLOCK in every single query.

Now, I work with a DBA who has banned NOLOCK under any circumstance - even when a report of mine (due to a considerable lack of indexes on a couple of tables) is stopping replication and system updates. In my opinion, in this case, a NOLOCK would be a good thing.

Since most of my SQL training has come various DBAs with very different opinions, I wanted to ask this to a wide variety of DBAs.

share|improve this question
1  
The other side of this discussion: dba.stackexchange.com/q/2684/2660 –  Nick Chammas Jan 13 '12 at 18:10

4 Answers 4

up vote 16 down vote accepted

If your report blocks updates that your DBA is right: you should absolutely not use NOLOCK. The very fact that there are conflicts is a clear indication that if you would use dirty reads you would get incorrect reports.

In my opinion, there are always better alternatives than NOLOCK:

  • Are your production tables read only in effect and never get modified? Mark the database read only!
  • Table scans cause lock conflicts? Index the tables appropriately, the benefits are multiple.
  • Can't modify/don't know how to index appropriately? Use SNAPSHOT ISOLATION.
  • Can't change app to use snapshot? Turn on read committed snapshot!
  • You have measured the impact of row versioning and have evidence it impacts performance? You can't index the data? and you are OK with incorrect reports? Then at the very least do yourself a favor and use SET TRANSACTION ISOLATION LEVEL, not a query hint. It will be easier to later fix the isolation level instead of modifying every query.
share|improve this answer
5  
Be careful: turning on read committed snapshot may break some code. –  A-K Jan 14 '12 at 3:36

It isn't always bad.

Of course it allows you to read uncommitted values (that may be rolled back and hence never logically existed) as well as allowing phenomena such as reading values multiple times or not at all.

The only isolation levels that guarantee that you won't encounter any such anomalies are serializable/snapshot. Under repeatable read values can be missed if a row is moved (due to a key update) before the scan reaches this row, under read committed values can be read twice if a key update causes a previously read row to move forward. These issues are more likely to arise under a nolock scan however.

But if your reporting application doesn't need absolutely precise figures and can tolerate the greater probability of such inconsistencies it might be OK.

share|improve this answer
3  
+1 - We use it a lot because our production tables never get modified. –  JNK Jan 13 '12 at 18:31
    
@JNK What do you mean by never gets modified? –  Bob Jan 14 '12 at 2:39
3  
Martin, I would suggest slightly different words: "under read committed values can be both missed and read more than once". We can have a row be retrieved more than twice in some exotic cases. –  A-K Jan 14 '12 at 3:06
    
@StarShip3000 The data we deploy to production is basically read-only for the end users, so most of their views have NOLOCK hints –  JNK Jan 14 '12 at 3:47

Do your customers tolerate inconsistent results in reports? If the answer is no, you should not use NOLOCK - you can get wrong results under concurrency. I wrote a few examples here, here, and here. These examples show inconsistent output under READ COMMITTED and REPEATABLE READ, but you can tweak them and get wrong results with NOLOCK as well.

share|improve this answer
    
Most of the reports I create aren't run on current data. Most customer's are running reports are yesterday's data. Would your answer change if that was the case? –  DataGirl Jan 15 '12 at 4:26

Most of the reports I create aren't run on current data. Most customer's are running reports are yesterday's data. Would your answer change if that was the case?

If that's the case, then you have one more possible option:
Instead of running your queries on the production database and messing around with locks and NOLOCK, you could run your reports from a copy of the production database.

You can set it up so it's automatically restored from a backup each night.
Apparently your reports are running on servers on customer's sites, so I don't know if setting this up would be a viable solution for you.
(but then again...they should have backups anyway, so all you need is some server space to restore them)

I'm an in-house developer, so this is easier for me because I have full control over the servers and databases.

You can do this at least for the reports that only need data from yesterday and older. Maybe some reports will have to stay on the production database, but at least you move some of the load to another database (or even better, another server).

I have the same situation at work as well:
We are using a production database copy like this for nearly all reporting stuff, but there are a few queries that require today's data.

share|improve this answer
    
I like your answer and it would work - if I had full control - which I didn't. Often times, I don't have full control and I can't create indexes. I'm lucky if I can run/display Execution plans. –  DataGirl Mar 19 '12 at 19:21

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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