Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

WITH (NOLOCK) Expand / Collapse
Author
Message
Posted Saturday, May 29, 2004 12:46 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Monday, August 27, 2007 2:06 AM
Points: 34, Visits: 1

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

 

 




Post #118419
Posted Saturday, May 29, 2004 9:59 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 8:07 AM
Points: 35,272, Visits: 31,764
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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #118422
Posted Saturday, May 29, 2004 11:22 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Monday, August 27, 2007 2:06 AM
Points: 34, Visits: 1

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

Phil




Post #118424
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse