NOLOCK is not the devil.
Wow, that felt weird to say.
The subject of NOLOCK hints (also known as the Read Uncommitted isolation level in SQL Server) is one that can spark very heated arguments. The arguments typically revolve around concurrency — how many processes can simultaneously access your data at once — and data reliability (aka consistency) — how sure you are that the data you got is representative of the actual data in your database.
Facts About NOLOCK
Before I continue with my opinion, I am going to start with a review of some facts:
Fact #1: Under the Read Uncommitted isolation level, you can read “dirty” data.
- Because of the way data is organized in a database and the way that changes to it are handled, this means you might not see some data that’s actually there. In simplified terms, updates to the data might change its internal location. If you started querying data while it was changing, your connection may have outdated information on where all of it is currently located and, as a result, miss returning some rows or return the same row multiple times.
- Fact #2: The default isolation level in SQL Server is READ COMMITTED.
- Note the lack of “un” in the second word there.
- Most people assume that because you can only read data that’s been fully committed, you won’t encounter the above-mentioned issues.
- Fact # 3: Under the READ COMMITED isolation level, the same things that happen under the Read Uncommitted isolation level can also happen.
- The explanation for this gets way more computer-science-y than I am qualified to talk about, but a brilliant colleague of mine, Paul White, has written about it in his blog here.
Human Error Causes More Issues Than NOLOCK
I’m already feeling warm from the heat of the glares of people in my network as they wait with their torches and pickaxes for what I am going to say next.
I’ve worked and consulted in IT in many different industries, including manufacturing, healthcare and financial services. In each one of them, the Read Uncommitted isolation level was used in production applications. In exactly none of them did this ever cause a catastrophic event.
No manufacturer shipped a metric ton of parts with the wrong specifications because of NOLOCK.
No bank unknowingly created a financial meltdown or accidentally swiped a million dollars from someone’s account because of NOLOCK.
No one’s stock portfolio disappeared because of NOLOCK.
No hospital failed a financial audit because of NOLOCK.
In the billions of transactions conducted during my tenure, was there an error somewhere that crept in due to the use of the Read Uncommitted isolation level? Undoubtedly there was. It’s likely there were also errors from using the default isolation level. The truth is that human error was much more responsible for issues than was the choice of the “wrong” transaction isolation level. Names and locations have been omitted, but these are a few of the issues that I witnessed:
- Rushed deliverables and failure to heed warnings about performance in a product revamp caused a flood of end user complaints, so much so that the company decided to send out a mea culpa letter to all of its customers.
- On more than one occasion, mislabeling of a server in the server room caused a production server to be powered down during normal work hours.
- On more than one occasion, an administrative-level user ran scripts meant for development on production, wiping it out or causing work stoppages for hours until it could be fixed.
- Despite a clear, tested checklist being present, a DBA decided to “improve efficiency” for weekend maintenance (spoiler alert: nope), resulting in additional load and reduced performance on critical systems until the following weekend.
If you’re designing a new system, I don’t suggest using NOLOCK as your method of concurrency improvement. The pitfalls I mentioned above do exist, and they do represent risk. Instead, if you’re designing a new system, I would strongly recommend designing it to use an isolation level like Read Committed Snapshot Isolation (RCSI). RCSI stores old versions of your data while it is being changed, avoiding those data consistency/reliability problems. It improves concurrency as well, because it uses a different model for locking data. Database systems like Oracle and PostgreSQL use methodologies similar to RCSI as their default, and won’t allow Read Uncommitted.
I can hear the whine of flamethrowers firing up in the distance, so I do want to clarify that I’m not recommending the use of the NOLOCK hint or Read Uncommitted isolation level in your environment to solve concurrency problems. However, if your environment is using this tactic widely, I think it’s also foolishly impractical for an incoming DBA or consultant to hammer you for it. Regression testing to eliminate it and replace it with RCSI is exorbitantly expensive in terms of person-hours, and there is no easy way to contain it to just certain parts of your system. Punishing yourself with guilt because you’ve decided not to direct effort to remove NOLOCK from your environment won’t make your product or team better. You know what will? Learning where it can bite you — trying to constrain it to areas where the objective is to analyze a trend rather than to reconcile a balance sheet.
I’ve said before that the peak (or near-peak) of Mount Everest is littered with the frozen bodies of determined climbers who were unable to summit. In the end, it’s just another hill that isn’t worth dying on. Don’t let eliminating NOLOCK be your Mount Everest. Don’t be ashamed that you’ve been unable to rid yourself of it, and don’t think that because it’s there, no professional DBA will want to work with you and no consultant will help you, or that we will spend an inordinate amount of time complaining about it if it shows up in your health check.
Personally, I am much more worried about how prepared your company is to deal with an outage or ransomware attack than I am about whether you use NOLOCK in your queries. As I write this, I have spent a good amount of time in the past two weeks helping a company that was not properly prepared to recover from a ransomware attack. Implementing that kind of protection is much less complicated than getting NOLOCK out of your environment, and it is a hill worth dying on.
SQL Tailor Consulting
At SQL Tailor Consulting, we’re practical people who want to offer you workable solutions. If you’d like a little (or a lot of) DBA assistance without paying for a full-time DBA, we’d love to help you. We’ll even do a quickie free health check and spend 30 minutes on the phone with you and your team to see if we’re the right fit for your organization. Have a look at our website, sqltailor.com, for some examples of packages we offer or ask us for a customized package.
