With(NOLOCK) Vs With(NOWAIT)

Introduction

We all know about dirty reads and it is not good for transaction table specially OLTP environment.

But sometimes customer wants like we are going to fire the query from frontend, if the table is locked by other transaction it just simply return a message. We don’t want any dirty read or not want to wait until the Transaction finish. If necessary, we again fire the query after sometime to get the result.

Before jumping into this type of solution we are going to show something related to dirty read.

Scenario

We have table named customer.

CREATE TABLE tbl_CustomerDetails

       (

              CustID        Int           NOT NULL IDENTITY Primary Key,

              CustName      Varchar(50)   NOT NULL,

              ContactNo     Varchar(12)   NOT NULL

       );

GO

INSERT tbl_CustomerDetails

       (CustName, ContactNo)

VALUES('Joydeep', '123456'),

      ('Deblina', '897654');

We are trying to update this customer table from session-1 and in the same time we are trying to retrieve record from customer table in session-2.

Session -1

BEGIN TRAN

   UPDATE tbl_CustomerDetails

      SET ContactNo = '99999'

   WHERE CustID = 1;

Please note that we are not providing any Commit or Rollback Transaction option in Session -1.

Now try to retrieve the records from session -2

Session -2

SELECT * FROM tbl_CustomerDetails;

Here we are not getting any output, until the Session -1 is committed or roll backed. Here the update statement of Session – 1 is just make lock on table object.

So it is not going to solve our solutions.

Now, to get the result.

Session -2

SELECT * FROM tbl_CustomerDetails With(NOLOCK);

Here we are using With(NOLOCK) keyword to get the dirty data.

CustID      CustName                                           ContactNo

----------- -------------------------------------------------- ------------

1           Joydeep                                            99999

2           Deblina                                            897654

(2 row(s) affected)

Now think about a situation.

In Session -1 we are using Rollback Transaction after getting the result in Session -2

Session – 1

ROLLBACK TRAN

So, what we get in the Session -2 is not correct data. That is called dirty read.

Now, it is not going to solve our problem as customer doesn’t want to get dirty records.

To solve this problem we are using With(NOWAIT) key word.

Session – 2

SELECT * FROM tbl_CustomerDetails With(NOWAIT);

CustID      CustName                                           ContactNo

----------- -------------------------------------------------- ------------

Msg 1222, Level 16, State 51, Line 1

Lock request time out period exceeded.

It is not waiting or not giving any dirty data. It just simply through an error message saying Lock request time out. It means Session-1 provides a lock on this table and for that it is unable to retrieve data.

So, we have to wait and fire the query again until the Session -1 finishes his transaction.

It is definitely solve our problem statement.

Hope you understand it.

Posted by: MR. JOYDEEP DAS