Patrick\’s Bytes

22, May 2007

Using NOLOCK and READPAST table hints in SQL Server

Filed under: Database — patrickyong @ 9:09 am

To keep SQL Server on tip top performance, all care is taken into considerations when writing application. NOLOCK is one of the common SQL hint to prevent locking on data selection in the context of transaction. Too many lockings will results into record deadlocks.  Today come across READPAST hint which is similar to NOLOCK. Below is an extraction of the article found on Tech Republic.com

The READPAST table hint example is very similar to the NOLOCK table hint example. I’ll begin a transaction and update one record in the SalesHistory table.

BEGIN TRANSACTION      UPDATE TOP(1) SalesHistory      SET SalePrice = SalePrice + 1

Because I do not commit or roll back the transaction, the locks that were placed on the record that I updated are still in effect. In a new query editor window, run the following script, which uses READPAST on the SalesHistory table to count the number of records in the table.

SELECT COUNT(*) 
FROM SalesHistory WITH(READPAST)

My SalesHistory table originally had 300 records in it. The UPDATE statement is currently locking one record in the table. The script above that uses READPAST returns 299 records, which means that because the record I am updating is locked, it is ignored by the READPAST hint.

Link to Using NOLOCK and READPAST table hints in SQL Server

Advertisements

Leave a Comment »

No comments yet.

RSS feed for comments on this post. TrackBack URI

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

Create a free website or blog at WordPress.com.

%d bloggers like this: