WITH (NOLOCK)

Question

WITH (NOLOCK)

pkrieg

Mr or Mrs. 500

Points: 538
More actions
May 29, 2004 at 12:46 pm

#108322

We are having some locking problems with our database and are planning to implement the WITH (NOLOCK) to help with this problem.
We have some questions that we would appreciate your help with:
1) Is the default on 'SELECT * FROM x' to lock each row?
2) Should all SELECTS have WITH (NOLOCK)?
3) Should all INNER JOINS have WITH (NOLOCK) if they are not part of an UPDATE?
4) Will performance improve using WITH (NOLOCK)s?
5) Is the default on 'UPDATE...' to lock each row (ROWLOCK)?
6) Will performance improve using WITH (ROWLOCK)?
7) Can we or should we include WITH (NOLOCK) on temp files?
Thanks in advance.
Phil

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

Jeff Moden SSC Guru Points: 1003853 More actions · Answer 1

1) Is the default on 'SELECT * FROM x' to lock each row?
I don't believe so but could be wrong.
2) Should all SELECTS have WITH (NOLOCK)?
Only if it's OK to do a "dirty read".
3) Should all INNER JOINS have WITH (NOLOCK) if they are not part of an UPDATE?
Yes, if it's OK to do a "dirty read".
4) Will performance improve using WITH (NOLOCK)s?
Yes, if locks are a problem. NOLOCK allows dirty reads past most locks.
5) Is the default on 'UPDATE...' to lock each row (ROWLOCK)?
In order to do an UPDATE on a row, the row must be locked even for the briefest of perios.
6) Will performance improve using WITH (ROWLOCK)?
No. Only INSERT, UPDATE, and DELETE require row locks and they pretty well do it on their own.
7) Can we or should we include WITH (NOLOCK) on temp files?
Nothing will be gained by NOLOCKing temp files because temp files can only be accessed by the connection that created them.

Suggestion... adding WITH (NOLOCK) or just (NOLOCK) to each table name is a real pain and you should be careful not to NOLOCK the target table of an INSERT, UPDATE, or DELETE. An easier solution might be to use SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED at the beginning of a query which is the same as using WITH (NOLOCK) on all tables involved in a SELECT statement or used as inputs to INSERT, UPDATE, or DELETE. If you have a particular case where dirty reads should not be allowed, then use WITH (ROWLOCK) only on those tables where the dirty read should not be allowed.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

pkrieg Mr or Mrs. 500 Points: 538 More actions · Answer 2

Thanks for your reply Jeff. It confirmed my 'book' findings.

Phil