Published on

January 5, 2013

Understanding NOWAIT in SQL Server

When it comes to querying data from a table in SQL Server, there are various techniques and hints that can be used to optimize performance and handle locking scenarios. One commonly known hint is NOLOCK, which allows reading of uncommitted data. However, there is another hint called NOWAIT that serves a different purpose.

In a recent presentation at the Bangalore User Group, I received a question from an attendee about handling locked tables in SQL Server. The attendee wanted to know if there was a mechanism besides NOLOCK that could inform the application when a table is locked under another transaction, so that the application can either return immediate results or notify the user to retry after a certain amount of time.

I suggested using NOWAIT or SET LOCK_TIMEOUT as alternatives to NOLOCK. NOWAIT is a table hint that instructs the database engine to return a message as soon as a lock is encountered on a table. This allows the application to handle the situation accordingly.

Let’s take a look at an example to understand how NOWAIT works:

USE tempdb
GO

CREATE TABLE First (
    ID INT,
    Col1 VARCHAR(10)
)
GO

INSERT INTO First (ID, Col1)
VALUES (1, 'First')
GO

-- Connection 1
BEGIN TRAN
DELETE FROM First WHERE ID = 1

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

In the above example, we have two different connections. Connection 1 performs a DELETE operation on the “First” table, while Connection 2 tries to SELECT data from the same table using the NOWAIT hint. As soon as Connection 2 executes the query, it encounters a lock on the table and returns the following error:

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

The error message indicates that the lock request exceeded the specified time out period. This is because we used the NOWAIT hint, which causes SQL Server to return an error instead of waiting for the transaction in Connection 1 to complete.

It’s important to note that NOWAIT is different from NOLOCK, but it is similar to SET LOCK_TIMEOUT. In future blog posts, we will explore how SET LOCK_TIMEOUT works and its flexibility compared to NOWAIT.

Understanding the different hints and techniques available in SQL Server can greatly improve the performance and responsiveness of your applications. By utilizing NOWAIT or SET LOCK_TIMEOUT, you can handle locked tables more efficiently and provide a better user experience.

If you have any questions or would like to share your thoughts on this topic, please leave a comment below. I look forward to hearing from you!

Click to rate this post!
[Total: 0 Average: 0]

Let's work together

Send us a message or book free introductory meeting with us using button below.