KARPACH

WEB DEVELOPER BLOG

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

NOLOCK is a 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 a significant improvement on large tables, where insert / update commands can take 3-15 seconds. However, you need to be very careful 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 or 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 June 18, 2008 by

Comments

Posted on 6/25/2009 09:01:46 AM by Leonardo

thanks for good explain!

Posted on 10/21/2009 04:02:28 AM by Jayanth

Good one….. :)

Posted on 11/14/2010 09:05:13 PM by Azeheruddin khan

good one…..:)

Posted on 12/1/2011 06:50:43 AM by Al

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'
Thank you for your comment. This page was there forever and nobody ever mentioned this. It is fixed now. Thanks again.

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

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.