Explore WITH(NOLOCK)and the difference between NOLOCK and WITH(NOLOCK)SQL Server Table hint

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'
SQL Server Table hints - NOLOCK

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'

BEGIN TRANSACTION

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)

Blocking information - SQL No LOCK

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)
request session id locking information - SQL No LOCK

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
SQL Server Table hints

Uncommited read data using NOLOCK


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.

WITH KEYWORD

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.

Table Alias

As shown below, the request status is WAIT.

Transaction requst status as WAIT - SQL No LOCK

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.

Incorrect Syntax near 'NOLOCK'

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.




441 views0 comments