What are they, Readpast and Nolock?

Question

What is READPAST and NOLOCK?

I’ve seen quite a lot of the use of NOLOCK, but the READPAST I saw the use now, practically the same way, ie.

FROM dbo.table t WITH(READPAST) 

and

FROM dbo.table t WITH(NOLOCK)

But what is the difference between them? And when should we use one or the other?

Answer 1

These clauses are tips for the optimizer of darlings meet some demand better, in general should only be used when it is certain that it needs a semantics of access to data different from the standard. It can have disastrous consequences if you don’t know what you’re doing.

• NOLOCK - ignores the locking of lines that are being updated in another transaction and access to data even in potentially inconsistent state. So you can take inconsistent data.
• READPAST - ignores only hangings made on pages, but not hangings on the lines. It tends to pick up inconsistent data but the chance is less.

Documentation.

Answer 2

Compare SQL Server NOLOCK and READPAST Table Hints

TL;DR

CREATE TABLE TESTE ( ID INT NOT NULL PRIMARY KEY, VALOR CHAR(1) )
INSERT INTO TESTE(ID,VALOR)
VALUES (1,'A'),(2,'B'),(3,'C')

Scroll this instruction in a window of SQL

BEGIN TRANSACTION Transaction1
DELETE FROM TESTE
WHERE ID=1

UPDATE TESTE
SET VALOR= 'D' 
WHERE VALOR='C'

INSERT INTO TESTE(ID, VALOR)
VALUES(4,'E'),(5,'F') GO WAITFOR DELAY '00:00:05'
ROLLBACK

In another window turn as well

SELECT * FROM TESTE WITH (NOLOCK)

The result will be:

After completing the first transaction, run select again see that the result has changed.

Could tell the difference?

When using the tip from NOLOCK not only is it possible to read modified data but also to read incorrect data, which is the result of changes in the physical location of the data made by other transactions. (As shown in the example above)

Switching to READPAST

SELECT * FROM TESTE (READPAST)

Upshot:

This is because this is the only line that has not been modified by Transaction1

CONCLUSION

NOLOCK allows reading of dirty lines (lines that are being used by other transactions) and may cause consistency problems during Table/Index Scan. NOLOCK can greatly improve performance(Como usar SELECT WITH NOLOCK para melhorar a Performance?), but be careful with that, see the Efeitos colaterais do WITH (NOLOCK) – Parte I

READPAST read lines that are not being used by other transactions.

I recommend reading:

• _{What are Begin, Commit and Rollback transactions?}
• _{What is Deadlock in SQL Server?}