To NOLOCK or not to NOLOCK?

Question

I've worked in a very large organization where they had to use NOLOCK on most queries - because data was commonly updated via ETL processes during the day and locking the application for 40% of the working day was certainly not an option.

Out of habit, in my next place I went on to automatically use NOLOCK everywhere. But since reading warnings and risks, I've been gradually undoing this, to have no table hints specified and let SQL Server do it's thing.

However, I'm still not comfortable I'm doing the right thing. In the place where we used NOLOCK, I never once saw data get doubled up, or corrupt data.. I was there for many years. Ever since removing NOLOCK I am running into the obvious obstacle of rowlocks slowing / pausing queries which gives the illusion that my DB is slow or flakey. When in actuality it's just somebody running a lengthy save somewhere (the ability for them to do so is a requirement of the application).

I would be interested to hear from anyone who has actually experienced data corruption or data duplication from NOLOCK in practise, rather than people who are going by what they've read about it on the internet. I would be especially appreciative if anybody can provide replication steps to see this happen. I am trying to gauge just how risky is it, and do the risks outweigh the obvious benefit of being able to run reports parallel to updates?

Answer 1

I've seen corruptions, duplication and bad results with NOLOCK. Not once, not seldom. Every deployment that relied on NOLOCK and I had a chance to look at had correctness issues. True that many (most?) were not aware and did not realize, but the problems were there, always.

You have to realize that NOLOCK problems do no manifest as hard corruptions (the kind DBCC CHECKDB would report), but 'soft' corruption. And the problems are obvious only on certain kind of workloads, mostly on analytic types (aggregates). They would manifest as an incorrect value in a report, a balance mismatch in a ledger, a wrong department headcount and similar. These problems are visible as problems only when carefully inspected by a qualified human. And they may well vanish mysteriously on a simple Refresh of the page. So you may well have all these problems and not be aware of them. Your users might accept that 'sometimes the balance is wrong, just ask for the report again and will be OK' and never report you the issue.

And there are some workloads that are not very sensitive to NOLOCK issues. If you display 'posts' and 'comments' you won't see much of NOLOCK issues. Maybe the 'unanswered count' is off by 2, but who will notice?

Ever since removing NOLOCK I am running into the obvious obstacle of rowlocks slowing / pausing queries

I would recommend evaluating SNPASHOT isolation models (including READ_COMMITTED_SNAPSHOT). You may get a free lunch.

Answer 2

I see you've read a lot about it, but allow me to point you to a very good explanation on the dangers of using NOLOCK (that's it READ UNCOMMITTED isolation level): SQL Server NOLOCK Hint & other poor ideas.

Apart from this, I'll make some citations and comments. The worst part of NOLOCK is this:

It creates “incredibly hard to reproduce” bugs.

The problem is that when you read uncommited data, most of the time is commited, so everything is alright. But it will randomly fail if the transaction is not comitted. And that doesn't usually happen. Right? Nope: first, a single error is a very bad thing (your customer don't like it). And second, things can get much worse, LO:

The issue is that transactions do more than just update the row. Often they require an index to be updated OR they run out of space on the data page. This may require new pages to be allocated & existing rows on that page to be moved, called a PageSplit. It is possible for your select to completely miss a number of rows &/or count other rows twice. More info on this in the linked article

So, that means that even if the uncommited transaction you've read is committed, you can still read bad data. And, this will happen at random times. That's ugly, very ugly!

What about corruption?

As Remus Rusanu said, it's not "hard" but "soft" corruption. And it affects specially aggregates, because you're reading what you shouldn't when updating them. This can lead for example to a wrong account balance.

Haven't you heard of big LOB apps that have procedures to rebuild account balances? Why? They should be correctly updated inside transactions! (That can be acceptable if the balances are rebuilt at critical moments, for example while calcultaing taxes).

What can I do without corrupting data (and thus is relatively safe)?

Let's say it's "quite safe" to read uncommited data when you're not using it to update other existing data on the DB. I.e. if you use NOLOCK only for reporting purposes (without write-back) you're on the "quite safe" side. The only "tiny trouble" is that the report can show the wrong data, but, at least, the data in the DB will keep consistent.

To consider this safe depends on the prupose of what you're reading. If it's something informational, which is not going to be used to make decissions, that's quite safe (for example it's not very bad to have some errors on a report of the best customers, or the most sold products). But if you're getting this information to make decissions, things can be much worse (you can make a decission on a wrong basis!)

A particular experience

I worked on the development of a 'crowded' application, with some 1,500 users which used NOLOCK for reading data, modifying it an updating it on the DB (a HHRR/TEA company). And (apparently) there were no problems. The trick was that each employee read "atomic data" (an employee's data) to modify it, and it was nearly impossible that two people read and modified the same data at the same time. Besides this "atomic data" didn't influence any aggregate data. So everything was fine. But from time to time there were problems on the reporting area, which read "aggregated data" with NOLOCK. So, the critical reports had to be scheduled for moments where noone was working in the DB. The small deviations on non-critical reports was overlooked and admittable.

Now you know it. You have no excuses. You decide, to NOLOCK or not to NOLOCK