SQL Server Table hints(NOLOCK) help override the default behavior of the query optimizer during the SQL query execution. It is accomplished by enforcing a specific locking method, a specific index or query processing operation, such as an index seek or table scan, to be used by the database query optimizer to build the query execution plan.
Developers frequently use the WITH(NOLOCK) hint heavily in the select T-SQL statements. SQL Server default isolation level – READ COMMITTED, blocks transactions to view change data until changes get to commit. SQL Server acquires a shared lock before reading data from a specified table in the default behavior. It ensures that you are reading the committed data.
The SQL Server Table hint WITH(NOLOCK) overrides SQL Server default isolation level behavior and allows users to retrieve uncommitted data without affecting the locking mechanism on the required data. Therefore, the NOLOCK table hint gets data without waiting for other queries' completion, that is reading and modifying data. It is similar to the SQL Server READ UNCOMMITTED transaction isolation.
Implications of WITH(NOLOCK) hint
As you know, the SQL Server Table hint - NOLOCK to read the uncommitted data. Therefore, if the transaction modifying data is rolled back after reading it using NOLOCK, you might accidentally read uncommitted dirty data.
Note: The WITH (NOLOCK) table hints can be used only with the SELECT statements. You cannot use it with UPDATE, INSERT, DELETE or MERGE statements
Let's understand the practical implementation of WITH(NOLOCK) table hints.
The following query returns the Name and Contact number for the person having the first name as Natalie and Lastname as Cooper.
SELECT [FirstName]+' '+[MiddleName] +' ' + [LastName] as PersonName
,pp.PhoneNumber
FROM [AdventureWorks2019].[Person].[Person] per
Join [AdventureWorks2019].[Person].[PersonPhone] pp
on per.BusinessEntityID=pp.BusinessEntityID
where Per.FirstName='Natalie' and Per.LastName='Cooper'
Suppose Natalie wants to change the phone number. Therefore, it started a transaction to update it.
BEGIN TRANSACTION
Update PP
set PP.[PhoneNumber]='234-577-0001'
FROM [AdventureWorks2019].[Person].[Person] per
Join [AdventureWorks2019].[Person].[PersonPhone] pp
on per.BusinessEntityID=pp.BusinessEntityID
where Per.FirstName='Natalie' and Per.LastName='Cooper'
Note that we have not committed or rollback the update transaction. If any user tries to check the phone number for Natalie, his session is blocked.
As shown below, session-id 53 is blocked by 62.
Session ID 53: It is for the Select statement that needs to fetch the phone number for Natalie
Session ID 62: Update statement (BEGIN Transaction)
The following query shows the locking information. The SPID 53 (row 6) shows the request_status as waiting.
select request_session_id,resource_type,
request_mode,request_type,request_status,
request_owner_type from sys.dm_tran_locks
where request_session_id in(53,62)
The query returns the uncommitted data if we use the WITH (NOLOCK) statement. Therefore, we get Natalie's phone number as 234-577-0001.
If the update transaction is rolled back, the select statement returns the different (old) phone number.
BEGIN TRANSACTION
Update PP
set PP.[PhoneNumber]='234-577-0001'
FROM [AdventureWorks2019].[Person].[Person] per
Join [AdventureWorks2019].[Person].[PersonPhone] pp
on per.BusinessEntityID=pp.BusinessEntityID
where Per.FirstName='Natalie' and Per.LastName='Cooper'
ROLLBACK Transaction
What is the difference between NOLOCK and WITH(NOLOCK) SQL Server Table hints
The NOLOCK and WITH(NOLOCK) table hints functionalities are the same. However, it is recommended to use WITH(NOLOCK) is required due to the following reasons.
Omitting the WITH KEYWORD is a deprecated feature: You should not omit WITH Keyword, as per the Microsoft docs.
You can specify multiple table hints using the WITH Keyword: Sometimes, we use multiple table hints together. You can specify multiple table hints in a WITH clause separated by a comma, as shown below.
SELECT [FirstName]+' '+[MiddleName] +' ' + [LastName] as PersonName
,pp.PhoneNumber
FROM [AdventureWorks2019].[Person].[Person] per WITH (TABLOCK, HOLDLOCK)
Join [AdventureWorks2019].[Person].[PersonPhone] pp WITH (TABLOCK, HOLDLOCK)
on per.BusinessEntityID=pp.BusinessEntityID
where Per.FirstName='Natalie' and Per.LastName='Cooper'SQL Server might treat NOLOCK as a table alias
Suppose you forgot to add parenthesis for the NOLOCK table alias. SQL Server might treat it as a table alias. For example, we do not get any records for the following query as an update statement in another transaction blocking it. Here, we do not see any NOLOCK hint impact.
As shown below, the request status is WAIT.
You get the following error message if you have used table alias and NOLOCK SQL Server Table hint without parenthesis. However, as stated earlier, always use WITH(NOLOCK) table hint, if required.
Note: SQL Server Query Optimizer selects the best execution plan for a query. Therefore, we recommend always using SQL Server Table hint only as a last resort.
