#0299 – SQL Server – Myths – Using WITH (NOLOCK) also works on INSERT/UPDATE/DELETE statements


Today’s post was prompted by a recent code review experience. I was called upon to code review a data cleanup script. Coming directly to the interesting part, I noticed that the developer had put the WITH (NOLOCK) table hint when consuming the tables in an UPDATE statement. The resulting conversation is transcribed below:


Me: We will need to remove the WITH (NOLOCK) table hint from the UPDATE statements in this script.


Developer: Why? Isn’t the WITH (NOLOCK) supposed to acquire no locks on the database?


Me: That’s not true. For the SQL Server to create a query plan and execute it, SQL Server will have to request at least a Schema stability (Sch-S) lock. You can read about this from Pinal Dave, a.k.a. “SQLAuthority” (B|T)’s post on “What Kind of Lock WITH (NOLOCK) Hint Takes on Object?


Developer: Okay, I get it now. So, we can use to reduce the locking in our database with the WITH (NOLOCK) statement. Wouldn’t we want to reduce locking as much as possible always?


Me: Yes, we may want to keep locks to a minimum, but that’s only during SELECT statements. This is because systems are typically 80% read, 20% write systems. An update would require an exclusive lock, and hence the WITH (NOLOCK) would be ignored by SQL Server. Let me show you an example:


Below are two UPDATE queries – one which uses a NOLOCK statement, and the other which does not. To allow us to track the nature of the locks acquired, I have wrapped them inside of a transaction.

USE AdventureWorks2012;
GO
–Check the locks acquired during a normal UPDATE
BEGIN TRANSACTION NormalUpdate
UPDATE hredh
SET hredh.ShiftID = 2
FROM HumanResources.EmployeeDepartmentHistory AS hredh
WHERE hredh.BusinessEntityID = 215;

–Check the nature of the locks applied
SELECT sdtl.request_type,
sdtl.request_status,
sdtl.request_mode,
sdtl.resource_type,
DB_NAME(sdtl.resource_database_id) AS DatabaseName
FROM sys.dm_tran_locks AS sdtl;
ROLLBACK TRANSACTION NormalUpdate
GO

USE AdventureWorks2012;
GO
–Check the locks acquired during an UPDATE WITH (NOLOCK)
BEGIN TRANSACTION UpdateWithNolock
UPDATE hredh
SET hredh.ShiftID = 2
FROM HumanResources.EmployeeDepartmentHistory AS hredh WITH (NOLOCK)
WHERE hredh.BusinessEntityID = 215;

–Check the nature of the locks applied
SELECT sdtl.request_type,
sdtl.request_status,
sdtl.request_mode,
sdtl.resource_type,
DB_NAME(sdtl.resource_database_id) AS DatabaseName
FROM sys.dm_tran_locks AS sdtl;
ROLLBACK TRANSACTION UpdateWithNolock
GO


Comparing the result sets of both these queries shows us that the same number and nature of locks were acquired by SQL Server for both queries during the UPDATE.











































































































request_type request_status Qry1
request_mode
Qry2
request_mode
Qry1
resource_type
Qry2
resource_type
DatabaseName
LOCK GRANT S S DATABASE DATABASE AdventureWorks2012
LOCK GRANT S S DATABASE DATABASE AdventureWorks2012
LOCK GRANT X X KEY KEY AdventureWorks2012
LOCK GRANT IX IX PAGE PAGE AdventureWorks2012
LOCK GRANT IX IX PAGE PAGE AdventureWorks2012
LOCK GRANT X X KEY KEY AdventureWorks2012
LOCK GRANT X X KEY KEY AdventureWorks2012
LOCK GRANT X X KEY KEY AdventureWorks2012
LOCK GRANT X X KEY KEY AdventureWorks2012
LOCK GRANT IX IX PAGE PAGE AdventureWorks2012
LOCK GRANT X X KEY KEY AdventureWorks2012
LOCK GRANT IX IX OBJECT OBJECT AdventureWorks2012

In fact, the official MSDN/Books-On-Line page for the UPDATE statement [Link] clearly states:



“NOLOCK and READUNCOMMITTED are not allowed”


If you do specify the WITH (NOLOCK) table hint, it is conveniently ignored by the database engine.


Developer: I guess the understanding of the entire team was incorrect. Thank-you, Nakul for explaining the detailed reason behind the code review comment.


Me: You are most welcome! Please note that Microsoft has already marked the use of NOLOCK in INSERT, UPDATE and DELETE statements as a deprecated feature. Hence, while today the database engine ignores the hint, it will produce an error in one of the future versions.


Further Reading:



  • Pinal Dave, a.k.a. “SQLAuthority” (B|T): What Kind of Lock WITH (NOLOCK) Hint Takes on Object? [Link]
  • Books-On-Line/MSDN: Table Hints in SQL Server [Link]
  • Books-On-Line/MSDN: Update Statement [Link]

Important Note


[Added: 10/01/2013, 10:15AM IST]


Please note that using WITH (NOLOCK) on the target table in a complex UPDATE statement may cause corruption in the non-clustered indexes in SQL Server 2008. Please refer https://support.microsoft.com/kb/2878968/en-us for details. Thank-you, Chintak for drawing attention to this KB article.


Until we meet next time,


Be courteous. Drive responsibly.


[Edit: 09/30/2013, 10:55AM IST – A schema-stability lock is abbreviated as Sch-S, not Sch-M.]

Advertisements

2 thoughts on “#0299 – SQL Server – Myths – Using WITH (NOLOCK) also works on INSERT/UPDATE/DELETE statements

  1. Nakul Vachhrajani

    Thank-you, Chintak! This actually makes using WITH (NOLOCK) on target tables in an update statement more dangerous than I originally thought. Thank-you for drawing my attention to it.

    Like

    Reply

Let me know what you think about this post by leaving your feedback here!

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 )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s