Nowait Table Hint in SQL Server

The nowait hint in SQL Server instructs the query to immediately return an error if a lock can not be obtained on a table, rather that waiting for the lock to be release. This is useful when we want to avoid blocking and prefer to handle lock contention error programmatically. If a required lock (shared, update or exclusive) is not immediately available SQL Server return error 1222 (Lock request time out period exceeded).

 SQL server uses locks to ensure that the data in our table can be accessed, without risk of corruption or dirty reads. Let say when we are inserting or updating records within transaction then our 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 we 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 the example

Creating a table Nowait_Table_Hint_Demo for the demo

create table Nowait_Table_Hint_Demo ( Nowait_Table_Hint_Demo_id bigint identity(1,1) primary key, column_one int  null, column_two int null )

 

Table created successful. Now in one window we are using transaction and inserting record into this table and not yet committed it meanwhile in other window we are running the select statement without any table hint. Let see

BEGIN TRAN   INSERT INTO Nowait_Table_Hint_Demo(column_one,column_two)      VALUES (1,200)   --ROLLBACK --COMMIT TRAN

 

Transact is not yet committed or rollbacked.

See select statement is still running.

If we use nolock then we will get the dirty read (un committed data)

Let’s use nowait and see

select * from Nowait_Table_Hint_Demo(nowait)

It will return error. Error code is 1222. This will be handled in application.

Once we commit the transaction, we will not get this error. No committing the transaction.

Now we got the result.