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.

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

About these ads

7 thoughts on “SQL SERVER – Difference Between NOLOCK and NOWAIT Hints

  1. Hello Sir, Please Tell me how to take differential backup in sql server , Please give me commands, I have SQL Backup and Ftp but I want to take using command day by day basis

    Like

  2. Pinal,
    Correct me if i am wrong !!
    Below is the scenario you described
    a) You deleted record from table
    b) Executed query with NOWAIT and got error..That looks fine
    c) Executed query with NOLOCK and it returned no record / error and that looks fine as we already deleted record from table.

    So, my question is with NOLOCK we are getting correct result..right?

    Am i missing something….

    Varun

    Like

    • yes, because the first transaction is not committed yet. you can rollback that transaction that means you still have single row in the table versus query with NOLOCK said zero rows

      Like

  3. @varun Khanna,

    The NOLOCK result is a dirty read..

    The transaction is occuring and not occured,if you see the SQL query properly Transaction is opened and not ended.

    suppose the transaction fails and is rolled back, the delete will not occur and the record will be in the table

    Pinal sir, correct me if i am wrong

    Regards,
    Varun

    Like

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