February 15, 2006 at 7:19 am
Is nolock assumed in a select statement (SQL 2000) or should I include it?
February 15, 2006 at 8:20 am
No lock is not assumed.
You do not want to use Nolock haphazardly. You should use it in very specific places. Because of the potential for a dirty read, No matter what your transaction isolation level is, if you use a lock hint you are esentially over riding it.
I don't recommend you use it unless you know what your doing.
From Books Online
NOLOCK :
Do not issue shared locks and do not honor exclusive locks. When this option is in effect, it is possible to read an uncommitted transaction or a set of pages that are rolled back in the middle of a read. Dirty reads are possible. Only applies to the SELECT statement.
February 16, 2006 at 8:10 am
NoLock is not assumed.
You can add "set transaction isolation level read uncommitted" and all tables opened in that connection will be opened with nolock. Otherwise, use [with] (nolock) after each table in your from clause.
As long a dirty read is acceptable, using nolock hints, or setting isolation levels is perfectly fine, and VERY advisable in mixed OLAP / OLTP, and replication environments.
Terry
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply