Reading records that are not locked - READPAST hint

When a set of records are being updated, with the default Isolation Level which is Read Committed, SQL Server makes sure that no one can read them until the transaction is completed. This avoids Dirty Read concurrency issue but it decreases concurrency.

What if you need to read rest of the records that are no locked? Now the question is, how can we instruct to SQL Server to skip locked records and read other records. It makes possible with READPAST hint.

The READPAST hint allows us to read records by ignoring locked record without waiting till incompatible locks to be released. This is NOT similar to READUNCOMMITTED hint because it reads all records including records that are locked.

Here is an example:

Execute the following query in a new window.

USE AdventureWorks2014;
GO

SELECT * FROM Production.Product;

-- This starts a transaction and locks set of records
BEGIN TRAN
 UPDATE Production.Product
  SET Color = 'Black'
 WHERE Color IS NULL;

-- ROLLBACK TRAN 

Now try following codes with another window

-- This wait until locks are released.
SELECT * FROM Production.Product

-- This returns all records including 
-- records that are being changed.
SELECT * FROM Production.Product WITH (READUNCOMMITTED);

-- This returns all records excluding
-- records that are being changed.
SELECT * FROM Production.Product WITH (READPAST);

See the difference in results. You may find READPAST hint useful with certain scenario.