NOWAIT hint in SQL Server

Today, I was reading about Table Hint in MSDN for other reason and came across this “NOWAIT” hint. In today’s post we will learn about NOWAIT hint. Well, SQL server uses locks to ensure that the data in your table can be accessed, without risk of corruption or dirty reads. Let say when you are inserting or updating records within transaction then your table are locked and other transaction have to wait to read or change the records.

Sometime we are in situation that when our application request for data and if our request is locked by SQL server then rather than wait for some interval, its better to move out. So in that type of situations “NOWAIT” hint comes into the picture. It means when you apply this hint and requesting for select records from the table, the command fails immediately if current request is blocked and then reporting an error.

Let’s see this practically –

Open two query window in your SQL Server Management Studio. Assume that you have a table named Products and you tried to insert some records within one transaction as shown below in query window 1.

BEGIN TRAN

INSERT INTO Products
     VALUES (1, 'iPhone 5s', 55200)

--ROLLBACK
--COMMIT TRAN

To create the blocking situation, i have not committed transaction in above query

Query_Window_1

Now in second query window if you fire select statement command without any hint then you have to wait for infinite interval of time as our above transaction is not completed. But what happen if you fire select command with “NOWAIT” hint like below –

SELECT * FROM Products WITH (NOWAIT);

Above query results in an error as shown below.

Query_With_NoWaitHint

And you have to now manage the exception in your application and modify the code accordingly. Hope this post may help you in future.

That’s it for now folks. Hope you like this post.

Advertisements

Leave a Reply

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