Transaction Isolation Levels

  • Bryant McClellan

    SSCarpal Tunnel

    Points: 4283

    I've worked with SQL Server since v4.2.1. I have never put any code containing NOLOCK in production. At my present employer we actively remove WITH (NOLOCK) from code as it is being updated.

    ------------
    Buy the ticket, take the ride. -- Hunter S. Thompson

  • aveek22

    SSC-Addicted

    Points: 484

    @bryant-mcclellan, NOLOCK seems to be a bit scary to me, especially when reporting financial data. There's a huge possibility that the reports might be generated with incorrect data, leading to a bad business decision that we never want to happen.

    Aveek has been working as an Analytics Engineer for the past couple of years now. His main areas of interests is SQL Server, SSIS, and SSAS. On his leisure, he likes to travel and is also an amateur photographer.
    https://twitter.com/avikoleum
    https://www.linkedin.com/in/aveekd22/

  • graham.wade 69740

    SSC Rookie

    Points: 26

    @aveek22, totally agree - it depends upon context, we often (hourly) run a query to identify errors being fed from another system (don't ask), if we return 1 or 2 fewer than they really are is not important as we will pick them up on the next run and nolock stops the query from waiting whilst the heavily used table is being updated.

    Very careful with it's use in production code.

  • Bryant McClellan

    SSCarpal Tunnel

    Points: 4283

    Agreed. That is also why having some demonstration of the potential consequences is so valuable. For some applications it is less risky.

    The biggest problem is having it peppered throughout a codebase without rationale for the next new person to discover. Suddenly you have cargo cult programming at work and you don't understand how you suddenly get questionable results. I assume any code I deploy will get copied by someone at some point. If I don't have solid rationale about why it will apply in the majority of cases then I better tread lightly.

     

    I cannot count the number of code reviews I've done where someone could not explain why they did something other than copying an existing example. You want the code that your company depends on to be the best because it will get reused where it was not intended to.

    ------------
    Buy the ticket, take the ride. -- Hunter S. Thompson

  • x

    SSC-Insane

    Points: 23581

    Bryant McClellan wrote:

    I am always surprised that the default isolation level for SSIS package objects is Serializable. Granted, it could be useful or even necessary in some cases. Isolation Level settings other than ReadCommitted should be concious decisions.

    I have to admit, serializable is just so darn easy. If I have an update that involves reading multiple tables and / or subqueries, serializable just takes care of some of the artifacts that I would otherwise have to keep an eye out for.

     

  • ScottPletcher

    SSC Guru

    Points: 98492

    You can get phantom reads and repeat reads using the default iso level of READ COMMITTED.  The only thing NOLOCK adds is dirty reads.  And you can greatly reduce the chance of those by setting cursor threshold to a reasonable, positive value (200 or whatever); naturally you should set it back when you are done with the reads.  That setting is not going to cause errors with results, it might just (in theory) cause less performance for your cursor'ed reads.  And most people don't use very many cursors at all, for very good and well known reasons.

    I'm not saying to use NOLOCK indiscriminately, but it should definitely not be banished.  There are certain cases where it can work very well, such as just query a master table for existence of a row, or reading from a lookup table (such as a states table).

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

  • MVDBA (Mike Vessey)

    SSC-Insane

    Points: 21757

    scott

    cursor thresholds are a new one on me...help a fellow dba out

     

    MVDBA

  • Bryant McClellan

    SSCarpal Tunnel

    Points: 4283

    I guess I've yet to encounter a tuning problem that required NOLOCK to handle.

    ------------
    Buy the ticket, take the ride. -- Hunter S. Thompson

  • ScottPletcher

    SSC Guru

    Points: 98492

    Ok, for a fellow DBA.  I actually "stole" this from a Paul White article.  To be honest, I wasn't aware that the cursor setting prevented allocation scan reads until I was doing research and read his article a few years back.  Then again, that's why I do this kind of reading, 'cause I'm a nerd-DBA and want to learn this kind of obscure stuff.

    https://sqlperformance.com/2015/01/t-sql-queries/allocation-order-scans

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

Viewing 9 posts - 16 through 24 (of 24 total)

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