Where to place WITH (NOLOCK) ?

  • BOR15K (12/30/2016)


    ThomasRushton (12/30/2016)


    Phil Parkin (12/30/2016)


    Further to Thomas' answer, the flippant answer given by many people here would be 'in the garbage'.

    y'know, that was my first answer... but I censored myself before clicking on "Post"... 😉

    I appreciate lot's of people may have lots of ideas, hence my post here. What would you advise then to use, please? I haven't mentioned, but the select is a part of a stored procedure

    The first thing I would do is to look at the query itself and ensure that it is as optimised as it can be. You can get help here by posting the actual execution plan and asking for advice on any aspects you do not understand.

    If you do that, it should run very fast and you should be able to remove NOLOCK hints across the board, with few adverse effects.

    The absence of evidence is not evidence of absence
    - Martin Rees
    The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
    - Phil Parkin

  • BOR15K (12/30/2016)


    Thank you Jeff,

    But I think we have drifted way beyond my original question of where to place WITH (NOLOCK).

    With regards to your concern it might always select a same row, I am sure this won't be a case,

    as I have only used the above SELECT to simplify the matter and focus on my core question.

    In real scenario there is an additional JOIN statement between myMainTable and a list of selected invoices, written into a temporary table, so myMainTable will only return

    the data for required, processed invoices, which can be amended no more.

    Correct. We can only go on what we see and we're trying to help with other potential issues.

    Your original question has serious ramifications and so does the use of TOP in the simplified code you posted and that's why it seems like we've deviated. My other answer would be that if you don't know where to put the WITH(NOLOCK), you don't actually understand it well enough to make the decision to use it or not and we're trying to watch out for you.

    If you have no such concerns for the possible quality of the data that will be used to validate internal data/processes, then use the SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED statement as Luis suggested and then you don't have to worry about where to use it. You only have to worry about having used it. 😉

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • Jeff Moden (12/30/2016)


    BOR15K (12/30/2016)


    Thank you Jeff,

    But I think we have drifted way beyond my original question of where to place WITH (NOLOCK).

    With regards to your concern it might always select a same row, I am sure this won't be a case,

    as I have only used the above SELECT to simplify the matter and focus on my core question.

    In real scenario there is an additional JOIN statement between myMainTable and a list of selected invoices, written into a temporary table, so myMainTable will only return

    the data for required, processed invoices, which can be amended no more.

    Correct. We can only go on what we see and we're trying to help with other potential issues.

    Your original question has serious ramifications and so does the use of TOP in the simplified code you posted and that's why it seems like we've deviated. My other answer would be that if you don't know where to put the WITH(NOLOCK), you don't actually understand it well enough to make the decision to use it or not and we're trying to watch out for you.

    If you have no such concerns for the possible quality of the data that will be used to validate internal data/processes, then use the SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED statement as Luis suggested and then you don't have to worry about where to use it. You only have to worry about having used it. 😉

    In addition to everything stated so far - no one has even mentioned snapshot isolation or setting read committed snapshot. If you enable snapshot isolation you can then use that in your procedure and avoid using NOLOCK completely.

    ALTER DATABASE {your database} SET ALLOW_SNAPSHOT_ISOLATION ON;

    GO

    Then in your procedure:

    SET TRANSACTION ISOLATION LEVEL SNAPSHOT;

    Now you can query without blocking and you don't have to worry about getting any data that has not been committed.

    https://msdn.microsoft.com/en-us/library/tcbchxcb(v=vs.110).aspx

    https://www.brentozar.com/archive/2013/01/implementing-snapshot-or-read-committed-snapshot-isolation-in-sql-server-a-guide/

    http://blog.sqlauthority.com/2015/07/03/sql-server-difference-between-read-committed-snapshot-and-snapshot-isolation-level/

    Jeffrey Williams
    “We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

    ― Charles R. Swindoll

    How to post questions to get better answers faster
    Managing Transaction Logs

  • I'd be very cautious about recommending the use of SNAPSHOT ISOLATION... very cautious. As with any such major feature, there are major caveats.

    Kendra Little wrote a nice 60,000 foot level article on the subject and although she wrote it in a non-alarming fashion, there can be some huge problems associated with TempDB and causing the wrong data to be written during updates. Here's the link.

    https://www.brentozar.com/archive/2013/01/implementing-snapshot-or-read-committed-snapshot-isolation-in-sql-server-a-guide/

    There are many other articles and MS documentation on the subject that identify the possible serious ramifications not to mention the fact that it also can affect front end code.

    I think I'd rather take the risk of using either WITH (NOLOCK) or WITH (READPAST) than have to worry about all that actually needs to be done for and the ramifications of with SNAPSHOT ISOLATION and/or RCSI. Even better than that, it seems far easier, wiser, and like a much better idea to find and fix highly contentious code, as well.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • Jeff Moden (12/30/2016)


    I'd be very cautious about recommending the use of SNAPSHOT ISOLATION... very cautious. As with any such major feature, there are major caveats.

    Kendra Little wrote a nice 60,000 foot level article on the subject and although she wrote it in a non-alarming fashion, there can be some huge problems associated with TempDB and causing the wrong data to be written during updates. Here's the link.

    https://www.brentozar.com/archive/2013/01/implementing-snapshot-or-read-committed-snapshot-isolation-in-sql-server-a-guide/

    There are many other articles and MS documentation on the subject that identify the possible serious ramifications not to mention the fact that it also can affect front end code.

    I think I'd rather take the risk of using either WITH (NOLOCK) or WITH (READPAST) than have to worry about all that actually needs to be done for and the ramifications of with SNAPSHOT ISOLATION and/or RCSI. Even better than that, it seems far easier, wiser, and like a much better idea to find and fix highly contentious code, as well.

    Maybe I have an over simplified view of it, but I find the red marble problem in Kendra's document doesn't reflect what's typically going on in an OLTP environment. In her example, the read and write are happening within the same transaction, while the typical live scenario is a read happens, the user or some automated batch determines some changes, then the write happens as a separate transaction. In that case, then having the default READ COMMITTED isolation level would not ensure consistency amongst multiple read/writes anyway.

    Multi-version concurrency control is what a modern RDBMS does. When I first started working with SQL Server in version 2000, I was astonished it didn't have it after working with Oracle and Interbase for years. In my mind, when Microsoft introduced that feature in 2005 is when SQL Server first became an enterprise worthy database engine.

  • Chris Harshman (1/2/2017)


    Jeff Moden (12/30/2016)


    I'd be very cautious about recommending the use of SNAPSHOT ISOLATION... very cautious. As with any such major feature, there are major caveats.

    Kendra Little wrote a nice 60,000 foot level article on the subject and although she wrote it in a non-alarming fashion, there can be some huge problems associated with TempDB and causing the wrong data to be written during updates. Here's the link.

    https://www.brentozar.com/archive/2013/01/implementing-snapshot-or-read-committed-snapshot-isolation-in-sql-server-a-guide/

    There are many other articles and MS documentation on the subject that identify the possible serious ramifications not to mention the fact that it also can affect front end code.

    I think I'd rather take the risk of using either WITH (NOLOCK) or WITH (READPAST) than have to worry about all that actually needs to be done for and the ramifications of with SNAPSHOT ISOLATION and/or RCSI. Even better than that, it seems far easier, wiser, and like a much better idea to find and fix highly contentious code, as well.

    Maybe I have an over simplified view of it, but I find the red marble problem in Kendra's document doesn't reflect what's typically going on in an OLTP environment. In her example, the read and write are happening within the same transaction, while the typical live scenario is a read happens, the user or some automated batch determines some changes, then the write happens as a separate transaction. In that case, then having the default READ COMMITTED isolation level would not ensure consistency amongst multiple read/writes anyway.

    Multi-version concurrency control is what a modern RDBMS does. When I first started working with SQL Server in version 2000, I was astonished it didn't have it after working with Oracle and Interbase for years. In my mind, when Microsoft introduced that feature in 2005 is when SQL Server first became an enterprise worthy database engine.

    Absolutely agreed. I worked with Oracle for about 3 years and if a deadlock occurred, it meant there was something seriously wrong because Oracle does a really good job in that area.

    But, let me ask... before you read Kendra's article, were you even aware that the "Red Marble" scenario could occur? Where you aware of all of the ramifications concerning TempDB like the one that could occur if a long winded transaction occurred or got stuck?

    That's what I'm talking about. Just like the folks that casually recommended the use of WITH(NOLOCK), there are ramifications and risks that must be fully understood before the use of SNAPSHOT ISOLATIONs or WITH(NOLOCK/READPAST), etc.

    And, the bottom line is that, while both things do have their uses and work very well for those correct uses, both are frequently used to put a band-aid on the stab wound known as bad code rather than fixing the bad code.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • Jeff Moden (1/2/2017)


    ...That's what I'm talking about. Just like the folks that casually recommended the use of WITH(NOLOCK), there are ramifications and risks that must be fully understood before the use of SNAPSHOT ISOLATIONs or WITH(NOLOCK/READPAST), etc.

    And, the bottom line is that, while both things do have their uses and work very well for those correct uses, both are frequently used to put a band-aid on the stab wound known as bad code rather than fixing the bad code.

    Well I'm always learning... it never stops 😉

  • BOR15K (12/30/2016)


    Thank you for the answers.

    I am aware of the dirty data and in this case there will be no update of the data within the selected range, hence I want to use it

    With regards to the TOP 1 I appreciate it might sound weird, but this what the customer require - to fetch the first value, disregarding the order, therefore there is no ORDER BY.

    Hi!

    There's another issue with this: testing the query may prove hard.

    When making changes to big queries, I usually save "sample results" produced by the original query (sometimes, the result of the whole query), then make the necessary changes and then I save "sample results" for the new query, using the same parameters used to produce the sample with the original query. After that, by comparing both sets of sample results, I can make sure that the changes I made only introduced the new behaviour I expected, and no extra "side effects".

    Not using an "order by" is a way of introducing "extra changes" between the "sample results" sets, which may make the results unpredictable and introduce extra noise in our testing procedures. If the value itself is not important (as you said that any value can be chosen at random), it may be replaced by and exists() subquery; if not, at least I would use an "order by" just to remove the "at random" part as much as possible, as debugging a problem that happens "at random" is something that may consumes a lot of time later.

    As for the nolock, I started using it because our ERP software would show a message everytime a user-created query was saved if it didn't have the "with (nolock)" hint near every table used. As such, I started using it in order to get rid of the message. And, some time later, I stopped using it the first time one of my queries returned duplicate rows, the duplicated were printed on a report and I got a ticket from a user complaining the report was wrong (in an irreproducible way, as the written query was never supposed to produce the duplicate results the user was complaining on the ticket, but, there the duplicates were, on paper, to prove).

    I don't know how long your query takes to execute, or how often it is executed, but I would advice to designing it to run as fast as possible or running it in a period where the database is not so busy (but allowing it to acquire the associated shared locks) would be better than using the "nolock" option and risk producing inconsistent results. Remember: when people realise this inconsistencies, they'll call you (or one of your teammates) to solve it.

    PS: I'm a programmer, but I write a lot of SQL queries (big, 300+ lines of code) and stored procedures.

  • Chris Harshman (1/3/2017)


    Jeff Moden (1/2/2017)


    ...That's what I'm talking about. Just like the folks that casually recommended the use of WITH(NOLOCK), there are ramifications and risks that must be fully understood before the use of SNAPSHOT ISOLATIONs or WITH(NOLOCK/READPAST), etc.

    And, the bottom line is that, while both things do have their uses and work very well for those correct uses, both are frequently used to put a band-aid on the stab wound known as bad code rather than fixing the bad code.

    Well I'm always learning... it never stops 😉

    Me, too, Chris. 🙂 That's why I love threads like this and posts like yours. And there's no irony intended in any of that. It's why I love the community here. Lot's of ideas exchanged. That you for what you do.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • BOR15K (12/30/2016)


    Chris Harshman (12/30/2016)


    BOR15K (12/30/2016)


    ...I am aware of the dirty data and in this case there will be no update of the data within the selected range, hence I want to use it...

    Sorry to pile on here, but if there will be no updates of the data you're reading, then NOLOCK would seem moot. NOLOCK doesn't prevent this query from taking out any locks, it actually just tells the database engine to ignore other sessions locks on the data this session is querying. What problem do you think NOLOCK will fix in your environment?

    https://blogs.sentryone.com/aaronbertrand/bad-habits-nolock-everywhere/

    There is 4GL application, querying and writing (mostly) into myMainTable frequently and I do not want to lock it with my SELECT statement.

    It doesn't update the columns I will be using though, but it has its own logic we cannot amend, when and how to lock the tables, so I want to reduce to a minimum

    situations my current process in any way will prevent that 4GL product to place a lock on myMainTable when required. Would you use a different approach?

    When you join two tables , a row in one can match (say) two rows in the other. If one of those is being updated and the update will not happen on the columns that you need then your focus is on those that you do need of course. But I wonder about the impact in your WHERE clause. If you are selecting columns a,b,c where column d> 50 and the process updates column D to 47 from 51... then is this a valid row to pull for validation?

    As has been mentioned I would first confirm that your query is optimised and see if there is an actual impact on performance before taking on this risk.

    ----------------------------------------------------

  • @BOR15K

    To answer your base question, use the NoLock hint in front of each table that you want it applied to. In my opinion, you add maintenance risk to your code by segregating the NoLock hint from the affected tables. I do not know if there is a behavior impact of using it on, for example, a CTE consisting of multiple joins.

    It is much safer to be explicit in your code to document your intent for yourself or another person reading or updating the code later.

    If you want it applied to every table in your transaction, and any tables added in the future, then use TRANSACTION ISOLATION LEVEL READ UNCOMMITTED.

    Wes
    (A solid design is always preferable to a creative workaround)

  • I guess everyone has their own coding standard, but I prefer to place most NOLOCK hints I see between two asterisk / backslash like this:

    /* NOLOCK */

    😉

    "Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho

Viewing 12 posts - 16 through 26 (of 26 total)

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