Table Hints – NoLock vs ReadPast


When any data in a database is read or modified, the database engine uses special type of mechanism, called locks, to maintain integrity in the database. Locks will be used to make sure the transaction consistency.

NoLock Table Hint
– Will allow you to read the uncommited data
– Only used with SELECT statement
– Blocking will not occur
– Will reduce the concurrency and improve the performance at some extent
– Risk of doing Phantom reads

Let’s create table for the NOLOCK and READPAST hint demo

create table tranDemo
(
			id int identity(1,1),
			name varchar(10)
)

insert into tranDemo values ('Jugal')
insert into tranDemo values ('Nehal')

–Now let’s update the values by specifying the explicit transaction and don’t commit/rollback the transaction

begin transaction
   update tranDemo
   set name = 'DJ'
   where name = 'Jugal'

Now open new query window and execute the below query and you will notice query will not return data and will continue running as it is blocked

select * from trandemo

Now open new query window and execute the below query to check the blocking, you can see the blocking SPID in the result set

sp_who2 active

Now open new query window and execute the below query using NOLOCK hint and it will return data, yet transaction is not committed but it will return the updated value.

select * from trandemo(NOLOCK)

READPast Table Hint: Less commonly used table hint than NOLOCK. This hint specifies that the database engine not consider any locked rows or data pages when returning results.
– Will only read the commited rows which are not locked
– Blocking will not occur
– Only used with SELECT statement
– Will reduce the concurrency and improve the performance at some extent
– Result set returned by this hint is not perfect as it will not retun the locked rows or pages, so you can not make any decision based on data

Now open new query window and run the below query it will return only one record (“Nehal”) which is not locked or modified

select * from trandemo(ReadPast)
Advertisements

Thanks for the comment, will get back to you soon... Jugal Shah

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

WordPress.com Logo

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