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 (https://blog.sqlauthority.com)