Table Hints and NOLOCK

Table hints are statements which instruct SQL to use specific locking methods or indexes when optimizing a query. One of the most common table hints is NOLOCK (or, its equivalent, READUNCOMMITTED), which I have sometimes seen used so extensively that it is as common as the table name itself.

Consider the following description from MSDN:

Specifies that dirty reads are allowed. No shared locks are issued to prevent other transactions from modifying data read by the current transaction, and exclusive locks set by other transactions do not block the current transaction from reading the locked data. Allowing dirty reads can cause higher concurrency, but at the cost of reading data modifications that then are rolled back by other transactions. This may generate errors for your transaction, present users with data that was never committed, or cause users to see records twice (or not at all).

NOLOCK is a handy way to speed up queries when performance is blocked due to excessive locking on a table. This can happen on a table that has a high number of INSERTs, UPDATEs, and DELETEs. However, you should not use NOLOCK — or any other table hint — unless an issue is already known to exist, and the table hint is used to explicitly resolve the issue without creating new issues.

In other words, do not add NOLOCK unless it is known to address a specific and known problem in a query. (No premature optimization!)

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes:

<a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>