SQL Hints: NOLOCK and NOWAIT Explained

As we know SQL Server uses pessimistic locking to protect data inconsistency. As part of its pessimistic locking strategy, it ensures data can be accessed safely, without the risk of data corruption, dirty reads or unrepeatable reads using locks. e.g. when you update any row within a transaction, locks are applied at row level which can be escalated to further levels and other transactions get 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 possible outcomes. Usually, the original statement will complete successfully, the acquired locks will be released which will unblock the blocked transactions. 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.

In ideal world we can choose a suitable isolation level and applications are designed to cope up with scenarios. However in many cases the front end applications are not designed to wait for a transaction to finish and immediately want a response back. In these cases, we can use NOLOCK and NOWAIT table hints as a temporary solution. Both the hints doesn’t hold the session in waiting state and report back to the application with completely different outcome. Let me explain both the hints in detail.

NOLOCK Hint

Nolock is the one of the famous table hints in the SELECT T-SQL statements. Although I do not recommend it but have used it many times :).

The default transaction isolation level in SQL Server is the READ COMMITTED isolation level, in which retrieving the changing data will be blocked until these changes are committed. The WITH (NOLOCK) table hint is used to override the default transaction isolation level of the table(s) within the view in a specific query session by allowing the user to retrieve the data without being affected by the locks acquired by other sessions. In this way, the query will consume less memory in holding locks against that data. In addition to that, no deadlock and blocking will occur against the queries, that are requesting the same data from that table, allowing a higher level of concurrency due to a lower footprint.

WITH (NOLOCK) table hint basically retrieves the rows without acquiring any lock and ignoring already acquired lock. In other words it helps to retrieve the data without waiting for the other queries to finish its processing. This is similar to the READ UNCOMMITTED transaction isolation level, that allows the query to see the data changes before committing the transaction that is changing it. The transaction isolation level can be set globally at the connection level using the SET TRANSACTION ISOLATION LEVEL T-SQL command or in a stored procedure.

Why should we avoid NOLOCK

To be honest using explicit table hints is not recommended and we should generally avoid. If you are using NOLOCK table hint to read uncommitted data that could be rolled back after you have read it and can lead to a Dirty read.

The NOLOCK table hint can also lead to a Nonrepeatable read. It occurs when we are trying to read the same data multiple times and the data is keep changing during these reads. In this case, you will read multiple versions of the same row.

Phantom reads can be also a result of using the NOLOCK table hint, in which you will get more records when the transaction that is inserting new records is rolled back, or fewer records when the transaction that is deleting existing data is rolled back.

The other problem may occur when transactions are moving data you have not read yet to a table that you have already scanned, or have added new pages to the location that you already scanned. In this case, you will miss these records and will not see it in the returned result.

When should we use NOLOCK

Using NOLOCK table hint is a reasonably good idea when you are reading data for non-critical application and the system uses explicit transactions heavily, which blocks the data reading very frequently. NOLOCK table hint can also be used by systems/applications that accept out of sync data, such as the reporting services. It can also be used by developers/support engineers if they are accessing data for troubleshooting some issue which does not require 100% accuracy in the data they have read.

NOWAIT Hint

Apart from NOLOCK, we can NOWAIT hint to modify the behavior of blocked queries, which can be applied to one or more tables in a query. It prevents a statement from being blocked by locks on the table. Instead, the command fails immediately when blocked, reporting an error. In  this case application does not wait for the lock to be released from other transaction. The application 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.

Let me demonstrate the use of both the hints.

Scenario 1

You application wants data back immediately and does not case about dirty reads etc. In this case we would need to use NOLOCK or READ UNCOMMITTED ISOLATION LEVEL at transaction level. I will use only NOLOCK for now.

I have executed following query in transaction and have not committed the transaction, this will mean that the query will not release the acquired lock.

Now I will run a SELECT query without NOLOCK. The query will not complete and will keep in blocked state until the lock acquired by first query will be released.

You can use sp_whoisactive or any other script to find the blocking and blocked sessions. I use separate script for blocking but here I have used sp_whoisactive.

Now I will use NOLOCK table hint to read the data immediately. We can see in following screenshot that I have read the data from whole table in few seconds and it did not wait for the first query to release the acquired lock.

Scenario 2

In this scenario, application does not want to read dirty data and also does not want to wait for other transaction to be finished. In the scenario like where you are an end user of ticketing application and you have been allotted a ticket which has been shared with multiple customers. You want to access your ticket but being modified by someone else. The event organizers do not want you to read the data when it is being modified and also do not want to hold you up. This scenario is perfect example of using NOWAIT which will not allow you to read the data and will also throw an immediate error which can be tracked and handled by front end application in form of error handling and can display an user friendly message such as ” Your booking is being modified by some other user, please try after some time”.

I have executed the same query again to acquire the lock on the table.

Now I try to read the data from the table:

We can see it immediately raised an error which can be handled by application. When this hint encounters by SQL Server it will give an error 1222 back instead of waiting for transaction on another transaction to complete. NOWAIT is very different than NOLOCK but very similar to SET SET LOCK_TIMEOUT.

Please leave your comment if you have any query or feedback. Thanks.

Advertisement