Use of Nolock Hint in Microsoft SQL Server

No lock hints to avoid locking during reading in Microsoft SQL Server

We can avoid locking with help of HINT with (NOLOCK) if we are using it for reporting purposes which will allow dirty read of data that data may be rollbacked during transactions or never exist in the database.

Example of default behavior:

Suppose one transaction is updating a table in 1st SESSION and 2nd SESSION is trying to read the same table. Then SELECT statement has to wait for the table until its transaction is committed or rollbacked.

SESSION 1: 
BEGIN TRAN
UPDATE [Person].[Address] SET City = 'DELHI' WHERE   AddressID = 70 

SESSION 2: (keep running and waiting)
select * from [Person].[Address]

Check session 2 is waiting with help of SP_WHO2 or follow query:

Check waiting of transaction for session 2:
SELECT r.session_id,st.TEXT AS batch_text,r.start_time,r.status, r.total_elapsed_time
FROM sys.dm_exec_requests AS r
CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) AS st
WHERE DB_NAME(r.database_id) = 'AdventureWorks2022'
ORDER BY cpu_time DESC;

Session 2: Select statement is waiting for Session 1 to complete its transaction, We can avoid this wait with help of NOLOCK hint

SESSION 3: Will run the same statement during transactions is running in SESSION 1.

SESSION 3:(Query return result immediate without wait for transaction to complete)
select * from [Person].[Address] with (NOLOCK)

Session 3 Output is not waiting for returns the result for transactions to complete.

Note: Nolock help in reporting for application, if we allow some time dirty reads.

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 )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.