What is the benefit of using WITH (NOLOCK) in T-SQL?

NOLOCK is 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:

INSERT INTO Orders (UserName,Total) VALUES ('John', 12.50)

 

SELECT * FROM Orders (nolock) where UserName = 'Viktar'

 

Conclusion:

If you have blocking issues try to eliminate them, but this is not always so simple. If you can not get rid of the severe blocking issues, see if using NOLOCK can help.


Wednesday, June 18, 2008 | Comments (2) | Add Comment

Comments

Gravatar

Re:What is the benefit of using WITH (NOLOCK) in T-SQL?

thanks for good explain!

6/25/2009 9:01:46 AM | by Leonardo
Gravatar

Re:What is the benefit of using WITH (NOLOCK) in T-SQL?

Good one..... :)

10/21/2009 4:02:28 AM | by Jayanth

New Comment

Your Name:
Email (for internal use only):
Subject:
Comment:
 
Code above: