Some records are locked, can I skip them and read the rest? (READPAST)

Assume that you have a table with thousands of records and some of the records are being updated by another user. Now you try to read some records from this table. If your request needs to access records that are being updated by other user, your request will not be immediately satisfied, you have to wait till other user completes his transaction. This is the default behavior and this is what we referred as Blocking. Here is an example for it;

USE AdventureWorks2014;
GO

-- Starting a transaction and updating 93 records
BEGIN TRAN
 UPDATE Production.Product
  SET Color = ''
 WHERE Color = 'Black';

-- Open a new window and run this
-- You will not be able to see records
SELECT * FROM Production.Product;
GO

-- Stop the query and see locks placed
-- You should see the records exclusively locked
sp_lock;

What if you need to read only possible records? If you all need get your recordset and you have no issue with skipping locked records but get the available records without waiting, it can be done with a hint. The hint that has to be used for this is: READPAST.

READPAST instructs SQL Server Engine not to read records locked by other transactions and continue the reading process. Here is the same code using READPAST Table Hint.

-- Use the table hint.
-- This will skip 93 locked records
-- and show the rest
SELECT * FROM Production.Product (READPAST);
GO