Don’t blink you might READPAST it

Following on from my previous post on NOLOCK ( I want to talk about another hint called READPAST. This hint tells the database engine not to read rows that are locked by other transactions.

It is best explained with an example.

First let’s look at the basics of locking under the default isolation level with no hints. In my first SSMS (SQL Server Management Studio) window I issue the following.

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

Under a second SSMS connection I run a select statement – yes naturally I am blocked.

SELECT * FROM HumanResources.Department


I have circled in red the resource_description which can be used as input into the undocumented function  %%lockres%% to understand/confirm what is actually being locked.

select *,%%lockres%%
from    HumanResources.Department (nolock)
where    %%lockres%% IN('(62fb4a4c0e9e)')

Just to confirm it is the DepartmentID = 16 row as per my update statement.


Anyways, I issue a rollback and the blocking stops.


So now that we understand what “normally” happens let’s play with the READPAST hint.

SSMS window 1

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

SSMS window 2

 SELECT * FROM HumanResources.Department (READPAST)


Totally skips the row where DepartmentID =16.

If I rollback the update statement you should see the DepartmentID = 16 come back.



SELECT * FROM HumanResources.Department (READPAST)


So, as the name of the hint suggests, it literally does “readpast” it.

3 thoughts on “Don’t blink you might READPAST it

  1. Pingback: Don’t blink you might READPAST it - SQL Server Blog - SQL Server - Toad World

  2. Pingback: Saturday Reading 2016-12-17 | The DBA Who Came In From The Cold

  3. Pingback: Saturday Reading 2016-12-17 - SQL Server Blog - SQL Server - Toad World

Leave a Reply

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

You are commenting using your 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