nolock

  • Is nolock assumed in a select statement (SQL 2000) or should I include it?

  • 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.

  • 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