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.