Essentials and usage of NOLOCK hint in SQL Server

The main idea of the SQL Server locking mechanism is that it controls the consistency of transactions. According to this principle, if a process wants to perform insert, delete or update operations, SQL Server engine locks the row or rows and does not allow another process until the transaction is completed. Under some circumstances, this locking mechanism can lead to performance problems such as high concurrent process pressures. So you can experience the deadlock (Deadlock is a concurrency problem in which two transactions want to access the same data simultaneously) problems in your database. In this article, we will focus on how to avoid lock issues with the help of NOLOCK hint. First, let’s learn the main essentials and details of dirty read methodology because the NOLOCK hint can cause dirty reading.

Dirty Read: In this reading methodology, the reading process reads uncommitted data and the reading process does not care about open transactions so the locks do not lead to any issues in the reading process. As a result, this type of reading reduces the locking problems. However, the dirty reading methodology has pros and cons because dirty reading can cause inconsistency problems in the result set of the SELECT statement. As already noted, this result sets can include uncommitted transactions data, that’s why we have to consider the dirty reading when deciding to make this kind of reading.  We cannot be sure about the accuracy of rows that we make during dirty reading because these rows can be rolled back. On the other hand, this type of reading allows us to avoid locking problems and to increase the performance of SQL Server.

NOLOCK: The SQL Server default isolation level is Read Committed and in this isolation level SQL Server does not allow to read locked objects which are locked by uncommitted transactions. In addition, these locked objects can be changed according to lock escalation.

Note: In this Main concept of SQL Server locking article you can find details about locking and lock escalation.

Imagine that you have two database users and these users want to execute update and select operation against the database. A first user starts to update a particular row in the table and then the other user wants to read the same row. These two users execute the following update and select statements, which is illustrated in the below image.

In this case, user2 waits at least 10 seconds and then the transaction will be rolled back by user1, and then user2 can read the green row because the locked row will be released by user1.  This is the default behavior of the SQL Server Read Committed isolation level.

Now, we will demonstrate this case in SQL Server. First of all, we will create the FruitSales table and its rows.

CREATE TABLE FruitSales
(Id INT IDENTITY (1,1)  PRIMARY KEY, [Name] Varchar(20) ,
SalesTotal Float)
GO

INSERT INTO FruitSales VALUES
('Apple',10) ,('Orange',8), ('Banana',2)

In this step, we will open two SQL Server Management Studio query windows and execute the user1 query and then execute the user2 query.

	  ---USER1----

BEGIN TRAN 
UPDATE FruitSales SET SalesTotal =20 WHERE Id=2 
WAITFOR DELAY '00:00:10'
ROLLBACK TRANSACTION

	---USER2----
SET STATISTICS TIME ON
SELECT * FROM FruitSales WHERE Id=2

As you can see in the above image, the second query waits until the rollback of the user1 transaction.

Now, we will discuss the NOLOCK hint and usage details. The NOLOCK hint is the most popular table hint which is used by database developers and administrators to eliminate lock issues in SQL Server databases. With the help of the NOLOCK table hint, we can read locked objects (row, page or table) which are locked by open transactions. The NOLOCK hint overrides the default behavior of the SQL Server query optimizer so that the select statement can read the locked objects.

Now, we will add the NOLOCK hint to the user2 select statement and then start the user1 update and then execute the user2 select statement.

---USER1----

BEGIN TRAN 
UPDATE FruitSales SET SalesTotal =20 WHERE Id=2 
WAITFOR DELAY '00:00:10'
ROLLBACK TRANSACTION

			---USER2----
SET STATISTICS TIME ON
SELECT * FROM FruitSales WITH(NOLOCK) WHERE Id=2

In this step, we will explain how to impact the NOLOCK hint on the user2 select statement.  User1 executes the updated statement in an explicit transaction and then user2 executes the select statement and the result set returns without delay the transaction completion. This is the main idea of NOLOCK, it reads locked objects.

Now, we will focus on the result set of the select statement.  The user2 select statement retrieved the SalesTotal value 20 but the actual value of SalesTotal is still 8. Keep in mind that if you are using the NOLOCK table hint in your select statement you may face this type of inaccurate data results.

Tip: “WITH” keyword is a deprecated feature, so Microsoft recommends not to use it in your new database development and remove the “WITH” keyword in your current developments.  You can find the usage of the NOLOCK hint without “WITH” keyword.

---USER1----
BEGIN TRAN 
UPDATE FruitSales SET SalesTotal =20 WHERE Id=2 
WAITFOR DELAY '00:00:10'
ROLLBACK TRANSACTION
SELECT * FROM FruitSales WHERE Id=2

   --USER2---
SELECT * FROM FruitSales (NOLOCK) WHERE Id=2

In addition, the READUNCOMMITTED table hint is equivalent to the NOLOCK hint and we can use the READUNCOMMITTED hint instead of the NOLOCK hint.

SELECT * FROM FruitSales (READUNCOMMITTED) WHERE Id=2

Even so, there is a particular case about the NOLOCK hint that can’t pass the locking barrier.  If there is any process altering a table, the NOLOCK hint can’t overcome this type of lock and can’t continue the reading operation.  The reason for this problem is that the NOLOCK hint acquires the Sch-S (schema stability) lock and the ALTER TABLE statement acquires the SCH-M (schema modification) lock, so a conflict occurs.

At first, we will learn the Object_Id of FruitSales table with the help of the following query.

select OBJECT_ID('FruitSales')

Run the following user1 query and then run the user2 query. As a result, the user2 query will delay completion of the user1 table alter process.

--USER1---
BEGIN TRAN
ALTER TABLE FruitSales
ADD ColorofFruit varchar(200) 
WAITFOR DELAY '00:00:35
GO
COMMIT TRAN

   --USER2---
SELECT * FROM FruitSales (NOLOCK) WHERE Id=2

Open the new query window and execute the following query. This query will help to find out the lock type of the user1 and user2 queries.

SELECT Resource_type,
	 Resource_database_id,
	 Resource_description,
	 Resource_associated_entity_id,
	 Resource_lock_partition,
	 Request_mode,
	 Request_type,
	 Request_status,
	 Request_session_id,
	 Request_request_id,
	 Request_owner_type,
	 Request_owner_id,
	 Lock_owner_address
FROM sys.dm_tran_locks
where resource_associated_entity_id =647673355

Now, we will check out the lock compatibility matrix for SCH-M and SCH-S interaction. The matrix describes that SCH-M and SCH-S interaction causes a conflict.

Conclusion

In this article, we mentioned the dirty reading process and NOLOCK hint. Using the NOLOCK hint is an effective method to read a locked page but also it has some advantages and disadvantages. For this reason, you have to consider the NOLOCK hint before using it.

References

SQL Server Transaction Locking and Row Versioning Guide

Hints (Transact-SQL) – Table

SET TRANSACTION ISOLATION LEVEL (Transact-SQL)