NOLOCK

NOLOCK, some say it’s a fast option for queries and will never cause blocking, I say it’s quite dirty and MAY cause blocking.

I used to think that a NOLOCK (which is the same as read uncommitted) hint actually meant that a “no lock” was used, I was very wrong and the naming doesn’t really help. Let’s have a look.

I will run a random SELECT statement with the hint, such as

SELECT * FROM Sales.BigSalesOrderDetailEnlarged WITH (NOLOCK)

Then I will check the “go to” DMV for locks.

SELECT resource_type, resource_associated_entity_id,
    request_status, request_mode,request_session_id,
    resource_description
    FROM sys.dm_tran_locks
    WHERE resource_database_id = 13

nolock

A Sch-S (schema stability) lock is taken.  This is a lightweight lock; the only lock that can conflict with this is a Sch-m (schema modification) lock. (C = Conflict). This means that a NOLOCK can actually block for example against an ALTER TABLE command.

nolock1

Below shows the example of when my NOLOCK query is being blocked by ALTER INDEX commands:

 SELECT resource_type, resource_associated_entity_id,
    request_status, request_mode,request_session_id,
    resource_description
    FROM sys.dm_tran_locks 

nolcok4

So there you go, it can be involved in blocking.

Dirty reads:

This is my example data.

nocok3.JPG

In Window 1 using SQL Server management Studio I begin an open UPDATE statement – notice that it has NOT been committed.

BEGIN TRAN
UPDATE [HumanResources].[Department]
SET Name = 'Global'
WHERE DepartmentID = 16

In window 2 I do a NOLOCK select statement.

SELECT * FROM HumanResources.Department (NOLOCK)

lockdffgsef.JPG

Now that’s dirty.

6 thoughts on “NOLOCK

  1. Pingback: NOLOCK - SQL Server Blog - SQL Server - Toad World

  2. Your opening statement said you knew nolock meant read uncommitted. You ran a query against a table with an open transaction – uncommitted data. And you got the result with the uncommitted data in your query. Exactly what part is dirty?

    Like

  3. Link below provides additional discussion on use of NoLock, the main issue being Deadlock vs possible ‘wrong’ value.

    http://stackoverflow.com/questions/686724/sql-server-when-should-you-use-with-nolock

    Working with real-time vehicle tracking data, dirty isn’t the issue, and after being stung with database deadlocks in Oracle v8.x I have always used ReadUncommitted in SqlServer.

    NoLock is a much more succinct way of issuing a command without having to explicitly specify transaction for ReadUncommitted.

    Like

  4. Pingback: Don’t blink you might READPAST it | All about SQL

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s