Why NOLOCK is a really, really bad idea

The NOLOCK / READ UNCOMMITTED hint is much more dangerous than its name suggests. And that it why most people who don’t understand the problem, recommend it. It creates “incredibly hard to reproduce” bugs. The type that often destroy your end-users confidence in your product & your company.

What many people think NOLOCK is doing
Most people think the NOLOCK hint just reads rows & doesn’t have to wait till others have committed their updates. If someone is updating, that is OK. If they’ve changed a value then 99.999% of the time they will commit, so it’s OK to read it before they commit. If they haven’t changed the record yet then it saves me waiting, its like my transaction happened before theirs did. But it's absolutely wrong!

The Problem
Those that know better will often point to the fact that NOLOCK allows dirty reads. Data that has not been committed can, and will, be returned. In straight terms, an insert, update, or delete that is in process will considered for the result set, regardless of the state of the transaction. This can mean returning rows from an insert that may potentially rollback, or returning rows that are being deleted.

What about times a query is returning rows that you know for certain are not being modified? Suppose that you are updating rows for one client and need to return rows for a second client. In this case, will the use of NOLOCK be safe? The data isn’t being modified for the second client, so you might assume that the returning that data won’t have an opportunity for dirty data. Unfortunately, this assumption is incorrect.

Same Data is Read Twice
There are rare occasions when the same data can be read twice when using the read-uncommitted isolation level or nolock hint. To illustrate this issue we have to give a little background first. Clustered Indexes are created on SQL Server tables to physically order the data within the table based on the Cluster Key. The leaf pages of the index contain the data pages which contains the actual data for the table. Data pages can hold 8K worth of data.
Scenario: You have an ETL process that will Extract all records from a table, perform some type of transformation, and then load that data into another table. There are two types of scans that occur in SQL Server to read data: allocation scans and range scans. Range scans occur when you have a specific filter (where clause) for the data you are reading, and an index can be used to help seek out those specific records. When you do not have a filter, an allocation scan is used to scan all of the data pages that have been allocated to that table. Pending you are not doing any type of sort operations, your data will read the data pages in the order as it finds them on the disk. For simplicity, let’s assume there is no fragmentation so your data pages are in order 1-10. So far your process has read pages 1-6. Remember your NOLOCK process is not requesting shared (S) locks so you are not blocking other users. Meanwhile, another process begins which inserts records into your table. This process attempts to insert records onto Page 3, but the page is full and the record will not fit. As a result the page has to be split and half of the records will remain on Page 3 and the other records will be moved to a new page which will be page 11. Your process has already read the data that was on Page 3, but now half of that data has been moved to page 11. As a result, as your process continues it will read Page 11 which contains data that has already been read. If there is no type of checks on the destination table, you will end up with bad duplicate data.

Solution
One of the the main concerns that people have with locking, is the blocking that is associated with two users trying to access the same locked resource. As an alternative to using NOLOCK, try using READ_COMMITTED_SNAPSHOT / SNAPSHOT isolation level instead. Through this, data readers won’t block writers; which will reduce the amount of lock blocking on your data platform.

Conclusion
SQL Server is a very complex enterprise database solution with many options and flags that can be changed to alter the behavior of SQL Server. Although many of these options have a justified use, it is important to understand the risks that are associated with changing these options. The read-uncommitted isolation level and nolock table hint are no exception to this rule. Generally it is best practice to stick with the default isolation level and refrain from using table/query hints unless it is absolutely necessary and the solution has been thoroughly tested. Using read-uncommitted and nolock should be the EXCEPTION and not the RULE.