Use of NOWAIT in SQL server

Today I will discuss on the very interesting topic NOWAIT in Sql server. How to use this and where to use NOWAIT in real time.

You know about transaction. Suppose when you update some records in my table with transaction. At the same time before commit the transaction if user want to fetch some related records and run query. It will not return any row and your query will be running continuously till your transaction commit.

Now,

Think at Application level .Suppose you are working on any Bank website . When you updating balance of any customer and at the same time if user try to check his account balance online . He will wait to see his balance till your updation work not commit successfully. I think this is not a good practice . At this time a Message should throw at the user end.

"Your account is on updation work .Kindly check after some time."

In the above scenario you can use NOWAIT. It will return a message instantly if your table is busy in transaction .it will give you following error

Msg 1222, Level 16, State 45, Line 1

Lock request time out period exceeded.

Lets see Query Example

Suppose I have some records in mProduct table and I have to delete those products which is in brandid 3.

begin tran
delete from mProduct where BRANDID =3

At the same time if any other user fetching records of brandid 3.

select * from mProduct where BRANDID=3

Query will process till transaction kill.

Go through with below query

select * from mProduct WITH(NOWAIT) where BRANDID=4

It will return Msg 1222 instantly.

At application level when you read error if it is 1222 you can throw a user defined message.

"Your account is on updation.".

Thank you for reading this article.

 

Up Next
    Ebook Download
    View all
    SQL Jobs
    Read by 0 people
    Download Now!
    Learn
    View all