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.
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:
Now let us open three different connections.
Run following command in the First Connection:
Run following command in the Second Connection:
Run following command in the Third Connection:
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.
