Join the social network of Tech Nerds, increase skill rank, get work, manage projects...
 
  • What is NOWAIT in SQL Server

    • 0
    • 0
    • 0
    • 0
    • 0
    • 0
    • 0
    • 0
    • 722
    Comment on it

    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

     

 0 Comment(s)

Sign In
                           OR                           
                           OR                           
Register

Sign up using

                           OR                           
Forgot Password
Fill out the form below and instructions to reset your password will be emailed to you:
Reset Password
Fill out the form below and reset your password: