SQL Server Performance

what is with nolock

Discussion in 'Getting Started' started by sureshk, Apr 23, 2008.

  1. sureshk New Member

    hi,
    what is the usuage of with nolock property.
    thanks in advance.

  2. MohammedU New Member

    From Books Online:
    Does not issue any locks. This is the default for SELECT operations. It does not apply to INSERT, UPDATE, and DELETE statements.
    Note:
    With SQL Server, a NOLOCK hint enables Read Uncommitted behavior. With SQL Server Mobile, using a NOLOCK hint still gives a Read Committed isolation level. SQL Server Mobile maintains copies of data to ensure that data can be read without needing share locks to help protect the data.
  3. jagblue New Member

    more detail on NOLOCK as per BOL
    Using locking hints in SQL Server Mobile is similar to that of SQL Server. However, for SQL Server Mobile, the NOLOCK hint has a very different behavior than it does for SQL Server. In SQL Server Mobile, the NOLOCK hint is the default for SELECT statements, but this still enforces Read Committed behavior.
    In SQL Server, a SELECT statement with the default isolation level of Read Committed causes S locks being taken and released on rows as they are read. Although this enforces the isolation level, it means that a SELECT statement waits if an incompatible lock exists on a row for which an S lock is required. When the NOLOCK hint is specified, the SELECT operation does not try to take the S lock and the data is read. Although this lets the operation succeed, it also means that the SELECT statement can read uncommitted data.
    SQL Server Mobile does not use S locks to ensure that data is Read Committed. Because SQL Server Mobile uses a page versioning mechanism when changing data, the data that is required by a SELECT statement can be read from the appropriate copy of the page. It is not required to take S locks to ensure Read Committed. Therefore, although SQL Server Mobile is using NOLOCK for a SELECT statement, the data is read at the isolation level of Read Committed. You cannot have a dirty read (Read Uncommitted) with SQL Server Mobile.

Share This Page