Avoid using NOLOCK on SQL Server UPDATE and DELETE statements

By:   |   Updated: 2014-02-24   |   Comments (6)   |   Related: > Locking and Blocking


Problem

Occasionally I come across UPDATE and DELETE statements where the target SQL Server table is referenced with the NOLOCK hint. Does this hint help or hurt performance in this case? Should I keep using this hint, or should I remove it from these DML statements?

Solution

There are definitely a couple of issues you should be aware of when using NOLOCK against the target table of a DML statement. To be clear, the pattern I'm talking about is this one:

UPDATE t1 SET t1.x = something
   FROM dbo.t1 WITH (NOLOCK)
   INNER JOIN ...;
-- or
DELETE t1 FROM dbo.t1 WITH (NOLOCK)
  WHERE EXISTS (SELECT ...);
  
-- or
;WITH x AS (SELECT x,y FROM dbo.t1 WITH (NOLOCK))
UPDATE x SET ...;

Of course, you can't even try using NOLOCK with MERGE; SQL Server immediately complains with the following error:

Msg 1065, Level 15, State 1
The NOLOCK and READUNCOMMITTED lock hints are not allowed for target tables of INSERT, UPDATE, DELETE or MERGE statements.

And in spite of the wording of the message, the same error is not raised in any of the other patterns I demonstrated above.

So what are the problems?

If SQL Server doesn't raise an error message, then this must be okay, right? This depends on your definition of "okay" and, more importantly, how future-proof you want your code to be. I'll briefly explain three reasons these NOLOCK hints shouldn't be in your DML statements:

The hint is a no-op

    NOLOCK has been ignored in this scenario since SQL Server 2005. So code that uses this hint is implying some sort of non-blocking behavior that doesn't actually happen (and, frankly, isn't possible, when you think about it).

    I ran some tests on SQL Server 2008 and confirmed that the locking behavior (at least according to sys.dm_tran_locks) is identical, with or without the hint. And yes, other statements were still blocked while the update was running (unless they were SELECTs which also used NOLOCK, allowing for dirty reads in either scenario - again, this was true with or without the hint on the UPDATE).

The syntax is deprecated

    In the SQL Server Books Online topic, Deprecated Database Engine Features in SQL Server 2005, you can find the following deprecation notice:

    "Specifying NOLOCK or READUNCOMMITTED in the FROM clause of an UPDATE or DELETE statement when applied to the target table of the statement."

    You can also find the following quote in the topic Table Hint (Transact-SQL):

    "Support for use of the READUNCOMMITTED and NOLOCK hints in the FROM clause that apply to the target table of an UPDATE or DELETE statement will be removed in a future version of SQL Server. Avoid using these hints in this context in new development work, and plan to modify applications that currently use them."

    This means that, while your code does not raise any errors today, it may start to fail in a future version of SQL Server. Nobody wants to carry forward code that has the potential to break through no other change than an upgrade.

Index corruption can occur

Conclusion

I hope that I have convinced you that NOLOCK has no place as a hint against the target table in a DML statement. As a broad first attempt to identify this pattern in your code, I'll offer this query, with the disclaimer that it is very likely to produce false positives, won't necessarily identify UPDATE statements that are built with dynamic SQL, and obviously won't find any offending statements in external applications.

SELECT s.name, o.name, m.definition
  FROM sys.schemas AS s
  INNER JOIN sys.objects AS o
  ON s.[schema_id] = o.[schema_id]
  INNER JOIN sys.sql_modules AS m
  ON o.[object_id] = m.[object_id]
  WHERE UPPER(m.[definition]) LIKE N'%UPDATE%SET%FROM%NOLOCK%'
     OR UPPER(m.[definition]) LIKE N'%DELETE%FROM%NOLOCK%'
     OR UPPER(m.[definition]) LIKE N'%WITH%(%SELECT%NOLOCK%)%DELETE%'
     OR UPPER(m.[definition]) LIKE N'%WITH%(%SELECT%NOLOCK%)%UPDATE%';
Next Steps


sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip



About the author
MSSQLTips author Aaron Bertrand Aaron Bertrand (@AaronBertrand) is a passionate technologist with industry experience dating back to Classic ASP and SQL Server 6.5. He is editor-in-chief of the performance-related blog, SQLPerformance.com, and also blogs at sqlblog.org.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips


Article Last Updated: 2014-02-24

Comments For This Article




Tuesday, January 18, 2022 - 1:09:40 PM - Aaron Bertrand Back To Top (89673)
@JS the hint is _supposed_ to be a no-op. The title of the KB alone reveals that this wasn't always true (and in some scenarios may still not be true, but I give them the benefit of the doubt that they've fixed it). The point remains the same regardless: don't use NOLOCK at all, but especially not on DML.

Tuesday, January 18, 2022 - 12:42:30 PM - JS Back To Top (89672)
Thanks for sharing this. #1 and #3 seem contradictory. High-level, if the hint is a no-op, how does it cause index corruption?

Friday, August 28, 2020 - 1:51:53 PM - Aaron Bertrand Back To Top (86386)
Tania, well, I don't know if it's "good", assuming you have a valid UPDATE statement where myTargetTable also appears in the FROM. It depends on whether you're okay with myTargetTable potentially getting updated in an inconsistent way (see all the possible problems here: https://www.mssqltips.com/sqlservertip/6072/sql-server-nolock-anomalies-issues-and-inconsistencies/).

This tip was specifically about applying the hint to the target of the DML. If you want to risk updating the target with dirty / inconsistent / uncommitted data, that's cool. :-)

Friday, August 28, 2020 - 12:35:15 PM - Tania Amin Back To Top (86385)
What if i use the NoLock hint for the non -target table like this....
update myTargetTable
set diag_code = vwd.DIAGNOSISCODE01,
diag_type = vwd.DIAGNOSISTYPE01,
mdg_mean = vwd.mdg_mean
from WKABEP_DIAGNOSTICS vwd with (NOLOCK) ---is this any good?
where vwd.claimid = myTargetTable.claimid

Monday, March 3, 2014 - 1:27:07 PM - TimothyAWiseman Back To Top (29631)

Excellent article, thank you for the information.

 

I find nolock useful occassionally in some situations dealing witha busy table for simple selects.  But it should be used with care and only if the chance of dirty reads is actually acceptable.  I would never have used it for an update or delete for the same table, and that was before I knew about the index corruption issue you mentioned.


Monday, March 3, 2014 - 9:34:19 AM - Mike Back To Top (29628)

If you're moving to Azure, you'll be forced to remove these hints from your code.















get free sql tips
agree to terms