Using MS-SQL's NOLOCK for faster queries

NOLOCK (aka READUNCOMMITED) is a t-sql hint (directive) that allows MS SQL Server to ignore the normal locks that are placed and held for a transaction and allows the query to complete without having to wait for the first transaction to finish and therefore release the locks.

Using NOLOCK gives significant improvement on large tables, where insert / update commands cantake 3-15 seconds.

However you need to be very carefully with using NOLOCK. Remember you can get some records that were partially updated or inserted. It is safe to use NOLOCK on rows that you know are not changing right now.For example, records that belong to some user and he is running reports, but not updates, however some users can do updates / inserts at the same time.

Example:

SELECT * FROM ORDERS (NOLOCK) WHERE orderdate < GETDATE() - 1

Issues

You can get dirty reads using the NOLOCK hint. These are also other terms you may encounter for this hint.

• Dirty Reads - this occurs when updates are done, so the data you select could be different.
• Nonrepeatable Reads - this occurs when you need to read the data more than once and the data changes during that process
• Phantom Reads - occurs where data is inserted or deleted and the transaction is rolled back. So for the insert you will get more records and for the delete you will get less records.

Understanding the SQL Server NOLOCK hint

MSDN >> Concurrency Effects