NOLOCK and READPAST Table Hint in SQL Server

Table Hint:

We use Table Hints to override the default behaviour of the query optimizer for the duration of the data manipulation language (DML) statement by specifying a locking method.

Table hints are specified in the FROM clause of the DML statement and affect only that particular table.

Syntax: Select column_name1,column_name2..,column_namen from Table_name with(Table_Hint)

In this blog post will talk only about NOLOCK and READPAST. 

NOLOCK:

This table hint NOLOCK is also known as READUNCOMMITTED, is applicable to SELECT statements only. NOLOCK indicates that no shared locks are issued against the table that would prohibit other transactions from modifying the data in the table.

This increases concurrency and performance of the transaction because the database engine does not have to maintain the shared locks involved. The problem with NOLOCK hint is DIRTY READ, because the statement does not issue any locks against the tables being read, some "dirty," uncommitted data could potentially be read.

A "dirty" read is one in which the data being read is involved in a transaction from another connection. If that transaction rolls back its work, the data read from the connection using NOLOCK will have read uncommitted data. This type of read makes processing inconsistent and can lead to problems. 

READPAST

READPAST is less popular than NOLOCK. After specifying this hint into the query Database Engine do not read rows that are locked by other transactions. When READPAST is specified, row-level locks are skipped. That is, the Database Engine skips past the rows instead of blocking the current transaction until the locks are released.

The advantage of READPAST is that dirty reads will not happen because the hint will not return locked records. The problem with this is that you will not get all your records, because locked rows will be skipped.

Let see the same with a sample database that is “DEMO”.

USE master

GO

CREATE DATABASE DEMO

GO

USE DEMO

GO

CREATE TABLE NUMBER(ID INT, NAME CHAR(3))

GO

INSERT INTO NUMBER VALUES(1,'XXX'),

(2,'YYY'),

(3,'ZZZ'),

(4,'AAA'),

(5,'BBB')

 GO

SELECT * FROM NUMBER;

The following transaction will show how NOLOCK and READPAST behaves.  In the script below, I begin a transaction and updated a record in the NUBER table as below:

BEGIN TRAN

UPDATE NUMBER SET NAME='TIM' WHERE ID =1

Now open another session and execute the below script and see the OUTPUT for same:

SELECT * FROM NUMBER WITH(NOLOCK);

From the output you can see that the name for ID is TIM. The data for ID 1 is inconsistent. This is DIRTY READ. What if the update transaction is roll backed, well in that case the select statement read the data which never existed in database.

Now lets see that how READPAST will behave in this situation:

SELECT * FROM NUMBER WITH(READPAST);

From the output you can see that the record  for ID 1 is missing. As this row is locked by some other transaction. So READPAST table hint skipped that row.

Rollback the above update transaction. Now, Clean the database "DEMO" as:

Use master

Go

Drop Database Demo

Go

Thanks For Reading this Post!!!