Skip to content

Tips and Tricks #4 – SQL (nolock) – Do You Need To Use It?

March 24, 2010

In one of my email conversations with Victoria Yudin, she asked me why I used (nolock) in a lot of my SQL Views.  As you will see at the top of many of the views I post, there will be credits given to Salespad.net for the base view that I have modified.  The guys over at Salespad like using the (nolock) where as I personally don’t take the time.  The conversation got me to thinking though…do I need to use it or not?  After a little research, I found that the answer really depends on your situation.  Take a look at the following article on SQL (nolock) and see what works best for your situation.

From the article:

http://articles.techrepublic.com.com/5100-10878_11-6185492.html

NOLOCK

This table hint, also known as READUNCOMMITTED, is applicable to SELECT statements only. NOLOCK indicates that no shared locks are issued against the table that would prohibit other transactions from modifying the data in the table.

The benefit of the statement is that it allows you to keep the database engine from issuing locks against the tables in your queries; this increases concurrency and performance because the database engine does not have to maintain the shared locks involved. The downside is that, because the statement does not issue any locks against the tables being read, some "dirty," uncommitted data could potentially be read. A "dirty" read is one in which the data being read is involved in a transaction from another connection. If that transaction rolls back its work, the data read from the connection using NOLOCK will have read uncommitted data. This type of read makes processing inconsistent and can lead to problems. The trick is being able to know when you should use NOLOCK.

As a side note, NOLOCK queries also run the risk of reading "phantom" data, or data rows that are available in one database transaction read but can be rolled back in another. (I will take a closer look at this side effect in part two of this article series.)

The following example shows how NOLOCK works and how dirty reads can occur. In the script below, I begin a transaction and insert a record in the SalesHistory table.

BEGIN TRANSACTION
      INSERT INTO SalesHistory
      (Product, SaleDate, SalePrice)         
      VALUES           
      ('PoolTable', GETDATE(), 500)                  

The transaction is still open, which means that the record that was inserted into the table still has locks issued against it. In a new query window, run the following script, which uses the NOLOCK table hint in returning the number of records in the SalesHistory table.

SELECT COUNT(*) FROM SalesHistory WITH(NOLOCK)

The number of records returned is 301. Since the transaction that entered the record into the SalesHistory table has not been committed, I can undo it. I’ll roll back the transaction by issuing the following statement:

ROLLBACK TRANSACTION

This statement removes the record from the SalesHistory table that I previously inserted. Now I run the same SELECT statement that I ran earlier:

SELECT COUNT(*) FROM SalesHistory WITH(NOLOCK)

This time the record count returned is 300. My first query read a record that was not yet committed — this is a dirty read.

About these ads

From → Dynamics GP

Leave a Comment

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

Follow

Get every new post delivered to your Inbox.

Join 27 other followers

%d bloggers like this: