Advertisements
Home > Differences > Difference between NOLOCK and READPAST table hints

Difference between NOLOCK and READPAST table hints


AS per MS BOL:

– NOLOCK: Specifies that dirty reads are allowed. No shared locks are issued to prevent other transactions from modifying data read by the current transaction, and exclusive locks set by other transactions do not block the current transaction from reading the locked data. NOLOCK is equivalent to READUNCOMMITTED.

– READPAST: Specifies that the Database Engine not read rows that are locked by other transactions. When READPAST is specified, row-level locks are skipped.

Thus, while using NOLOCK you get all rows back but there are chances to read Uncommitted (Dirty) data. And while using READPAST you get only Committed Data so there are chances you won’t get those records that are currently being processed and not committed.
 

Let’s do a simple test:

–> Open a Query Editor in SSMS and copy following code:

-- Creating a sample table with 100 records:
SELECT TOP 100 * 
INTO dbo.Person
FROM [Person].[Person]

-- Initiate Transaction to verify the behavior of these hints:
BEGIN TRANSACTION

UPDATE dbo.Person
SET MiddleName = NULL
WHERE BusinessEntityID >= 10 
AND BusinessEntityID < 20

–> Now open a second Query Editor in SSSM and copy following code:

-- NOLOCK: returns all 100 records
SELECT * 
FROM dbo.Person (nolock)
-- this includes 10 records that are under update and not committed yet.

-- READPAST: returns only 90 records
SELECT * 
FROM dbo.Person (readpast)
-- because other 10 are under update and are no committed yet in the 1st Query Editor:

–> Now go back to the 1st Query Editor window and run following query to Rollback the Transaction:

-- Issue a Rollback to rollback the Transaction:
ROLLBACK

-- Drop the Sample table:
DROP TABLE dbo.Person

 

Note:

– Using READPAST avoids locking contention when implementing a work queue that uses a SQL Server table.

– Using NOLOCK may lead to read uncommitted (dirty) data and/or may read a row more than once due to page-splitting.

Both of them avoids locking, but on the cost of incorrect/dirty data. So one should carefully use them depending on their business scenario.


Advertisements
  1. Neha
    December 16, 2015 at 8:37 pm

    very helpful, thank you!

  2. Bharath
    January 1, 2014 at 8:26 pm

    Nice article to derstand readpast hint.

  1. March 4, 2016 at 4:47 pm
  2. July 7, 2015 at 2:24 pm

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

%d bloggers like this: