To NOLOCK Or Not?

I was having a discussion with another DBA the other day about a particular stored procedure. It was a paging stored procedure with a number of other tasks to do. He was adamant about me including the NOLOCK hint for the SELECT statements. He ranted a bit about how it was "best practice" to use the NOLOCK hint on all SELECT statements.  I retorted with a different general statement of "let's optimize first and then see where we are."  I added that I felt it was a last resort to add NOLOCK to queries.

Keep in mind that SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED is basically the same thing as putting NOLOCK on all of the tables in your FROM clause.  What does SQL Server Books Online say about locking (emphasis/coloring mine)?

 READ UNCOMMITTED

Specifies that statements can read rows that have been modified by other transactions but not yet committed.

Transactions running at the READ UNCOMMITTED level do not issue shared locks to prevent other transactions from modifying data read by the current transaction. READ UNCOMMITTED transactions are also not blocked by exclusive locks that would prevent the current transaction from reading rows that have been modified but not committed by other transactions. When this option is set, it is possible to read uncommitted modifications, which are called dirty reads. Values in the data can be changed and rows can appear or disappear in the data set before the end of the transaction. This option has the same effect as setting NOLOCK on all tables in all SELECT statements in a transaction. This is the least restrictive of the isolation levels.

....

Because the SQL Server 2005 query optimizer typically selects the best execution plan for a query, we recommend that hints, including <table_hint>, be used only as a last resort by experienced developers and database administrators.

The text in blue is what I find most interesting.  I have been in situations where tables that have NTEXT columns (off page data) have SELECT statements that use the NOLOCK hint. I come across more consistency errors from those data points than any other.  Now, this isn't just because these conditions are here (NTEXT, NOLOCK) but they contribute to the problem of inconsistency.  These are tools that can be used when enough avenues have been taken to increase performance through table, index, and query design first. I find that gets you so close you may not need to go any further.  I'd rather spend a little time and optimize indexes and queries instead of just putting NOLOCK on things. Reduction in CPU cost, reads, and duration amass huge wins in server performance and application performance.  Note the text in bold. Microsoft even recommends not using hints unless there are dire needs for it.

What did a few people on Twitter have to say about it?

  • xandersherry: @dpenton I agree. Hints shouldn't ever be "standard practice." They're specialized tools to be applied in specialized circumstances.
  • johnny861: @dpenton unless you just really wanna be dirty
  • rhyscampbell: @dpenton Nope, leave it up to the opimizer, use only when you have to and never use when accuracy is critical, i.e cannot tolerate rollback
  • jmkehayias: @dpenton not unless you accept that you get uncommitted data. Of course I have an ISV app that uses it for every query that is submitted.
  • IDisposable: Should using the NOLOCK hint be a "standard operating practice" for SELECT statements? (via @dpenton) NO NO NO NO!

Bottom line for me is that this is a special tool for special circumstances. I'd rather let the query engine handle it.

Trackbacks

  • CodeStock 2009!
    Posted on 6.27.2009 at 4:13 PM

    I was invited to speak at CodeStock 2009 this year. There were some great people here, met some new people

7 Comments

  1. Don Demsak Says:

    T-SQL with NOLOCK hint is a code smell! It's a hint that there is something maybe wrong with your design.

  2. Michelle Ufford Says:

    I agree that NOLOCK shouldn't be a "standard practice," but it does have its place. I consistently hear that using NOLOCK means there's something wrong with the design of your system (i.e. see Don's comment). In my experience, it can have a pretty large impact in high volume, high contention environments *where dirty reads are acceptable*, and it shouldn't be prematurely discarded.

  3. Brad Kingsley Says:

    Is a standard SELECT statement a "transaction" though? That's what wasn't clear to me when I read this in BOL long ago. Because of that, I generally use NOLOCK on an as-needed basis.

  4. David L. Penton Says:

    Every sql statement is a transaction in and of itself. But, with NOLOCK you have the possibility of returning uncommitted data. The question becomes "How do you define AS-NEEDED?" That is the crux of the dilemma that I think some people are faced with.

  5. Steve Smith Says:

    For me it comes down to a question of whether I value data consistency or speed more. Which is worse, that my forum display a thread without a particular message in it, or that my forum take 2 seconds to display because of a lock? I'm going to vote for speed. Which is worse, that my shopping cart display the correct items, or that it take a couple of seconds because of a lock? I'm going with the lock. It really depends on which thing the business dictates is the more important.

    In many cases, you can have your cake and eat it too - consistent, fast enough data. So, like Penton said, don't prematurely optimize. But when you do see a need to optimize, let the business needs dictate the tradeoffs.

  6. Michael Hunter Says:

    I could understand certain diagnostic scenarios, but one has to ask, why would anyone want to query data that may contain potentially uncommented instances?

    What value does it serve? Is one trying to mitigate risk in a poorly (or under) architected schema for the sake of performance? Is the db over compensating for a poorly architected application?

    I'm inclined to believe it is a last resort, for whatever the circumstances may be.


Leave a Reply