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 WITH(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.


Posted on Wednesday, June 18, 2008 by | Comments (6) | Add Comment

Comments

Gravatar

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

thanks for good explain!

Posted on 6/25/2009 9:01:46 AM by Leonardo #
Gravatar

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

Good one..... :)

Posted on 10/21/2009 4:02:28 AM by Jayanth #
Gravatar

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

Make sure you know what it does before you use it. Microsoft says no.

http://blogs.msdn.com/davidlean/archive/2009/04/06/sql-server-nolock-hint-other-poor-ideas.aspx

Posted on 4/13/2010 11:51:32 AM by TL #
Gravatar

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

good one.....:)

Posted on 11/14/2010 9:05:13 PM by Azeheruddin khan #
Gravatar

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

You example doesnt use WITH. Im tyring to figure out if we need to use WITH or not.

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

Posted on 12/1/2011 6:50:43 AM by Al #
Gravatar

Thank you for your comment. This page was there forever and nobody ever mentioned this. It is fixed now. Thanks again.

Gravatar

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

Using WITH is optional. Supposedly it is going to be required in a future release (see Books Online). So best practice is to include it.

Posted on 12/28/2011 1:50:27 PM by SQL Gourmet #

New Comment

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

Categories

Recent Tweets

  • Hierarchies with HierarchyID in SQL 2008http://blogs.msdn.com/b/simonince/archive/2008/10/17/hierarchies-with-hierarchyid-in-sql-2008.aspx
  • Visual Studio 2010 WAS painfully slow - CodeProject http://t.co/Usba1x6CZy
  • VisualSVN Subversion Server and Git | SubGit Blog http://t.co/S2FwsNKD2m

Valid HTML5