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.

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.]