Using NOLOCK in UPDATE or DELETE

  • Is it good idea to add hint NOLOCK in Sales.SalesPerson for second example. Any references that tells adding is not beneficial

    DELETE spqh

    FROM

    Sales.SalesPersonQuotaHistory AS spqh

    INNER JOIN Sales.SalesPerson AS sp

    ON spqh.BusinessEntityID = sp.BusinessEntityID

    WHERE sp.SalesYTD > 2500000.00;

     

  • nolock should NOT be added anywhere.

    you can read (from many others) this one https://www.sentryone.com/blog/aaronbertrand/bad-habits-nolock-everywhere

  • Most articles I found were talking about 1 table use.

    What if I'm joining on table 2 and using NOLOCK there? Is it good to use? any issue that i can refer too

  • in order to keep things ATOMic, nolock is ignored in INSERT/UPDATE/DELETE operations.

    even if you added it, it would be ignored.

    Lowell


    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • Adding NOLOCK here, or almost anywhere, is a bad idea.

    Look, if you really want to have dirty reads, change your connection string to use READ_UNCOMMITTED. That's the same as NOLOCK, but you don't have to modify your code.

    Then, when you finally realize just how bad a problem dirty reads are, you only have one place to fix everything instead of needing to rewrite every piece of code.

    ----------------------------------------------------
    The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood...
    Theodore Roosevelt

    The Scary DBA
    Author of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd Edition
    Product Evangelist for Red Gate Software

  • Any reference link I can refer too and make my point?

  • Thats a bad trade off we live in

    Isloation is read committed and developers would like to use NOLOCK everywhere. Just needing some references to make my point nolock in delete  on join table 2 is bad idea.

    Can you help pls

  • Klaus Aschenbrenner

    Brent Ozar

    READUNCOMMITTED and NOLOCK cannot be specified for tables modified by insert, update, or delete operations. The SQL Server query optimizer ignores the READUNCOMMITTED and NOLOCK hints in the FROM clause that apply to the target table of an UPDATE or DELETE statement.

    Stuart Ainsworth on Pinal Dave

    Aaron Bertrand

    In short, this is no kind of "new magic" never heard of before. There are very well published issues with NOLOCK. Microsoft states, in the bloody documentation, that NOLOCK will be ignored in the FROM clause.

    I don't know what else to give you here. NOLOCK has a limited purpose in certain, relatively rare, circumstances. When you just paint your code with NOLOCK, you're doing it wrong. And it's not just me who is saying it (oh, and btw, look at the signature, I'm not trying to argue from authority, but I do have a little bit of knowledge here). You've got people like Aaron Bertrand. I'm sure I can track down more if necessary, but I can't see it being necessary.

     

    ----------------------------------------------------
    The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood...
    Theodore Roosevelt

    The Scary DBA
    Author of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd Edition
    Product Evangelist for Red Gate Software

  • One more

    ----------------------------------------------------
    The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood...
    Theodore Roosevelt

    The Scary DBA
    Author of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd Edition
    Product Evangelist for Red Gate Software

  • And another. Cleaning up tabs.

    ----------------------------------------------------
    The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood...
    Theodore Roosevelt

    The Scary DBA
    Author of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd Edition
    Product Evangelist for Red Gate Software

  • So NOLOCK would take effect in the following example:

    DELETE spqh
    FROM Sales.SalesPersonQuotaHistory AS spqh
    INNER JOIN Sales.SalesPerson WITH (NOLOCK) AS sp
    ON spqh.BusinessEntityID = sp.BusinessEntityID
    WHERE sp.SalesYTD > 2500000.00;

    As the table Sales.SalesPerson is not the target table.

    I'm not sure why you would want to do this though (I wouldn't). Maybe the table Sales.SalesPerson is locked by another session preventing read and you don't really care if the value is taken before or after the other process has locked it.

    There are also other issues that could lead to errors, for example you could query the Sales.SalesPerson table midway though a page split and get 2 rows back when there is really only 1, this wouldn't affect the query above but it could cause serious errors on other queries.

  • NOLOCK is NOT a bad thing when used properly, i.e., dirty reads are acceptable for that query (or you know that dirty reads aren't going to recur).  There has been a valid war against NOLOCK everywhere; there has been not so valid war against NOLOCK anywhere.

    People need to be made aware that phantom reads and non-repeatable reads can occur with READ COMMITTED locking as well.  Schema locks are also, IIRC, taken for ANY SELECT, not just those with NOLOCK.

    Thus, the only real issue is dirty reads.  So, again, if dirty reads would cause you a problem, DON'T use NOLOCK.  If not, you can consider it.

    NOLOCK is especially useful on extremely static data, such as historical data.  I can't imagine any reason not to specify NOLOCK for data that's guaranteed to remain static.  For example, we have a table of state abbrevs with name to do lookups.  NOLOCK seems obvious there.  Similarly, we have tables that are only modified once a week, so NOLOCK for those too.

    Allocation order scans could cause issues to be more prevalent with NOLOCK.  If you really must you can avoid that by temporarily modifying the cursor threshold setting (ref: https://www.sqlperformance.com/2015/01/t-sql-queries/allocation-order-scans).

    But back now to the original reason to use NOLOCK in the first place:

    NOLOCK avoids taking locks, which IS more efficient than taking locks.  That's kinda self-explanatory, really, right?

    Thus, yes, use CAUTION and understand the risks of NOLOCK.  But don't automatically refuse to use it or "overblow" the dangers.

    One alternative, SNAPSHOT, has wonderfully less blocking.  But the overhead of SNAPSHOT is quite high.  For example, the potentially heavy use of tempdb and the certain 14 overhead bytes added to each table row.  [Perhaps even worse, those bytes are removed when a table REBUILD is done -- meaning that if a rebuilt row is UPDATEd, the 14 bytes are added again, possibly causing a page split.]

    SNAPSHOT also has a hidden downside.  You see data as from the start time of the query, period.  If someone runs a report at 9:03AM that takes 3 hrs, do they really not want to see any later data on the report?  Sometimes the answer to that q is not as easy as you might think.

    That said, I do have dbs with READ_COMMITTED_SNAPSHOT ON.  SNAPSHOT isn't inherently bad, any more than NOLOCK is inherently bad.

    As we know, overall database performance is complex and involves many interrelated things.

    SQL DBA,SQL Server MVP(07, 08, 09) Prosecutor James Blackburn, in closing argument in the Fatal Vision murders trial: "If in the future, you should cry a tear, cry one for them [the murder victims]. If in the future, you should say a prayer, say one for them. And if in the future, you should light a candle, light one for them."

  • Jonathan AC Roberts wrote:

    So NOLOCK would take effect in the following example:

    DELETE spqh
    FROM Sales.SalesPersonQuotaHistory AS spqh
    INNER JOIN Sales.SalesPerson WITH (NOLOCK) AS sp
    ON spqh.BusinessEntityID = sp.BusinessEntityID
    WHERE sp.SalesYTD > 2500000.00;

    As the table Sales.SalesPerson is not the target table.

    I'm not sure why you would want to do this though (I wouldn't). Maybe the table Sales.SalesPerson is locked by another session preventing read and you don't really care if the value is taken before or after the other process has locked it.

    There are also other issues that could lead to errors, for example you could query the Sales.SalesPerson table midway though a page split and get 2 rows back when there is really only 1, this wouldn't affect the query above but it could cause serious errors on other queries.

    Actually no, according to the documentation, testing, and only excepted by a few odd bugs here & there, NOLOCK is just ignored in an UPDATE query. That's what the documentation says up front. Further, it even warns that since this is non-standard coding practices, at some point in the future they'll be removed. Now, I know that's unlikely (go and look at the deprecation list, nothing ever gets removed any more). However, you're writing code that won't do anything (unless you hit one of the bugs). Why bother?

    ----------------------------------------------------
    The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood...
    Theodore Roosevelt

    The Scary DBA
    Author of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd Edition
    Product Evangelist for Red Gate Software

  • Grant Fritchey wrote:

    Jonathan AC Roberts wrote:

    So NOLOCK would take effect in the following example:

    DELETE spqh
    FROM Sales.SalesPersonQuotaHistory AS spqh
    INNER JOIN Sales.SalesPerson WITH (NOLOCK) AS sp
    ON spqh.BusinessEntityID = sp.BusinessEntityID
    WHERE sp.SalesYTD > 2500000.00;

    As the table Sales.SalesPerson is not the target table.

    I'm not sure why you would want to do this though (I wouldn't). Maybe the table Sales.SalesPerson is locked by another session preventing read and you don't really care if the value is taken before or after the other process has locked it.

    There are also other issues that could lead to errors, for example you could query the Sales.SalesPerson table midway though a page split and get 2 rows back when there is really only 1, this wouldn't affect the query above but it could cause serious errors on other queries.

    Actually no, according to the documentation, testing, and only excepted by a few odd bugs here & there, NOLOCK is just ignored in an UPDATE query. That's what the documentation says up front. Further, it even warns that since this is non-standard coding practices, at some point in the future they'll be removed. Now, I know that's unlikely (go and look at the deprecation list, nothing ever gets removed any more). However, you're writing code that won't do anything (unless you hit one of the bugs). Why bother?

    Interesting.  I don't see why MS would block NOLOCK on a non-modified table in an UPDATE or DELETE.  I would think it would be irrelevant to the UPDATE mechanism whether other tables had NOLOCK or not.  I, too, understood the MS docs to mean only the actual table being modified.  I'll have to read up on this further.

    SQL DBA,SQL Server MVP(07, 08, 09) Prosecutor James Blackburn, in closing argument in the Fatal Vision murders trial: "If in the future, you should cry a tear, cry one for them [the murder victims]. If in the future, you should say a prayer, say one for them. And if in the future, you should light a candle, light one for them."

  • ScottPletcher wrote:

    Thus, the only real issue is dirty reads.  So, again, if dirty reads would cause you a problem, DON'T use NOLOCK.  If not, you can consider it.

    But...

    The biggest issue with dirty reads is how they work. Lots and lots of people think about the classic 'cat' or 'dog' example. "Well, we're updating 'cat' to 'dog' but while that update occurs, some people might see 'cat'"

    If that was all a dirty read was, cool. No big deal. However, dirty reads can lead to missing rows and duplicate rows. Not simply 'cat' or 'dog', but literally variable amounts of data, some possibly duplicate, some possibly missing.

    Now, how bad are dirty reads? A lot worse. A whole lot worse.

    Add to the fact that most people who are painting their code with NOLOCK already have very bad code and very bad structures, both of which lead to lots of scans, the main place where dirty reads are going to miss, duplicate, or otherwise muck with entire rows of data, and suddenly the NOLOCK problem is much worse. Telling people who have horrific code and structure problems that there's a magic bullet that fixes everything is where we get companies that require NOLOCK in every query (and yeah, run across this one many times now). Education on NOLOCK must be brutally clear that, more often than not, it's being abused.

    Are there uses for NOLOCK?

    Yes, absolutely. However, like any query hint, they should be used rarely, with a lot of planning, knowledge, and testing.

    ----------------------------------------------------
    The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood...
    Theodore Roosevelt

    The Scary DBA
    Author of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd Edition
    Product Evangelist for Red Gate Software

Viewing 15 posts - 1 through 15 (of 48 total)

You must be logged in to reply to this topic. Login to reply