Alternative to WITH (NOLOCK) in your SQL

Querying a table with the table hint NOLOCK allows a ‘dirty’ read to take place without applying a lock on the table. The advantages of this are performance over reliability because there may be uncommitted transactions or updates in progress, meaning the data you read could be out of date. Ignoring the pros and cons of NOLOCK for the moment, here is an example query using the NOLOCK table hint:

SELECT o.*
FROM Customer c WITH (NOLOCK)
INNER JOIN Order o WITH (NOLOCK) ON c.Id = o.CustomerId
WHERE c.Id = 1352;

The most obvious thing is that the table hints add quite a lot of ‘noise’ to the SQL command, especially when you are joining on a lot of tables. So a simpler alternative is to add the following line before the query (typically this will be at the top of a view or stored procedure) and then you don’t need to add a NOLOCK hint to every table.

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

Advertisements

About Phil Munro

I have been developing commercial desktop and distributed web applications with Microsoft technologies since 1997.
This entry was posted in SQL. Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s