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