SQL 2005+

SQL Server NOWAIT Hint

by Richard Carr, published at http://www.blackwasp.co.uk/SqlNoWait.aspx

In some situations it can be necessary to fail quickly when a query is blocked, rather than wait for locks to be released. This can be achieved simply using the NOWAIT hint.

Table Hints

Table hints allow you to modify the behaviour of Transact-SQL (T-SQL) statements and queries. You add them to the names of tables in your commands to change the way in which those tables are accessed. In most cases they are unnecessary but sometimes they can help improve performance or change the way that commands are affected by locks.

NOWAIT Hint

To ensure that data in a database can be accessed safely, without the risk of data corruption, dirty reads or unrepeatable reads, SQL Server uses locks. For example, when you are updating information within a transaction, locks are applied and other transactions are blocked from reading the changed data. This stops the second transaction making decisions that are based upon changes that could be rolled back.

When queries are blocked, there are two usual outcomes. In many cases, the original statement will complete successfully, the locks will be released and the blocked transactions will become unblocked and continue executing. In some cases, two or more transactions will block each other, causing a deadlock. In this situation, SQL Server will terminate and roll back one of the transactions to allow the other to execute.

One way to modify the behaviour of blocked queries is with the NOWAIT hint, which can be applied to one or more tables in a query. It prevents a statement from being blocked by locks on the named table. Instead, the command fails immediately when blocked, reporting an error. The code that executed the statement can then decide what further action to take, such as trying the query again later or reporting to the user that the action cannot be taken.

NOWAIT Example

Let's demonstrate the use of NOWAIT with a simple table and some basic queries. In a test database, run the following script to create a table and populate it with data.

CREATE TABLE People
(
    Name VARCHAR(50)
)

INSERT INTO People VALUES ('Bob')
INSERT INTO People VALUES ('Jim')
INSERT INTO People VALUES ('Sam')

Open two query windows in SQL Server Management Studio. We'll use each one to create a separate transaction and introduce some locks. In the first query window, execute the code shown below. This starts a new transaction and modifies all of the rows in the table. As the transaction is not committed or rolled back, all of the rows are locked.

BEGIN TRAN

UPDATE People
SET Name = UPPER(Name)

In the second query window, run the query below to attempt to read the data from the People table. This query attempts to read locked data so is blocked. The query cannot complete so will show as in progress indefinitely.

SELECT * FROM People

Switch back to the first window and roll back the transaction. The updates will be cancelled and the locks released. If you switch again to the second query window, you'll see that the unmodified rows have been selected and displayed.

ROLLBACK

We can now repeat the process using the NOWAIT hint. In the first query window, execute the code. Again, the rows in the table will be locked. Now switch to the second window and modify the query to add the NOWAIT hint, as shown below:

SELECT * FROM People WITH (NOWAIT)

When you run the changed query, it is unable to read the locked data. Instead of being blocked and waiting for the locks to be released, the query immediately terminates, showing the following error message.

Lock request time out period exceeded.