does WITH (NOLOCK) hint in Select statement really make a difference?

  • VoldemarG

    Hall of Fame

    Points: 3514

    In any regular Select query joining a few tables like below, does using this hint  WITH (NOLOCK) really make any big difference?

    In our environment, such hint is placed after every single table in every join in every stored proc.

    SELECT 1

    FROM #GR             X     WITH(NOLOCK)

    JOIN GT               GT  WITH (NOLOCK) 

    ON X.i_gid            = GT.Child_gid

    JOIN Bi_Pi_A        BPA WITH (NOLOCK)

    ON BPA.e_gid           IN (100,200,300)

    WHERE BPA.rs       = 'Z')

     


    Voldemar
    likes to play chess

  • Jonathan AC Roberts

    SSCoach

    Points: 16993

    Yes, it can retrieve partially updated records and if a page-split is happening it can return duplicate rows.

    Best to only be used on data that is not being updated and that you don't care if the results are accurate or not.

    If a table is midway through a transaction it can be used to see how many rows have been inserted so far, so it can be useful in some instances.

  • Jeff Moden

    SSC Guru

    Points: 995110

    VoldemarG wrote:

    In our environment, such hint is placed after every single table in every join in every stored proc.

    SELECT 1

    FROM #GR             X     WITH(NOLOCK)

    JOIN GT               GT  WITH (NOLOCK) 

    ON X.i_gid            = GT.Child_gid

    JOIN Bi_Pi_A        BPA WITH (NOLOCK)

    ON BPA.e_gid           IN (100,200,300)

    WHERE BPA.rs       = 'Z')

    Let's, just for a minute, assume that the use of WITH(NOLOCK) was ok to do.  It's stupid to mark every single table with the hint especially if you ever need to change that in the future (and, you will).  Learn how to use SET TRANSACTION ISOLATION LEVEL instead.

    Of course, that would be only for the very rare exceptions that Jonathon speaks of.  Both the WITH hint and the SET option are usually bad ideas.

     

    --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.
    "If you think its expensive to hire a professional to do the job, wait until you hire an amateur."--Red Adair
    "Change is inevitable... change for the better is not."
    When you put the right degree of spin on it, the number 3|8 is also a glyph that describes the nature of a DBAs job. 😉

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

  • Chris Harshman

    SSC-Forever

    Points: 41845

    Other than some advanced troubleshooting cases like Jonathan says, there is no reason you should use NOLOCK in your code.

    NOLOCK probably does not do what you think it does.  It still will take out locks for itself on the tables you are querying, it just ignores locks that other sessions have on those tables.  That's what allows it to see uncommitted data, duplicate data, bad data, etc.

    It was a bad hack in the SQL Server 2000 days to deal with the fact that the database's pessimistic locking scheme meant that readers block writers and writers block readers.  Modern database systems use some form of multi-version concurrency, such as SQL Server's read committed snapshot isolation level:

    https://docs.microsoft.com/en-us/dotnet/framework/data/adonet/sql/snapshot-isolation-in-sql-server

     

  • ScottPletcher

    SSC Guru

    Points: 98206

    Chris Harshman wrote:

    Other than some advanced troubleshooting cases like Jonathan says, there is no reason you should use NOLOCK in your code.

    NOLOCK probably does not do what you think it does.  It still will take out locks for itself on the tables you are querying, it just ignores locks that other sessions have on those tables.  That's what allows it to see uncommitted data, duplicate data, bad data, etc.

    It was a bad hack in the SQL Server 2000 days to deal with the fact that the database's pessimistic locking scheme meant that readers block writers and writers block readers.  Modern database systems use some form of multi-version concurrency, such as SQL Server's read committed snapshot isolation level:

    https://docs.microsoft.com/en-us/dotnet/framework/data/adonet/sql/snapshot-isolation-in-sql-server

    Keep in mind that SI causes big overhead to implement it.  Therefore, SI should not automatically be applied to every db.  Appropriate use of WITH (NOLOCK) is better than just rotely turning on SI for all user dbs.

    Of course not every table should automatically be coded WITH (NOLOCK).  That's just crazy.  But NOLOCK does reduce overhead and thus definitely has a place in coding.

    It's actually worse to automatically use SI than NOLOCK -- unless you have gobs of I/O capacity and disk -- but it's still a very bad idea to use NOLOCK indiscriminately.

    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.

  • DesNorton

    SSC-Insane

    Points: 22848

    I stand to be corrected here, but NOLOCK on a #temptable has no effect.  The #temptable is local to your SPID, so there is nobody else reading or writing to the #temptable while you are using it.

  • Grant Fritchey

    SSC Guru

    Points: 395634

    Jeff Moden wrote:

    Let's, just for a minute, assume that the use of WITH(NOLOCK) was ok to do.  It's stupid to mark every single table with the hint especially if you ever need to change that in the future (and, you will).  Learn how to use SET TRANSACTION ISOLATION LEVEL instead.

    Of course, that would be only for the very rare exceptions that Jonathon speaks of.  Both the WITH hint and the SET option are usually bad ideas.

    Please, please, please, please, listen to this. It's so important. It's going to save you tons and tons of work. Let's say you never see a problem from NOLOCK (and honestly, you may not see them, but I know your business is, they just don't report it, they get weird data and rerun the query, maybe two or three times, and never tell you, they think it's all magic any way). You're writing that stuff in every query and every join. What a waste of effort. All you have to do is set the isolation level to READ UNCOMMITTED and you don't have to do all that work.

    However, I have a secondary reason for pushing this. When you do finally realize that, OMG, missing and duplicate data is actually causing problems, and, OMG, running the same query 2-3 times in a row causes all sorts of additional contention and overhead that we otherwise would not have, and OMG, do something about it NOW!!!! You won't have to open up all 8 gajillion lines of code. You go one place, change to READ COMMITTED (or better still READ COMMITTED SNAPSHOT, I'll leave you to look that one up), and you've fixed the problem, quickly and easily with minimal to zero code changes and deployments needed.

    Please, take the time to learn about SNAPSHOT isolation and stop hurting your business with that silly NOLOCK crap.

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

  • jonathan.crawford

    SSCertifiable

    Points: 6366

    DesNorton wrote:

    I stand to be corrected here, but NOLOCK on a #temptable has no effect.  The #temptable is local to your SPID, so there is nobody else reading or writing to the #temptable while you are using it.

    Yes, but, OP said they use it on every single join to every single table. Also, depends if local or global temp (I know this one was local)

     

    -------------------------------------------------------------------------------------------------------------------------------------
    Please follow Best Practices For Posting On Forums to receive quicker and higher quality responses

  • ScottPletcher

    SSC Guru

    Points: 98206

    Chris Harshman wrote:

    NOLOCK probably does not do what you think it does.  It still will take out locks for itself on the tables you are querying

    I don't think so.  Where did you get that from?  Other than the locks absolutely necessary for schema/meta-data and other high-level considerations, NOLOCK does not take any row locks on the table being read.

    Btw, NOLOCK is applicable only to SELECT.

    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.

  • VoldemarG

    Hall of Fame

    Points: 3514

    Thanks everyone for your input on that.  And Thank you, Grant.

     


    Voldemar
    likes to play chess

  • Chris Harshman

    SSC-Forever

    Points: 41845

    ScottPletcher wrote:

    Chris Harshman wrote:

    NOLOCK probably does not do what you think it does.  It still will take out locks for itself on the tables you are querying

    I don't think so.  Where did you get that from?  Other than the locks absolutely necessary for schema/meta-data and other high-level considerations, NOLOCK does not take any row locks on the table being read.

    Sorry for not being specific enough.  But yes it does take out a "Sch-S" lock on the table.  No it doesn't take out page or row level locks, I never said that it did.

  • ScottPletcher

    SSC Guru

    Points: 98206

    Chris Harshman wrote:

    ScottPletcher wrote:

    Chris Harshman wrote:

    NOLOCK probably does not do what you think it does.  It still will take out locks for itself on the tables you are querying

    I don't think so.  Where did you get that from?  Other than the locks absolutely necessary for schema/meta-data and other high-level considerations, NOLOCK does not take any row locks on the table being read.

    Sorry for not being specific enough.  But yes it does take out a "Sch-S" lock on the table.  No it doesn't take out page or row level locks, I never said that it did.

    Actually the full quote made it seem clear that you were talking about row/page locks.

    It still will take out locks for itself on the tables you are querying, it just ignores locks that other sessions have on those tables.  That's what allows it to see uncommitted data, duplicate data, bad data, etc.

    It was a bad hack in the SQL Server 2000 days

    Since a schema lock can't be ignored, only row/page locks can, I think there's a clear inference that you were referring to the latter type of locks, as uncommitted data, etc., also have nothing to do with schema-level locks.  What am I missing?

    The only thing that's really important is that people understand accurately what NOLOCK does save in terms of locking.  It does prevent the normal row share locks that SQL otherwise would have to take.  This does provide a performance benefit, although it can be a slight one; then again, it can also prevent some deadlock situations.  NOLOCK is a legitimate option today, not just in SQL 2000.  And there are very good reasons not to automatically put every db in snapshot mode.

    Even Oracle has moved away from the days when they forced every schema and table to have the overhead of a snapshot-style approach (although they used different terms for it: their dbms effectively required every table to use a snapshot-type techinque, using the rollback segment (as they called it), which often caused failures because of long-running trans, etc.).  Yes, they bragged that "readers didn't block writers" and vice versa, but you paid for that in much added I/O and other overhead.

    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.

  • ben.brugman

    SSChampion

    Points: 13321

    Snapshot isolation is not serializable. Although Oracle had (and has ?) a serializable isolation level, according to the ISO standard it was (is) not serializable. See :

    Serializable vs. Snapshot Isolation Level

    In the past I have build simple examples to show this. The example in the given link is so much more illustrative.

    Ben

    Who uses 'dirty reads' to spy on the progress of other connections. In the running scripts I add rows to a timing table. These can be read from another connection, but if the transaction is still open, 'dirty reads' are needed.

     

     

     

Viewing 13 posts - 1 through 13 (of 13 total)

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