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.
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.
Thanks for your reply Jeff. It confirmed my 'book' findings.