Tuesday, March 10, 2009

To NOLOCK Or Not?

by David L. Penton via The Penton-izer on 3/10/2009 2:05:00 AM

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.

Posted to SQL Server