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
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.
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.