SQL SERVER – Difference Between NOLOCK and NOWAIT Hints

It is interesting to see how a blog evolves with the time and user interacts with each blog post. Earlier I wrote two of the blog posts on NOWAIT and SET LOCK_TIMEOUT. I have received very good response on this subject. Please read following two blog posts before continuing this blog post.

• Basic Explanation of Query Hint NOWAIT – How to Not Wait on Locked Query
• Basic Explanation of SET LOCK_TIMEOUT – How to Not Wait on Locked Query

Here is the follow up question which I received from reader after reading above blog posts.

“I now understand that NOWAIT hints do not return result and errors out if there is a lock on the table. Does this mean I should use the NOLOCK hint to retrieve the results?”

Well, here is the answer – the behavior of NOLOCK and NOWAIT is absolutely opposite to each other but there is no guarantee to get the valid answer in either of them.

NOWAIT will return error if the original table has (transaction) locked on it.
NOLOCK will read the data irrespective of the (transaction) lock on it.

In either case there can be incorrect data or unexpected result. There is no guarantee to get the appropriate data.

Here is the example of the how both provide different result on similar situation.

In this sample scenario we will create a table with a single row. We will open a transaction and delete a single row. We will now close the transaction and read the query with once with NOLOCK hint and once with NOWAIT hint. After the test we will rollback original transaction, which was deleting the record. As we will rollback transaction there will be no change in resultset however, we will get very different results in the case of NOLOCK and will get error in case of NOWAIT.

First Let us create a table:

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

Now let us open three different connections.

Run following command in the First Connection:

BEGIN TRAN
DELETE FROM First
WHERE ID = 1

Run following command in the Second Connection:

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

Run following command in the Third Connection:

SELECT ID, Col1
FROM First WITH (NOLOCK)
WHERE ID = 1

You can notice that result is as discussed earlier. There is no guarantee to get 100% correct result in either case. In the case of NOLOCK and will get error in case of NOWAIT. If you want to get the committed appropriate result, you should wait till the transaction lock on the original table is released and read the data.

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