I’ve poked a lot of fun at NOLOCK
I mean, it sucks. You can get incorrect data, and it can throw errors if your data is particularly volatile. Generally, outside of testing a query or grabbing some sample data, I’m going to avoid it.
But what if…
What if it’s about all you can handle? Or your server can handle?
Picture a couple developers who started their app in the cloud, where they can’t get fancy with tempdb, fast disks aren’t in the budget yet, along with that beefier server with some extra RAM. They may not be able to turn on RCSI or SI at the drop of a hat; tempdb would keel over with the row versioning as part of a workload that already uses it pretty heavily.
They still need to run reports, either for users, or for higher ups at the company, and they can ask for them at any time. Caching the data when user activity is low and reporting against it when someone asks may raise some questions, like “why doesn’t my sales data show anything from today?”, or worse. You could invalidate the cache every X minutes, but that doesn’t help because then you need to re-run that reporting query every X minutes. That’s only moderately better than letting users query it at will.
Even with the right indexes in place for their workload, readers and writers will block each other, and long running reports can be painful.
What other options might they have? READPAST?
Ultimately, the goal should be to get your server, and your skills, to a place where they can handle optimistic isolation levels. Until you get there, you should know what NOLOCK actually does.
What everyone thinks: It doesn’t take out any locks
What it really does: Doesn’t respect other locks
This is where things go bad. You can get partial data, double data, and if you catch an update that gets executed as an insert and a delete, both versions of the row. That’s just for starters. And that’s tough news to break to people who just may well want accurate reports.
You might be able to get away with them just not noticing if things don’t line up. You might not. You could just ask them to re-run a report if they come to your desk with a hug full of dot matrix printer paper festooned with sticky note tabs, but if that happens enough times…
ISVs have it especially tough
Anyone with a credit card can install their software. Thinking about an application like kCura’s Relativity, where servers who have never met a DBA often end up with multiple terabytes of data, what would happen if a few cases got really lively? Picture tempdb with one file on a 100 GB C: drive. It ain’t pretty.
You can make all the best practice setup guides you want, but it doesn’t mean anyone’s going to follow them.
And so I ask you, dear reader
Are you ever okay with NOLOCK? Under what circumstances?
What options do DBA-less, development focused shops have?
Thanks for reading!
Brent says: I’m always okay with NOLOCK because I just pretend it’s another one of SQL Server’s incorrect results bugs.