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

Blocking in SQL Server

In this article I am trying to explain the Blocking.

What is it

Blocking in SQL Server is a scenario where one connection to SQL Server locks one or more records, and a second connection to SQL Server requires a conflicting lock type on the record or records locked by the first connection. This causes the second connection to wait until the first connection releases its locks. By default, a connection will wait an unlimited amount of time for the blocking lock to go away. 

A certain amount of blocking is normal and unavoidable. But too much blocking can cause connections (representing applications and users) to wait extensive periods of time, hurting overall SQL Server performance.

Type of Blocking

1.     Short-time Blocking - average duration less than three seconds. They do not create problems.

2.     Mid-time Blocking - average duration ranges from 3 seconds to a maximum of 10 seconds. Usually do not create problems

3.     Long-time Blocking - average duration more than 10 seconds. Require attention.

Monitoring Blocking

Sysprocesses is the system table that can reveal the most clues about why a process is blocking others. The sysprocesses table holds information about processes running on Microsoft SQL Server. These processes can be client processes or system processes. Sysprocesses is stored in the master database.

SELECT 'Server : ' + rtrim(@@servername) + ',  SPID=' +

        rtrim(convert(char(6),p.spid))+',  STATUS='+

        rtrim(convert(char(15),IsNull(p.status, ' ')))+'  , LOGIN NAME='+

        rtrim(convert(char(25),IsNull(p.loginame, ' ')))+'  ,HOST NAME='+

        rtrim(convert(char(15),IsNull(p.hostname, ' ')))+'  ,PROGRAM NAME='+

        rtrim(convert(char(30),IsNull(p.program_name, ' ')))+'  ,COMMAND='+

        rtrim(convert(char(25),IsNull(p.cmd, ' ')))+', LOGIN TIME='+

        rtrim(convert(char(19),IsNull(p.login_time,'1900-01-01'),121))+' ,LAST BATCH='+

        rtrim(convert(char(19),IsNull(p.last_batch,'1900-01-01'),121))

FROM master..sysprocesses p

GO

To avoided unnatural blocking

1.    Keep the transactions as short as possible. We must take care the transaction time by optimize Transact-SQL code, optimize indexes, break long transactions into multiple, smaller transactions, avoiding cursors, etc.

2.    In any cases if the running query is cancelled it is important that the code also roll back the transaction. If this does not happen, locks held by the query will not be released, which means blocking can occur.

3.    Avoid INSER, UPDATE or DELETE large numbers of records in a single transaction.

4.    Add query time-out for each of the query.

5.    Add lock time-out for each of the query.

Using SET LOCK_TIMEOUT

Specifies the number of milliseconds a statement waits for a lock to be released. This is the number of milliseconds that will pass before Microsoft SQL Server returns a locking error. A value of -1 (default) indicates no time-out period (that is, wait forever). When a wait for a lock exceeds the time-out value, an error is returned. The value of "0" means not to wait and return a message as soon as a lock is encountered.

SET LOCK_TIMEOUT 1800

GO

Hope you like it.

Posted by: MR. JOYDEEP DAS