4

The (NOLOCK) optimizer hint is well known by most SQL developers for setting the isolation level so that tables with rows or escalated page locks can be read. I've always known about the bad and ugly; dirty reads, errors, dup rows that can occur. My specific question is why is it so overused in every organization I tend to consult with? I've seen it used for every select statement on every table in the last 3 clients I've been at. These are OLTP systems and not replicated data that is read only. What are the possible reasons for this? Is the memory/hardware insufficient to get transactions processed? Is data consistency less important than it used to be? What is the general consensus and best practice on using NOLOCK?

3

You seem to already know what the table hint NOLOCK does, but you question "why" do people use it so frequently. In people's experience, or people that have shared experience with them, they have alleviated concurrency problems before by using the read uncommitted table hint.

Despite what the hint does (the lack of issuing a shared lock on data to prevent from blocking or being blocked), they still use it. Why? Because it is "easy" to sacrifice important things (consistent and clean data, security, etc.) with common "fixes".

Why leave it up to chance that your query will block? Just specify WITH (NOLOCK). 13 characters that wipe clean any thought of being blocked or blocking.

Of course this is bad practice, and could lead to reading data that is dirty and inconsistent, but it is easy. Typically this is done by SQL developers that don't want to trouble themselves with troubleshooting blocking and other concurrency situations, and they'd rather take the easy way out.

My specific question is why is it so overused in every organization I tend to consult with? I've seen it used for every select statement on every tables in the last 3 clients I've been at. What are the possible reasons for this?

Because those that use and abuse it tend to not understand relational database management systems or concurrency models, and it errs on the side of generosity in lieu of data cleanliness.

Is the memory/hardware insufficient to get transactions processed?

No, that's typically not the driving factor.

Is data consistency less important than it used to be?

Data consistency should be a timeless goal. 99% of the time it's out of laziness or ignorance that developers use it.

What is the general consensus and best practice on using NOLOCK?

The best practice is don't use it unless you've absolutely proven without a doubt that you need it and it is ok. Simple as that.

5

Because many teams are clueless about benefits and drawbacks of the different features of Microsoft SQL Server.

Imagine a slow app. Management has a choice: either throw more hardware (which translates into immediate and substantial cost), or blame developers for not doing their job correctly and expect them to make it faster (which translates into substantial cost spread over weeks or months). What, in your opinion, is the management's choice in most companies?

Now, the team searches for the ways to optimize their app and a programmer learns that NOLOCK makes everything magically faster. Great—he tells to himself—I can tell that to my team and our product will be faster. If the team has a skillful DBA, they have a chance to avoid the mistake. If they don't (and in too many cases, they don't), they may end up with NOLOCKs everywhere.

Unfortunately, consequences of the lack of data consistency are not immediate. In some cases, the application may function for months without issues. Then, when an inconsistency happens, nobody knows the origin of the problem. Programmers blame system administrators. System administrators blame programmers. The system may end up being patched to avoid inconsistencies in the specific location by adding dubious tests at application level which make everything slow as hell, and function for months before encountering an inconsistency somewhere else.

This being said, there are valid circumstances where data consistency doesn't matter. The feature is there for a purpose; if the number of transactions is high and obtaining inconsistent data is perfectly acceptable, there is nothing wrong in using NOLOCK. For instance, would it really matter to get a comment on a forum while a rollback happened to remove it? Probably not. On the other hand, if you see it being used in a banking application inside a method which transfers money from an account to another, this is more problematic.

  • 1
    This is a good answer but your over all tone implies people are being careless. (Though it is true it often doesn't matter). The better reason is often locking its simply not needed. If My data is updated only at night,then and all my reports run after, in batches, I would be a fool use locks and risk my reports colliding with each other. Small reference tables, are often the most accessed and least often updated, whats gonna happen if you have an extra reference ID or missing one? Nothing, because that ID is not referenced anywhere. – Morons Feb 18 '15 at 2:02
  • @Morons If your data is only updated at night then why do you need your daytime data retrieval queries not to take shared locks and block? – Thomas Stringer Feb 18 '15 at 2:28
  • 1
    @ThomasStringer There is no need for it, data is not being updated, there is no chance of a corrupt read. Its just additional overhead. – Morons Feb 18 '15 at 2:39
  • 1
    @Morons I agree with MainMa, in my experience NOLOCK corresponds with ignorance or carelessness. – user22815 Feb 18 '15 at 2:51
  • @Morons "there is no need for it"... Then why do it? – Thomas Stringer Feb 18 '15 at 3:00

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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