SQL SERVER – Basic Explanation of Query Hint NOWAIT – How to Not Wait on Locked Query

Everybody knows about NOLOCK  but not everyone knows about NOWAIT. They are different and they have an entire different purpose. In this blog post we will not talk about NOLOCK but we will see how NOWAIT will work. The idea of writing about blog post is based on the question I received in recent Bangalore User Group presentation. Here is the quick conversation with one of the attendee I had after my presentation. I did not ask the name of the attendee so I will have to address him as an attendee here. If you are reading this blog post, please let me know your name and I will replace it with your name.

Attendee: Pinal, in SQL Server when we have one query under transaction, when we have to read the data from the table we use NOLOCK, which gives us dirty data.

Pinal: Yes, that is correct.

Attendee: In our application we do not want to wait for the query to execute, as the way the application we have built, we have to either return the results immediately or inform user that they should retry after a while as an application (database) is busy right now. Is there any other mechanism besides NOLOCK which we can use which inform us that the table from which we are trying to retrieve the data is locked under other transaction. This way, we can pass the same message to the user and they can re-attempt to fetch the data after a specific amount of the time.

Pinal: So, basically you do not want to use NOLOCK as it gives you dirty data and you do not want to wait also till the tables under under transactions is available. You rather prefer error instead or message that your query is not going to execute immediately.

Attendee: Yes, absolutely correct.

Pinal: Use NoWait or SET LOCK_TIMEOUT.

Attendee: I have never used either of them, do you have an example on your blog?

Pinal: No, I have not blogged about it but I will for sure blog how they work.

I hope the above conversation also explains the real world scenario and business need of such feature.

NOWAIT is table hint and it instructs the database engine to return a message as soon as a lock is encountered on a table. Let us see an example.

First Create the following table in the database:

USE tempdb
GO
CREATE TABLE First (ID INT, Col1 VARCHAR(10))
GO
INSERT INTO First (ID, Col1)
VALUES (1, 'First')
GO

Now open two different connections:

Connection 1: Run following query

BEGIN TRAN
DELETE FROM
First
WHERE ID = 1

Connection 2: Run following query

BEGIN TRAN
SELECT
ID, Col1
FROM First WITH(NOWAIT)
WHERE ID = 1

As soon as you run above query in connection 2 it will give you following error:

Msg 1222, Level 16, State 45, Line 2
Lock request time out period exceeded.

The reason is because we have used NOWAIT as a query hint. When this hint is encountered by SQL Server it will give an error 1222 back instead of waiting for transaction on another window to complete. As I said NOWAIT is very different than NOLOCK but very similar to SET SET LOCK_TIMEOUT. In future blog posts, we will see how SET SET LOCK_TIMEOUT works. The matter of fact SET SET LOCK_TIMEOUT is more flexible than NOWAIT and I often use it.

Reference: Pinal Dave (http://blog.sqlauthority.com)

About these ads

11 thoughts on “SQL SERVER – Basic Explanation of Query Hint NOWAIT – How to Not Wait on Locked Query

  1. Nice post . This is similar to Oracle. in Oracle, In For update nowait the rows will busy and acquire lock until any commit or rollback is executed. Other session try to aquire the lock will
    get a oracle error message like ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired instead of waiting the lock to release.
    Session1:
    SQL>select * from dept where deptno =10 for update nowait;

    Session2:
    SQL>select * from dept where deptno =10 for update nowait;
    ERROR at line 1:
    ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired.

    That means NO_WAIT Specifies that if the changes in the database cannot complete immediately without waiting for transactions to commit or roll back on their own, then the request will fail.

  2. hi I want design form in access and source for form is two table and these tables existed in sql server 2008 please answer me

    ________________________________

  3. Pingback: SQL SERVER – Basic Explanation of SET LOCK_TIMEOUT – How to Not Wait on Locked Query « SQL Server Journey with SQL Authority

  4. Pingback: SQL SERVER – Difference Between NOLOCK and NOWAIT Hints « SQL Server Journey with SQL Authority

  5. hi pinal i have used same query in first window with small change like
    BEGIN TRAN
    DELETE FROM First
    WHERE ID = 1
    COMMIT

    but after running this am executing same code in second window but still getting same errror..any comments on this

  6. Dear Pinal Sir,

    Myself Rohan,i take help of your blog posts whenever i get any new challanges in sql and i get solution.I am not sure whether my question on above post is logical or not ?

    I would like to ask that when NOWAIT is used, database engine gives an error msg of “Msg 1222, Level 16, State 45, Line 2
    Lock request time out period exceeded.” in sql query editor window but how user will be intimated about this error in application level ?

    I am in situation where NOWAIT is not used in query and i can see deadlock in DB but if i modify the query or set LOCK_TIMEOUT time then how an user will be informed ?

    Thanks,
    Rohan

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