Today i will discuss one interesting topic NOWAIT in SQL Server and would explain  how to use it.

As we know about the transaction in SQL it is unit of work which is to be executed as whole. Suppose we are updating some records in a table in transaction and before the transaction commit if anyone want to access the table it will not be able to do so because table is already locked in transaction. User can only access the table when the transaction is committed. If the transaction is not committed till particular time user would get the TIMEOUT exception in accessing records. Now question arises is there any mechanism in which i can know immediately that this table is locked or DB is busy,so you should retry after some time instead of waiting for result ? the answer of this question is YES here NOWAIT would do the job. 

I used the below script to explain 

CREATE TABLE [dbo].[Product]
(   
 [ProductId] INT IDENTITY(1,1),
 [ProductName] [nchar](50) NULL,
 [ProductDescription] [nchar](3000) NULL,
 [ProductPrice] MONEY NULL
)

I have some dummy records in this table 

now i am updating the Product table in transaction which will put the exclusive lock on the Product table

As we can see Product table in locked so now its not possible to access the data till than transaction is not committed but if we use NOWAIT it will immediately raise TIMEOUT exception please see it below image