I'm doing some study on locking and read this blog post about NOLOCK and READPAST in SQL Server 2005. The test was straightforward:

  • In one session , begin a transaction, do a count on a table, then update a record.
  • In another session, with that previous transaction still open, do a count with NOLOCK then a count with READPAST.

The pre-update count should match the NOLOCK count, and the READPAST count should be one less because the database engine is reading past the locked record. Except in my SQL Server 2014 developer instance with AdventureWorks2012 all of the counts were the same:

-- Session 1
Begin	Tran
Select 	Count(*) 
From 	AdventureWorks2012.dbo.DatabaseLog

Update 	AdventureWorks2012.dbo.DatabaseLog 
Set 	DatabaseUser = DatabaseUser 
Where 	DatabaseLogID = 1
-- Rollback
-- Session 2
Select 	Count(*) 
From 	AdventureWorks2012.dbo.DatabaseLog With(NOLOCK)
Select 	Count(*) 
From 	AdventureWorks2012.dbo.DatabaseLog With(READPAST)

I suspect this is because there is a row lock however the Count aggregate gets its data from an Index Scan (or Table Scan if I remove the index), so the row lock isn't encountered and doesn't block anything.

I'm not sure why this is different to SQL 2005, if it ever was. But what does definitely act differently in the behaviour between the two is when you try to access the specific changed record, and NOLOCK returns it but READPAST doesn't.

Select 	* 
From 	AdventureWorks2012.dbo.DatabaseLog With(NOLOCK) 
Where 	DatabaseLogID = 1

Select 	* 
From 	AdventureWorks2012.dbo.DatabaseLog With(READPAST) 
Where 	DatabaseLogID = 1