Transaction Isolation Levels

  • aveek22

    SSC-Addicted

    Points: 484

    Comments posted to this topic are about the item Transaction Isolation Levels

    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/

  • MVDBA (Mike Vessey)

    SSC-Insane

    Points: 21757

    Great article,

    I've had to use READ UNCOMMITTED to fix so many locking and blocking issues (it's not a fix all and should be used only by experienced adults under supervision)

    It's nice to  see articles that can show my dev team a few tricks that makes a DBA a lifesaver 🙂

    MVDBA

  • Bryant McClellan

    SSCarpal Tunnel

    Points: 4283

    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.

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

  • ReportThrall

    Newbie

    Points: 6

    This was a good read. Short and straight to the point. As primary a application developer, the different isolation levels have a lot of application in different areas based on the app itself as well, I would imagine. Being able to read "dirty" uncommitted data in some cases may be required for performance reasons. It is part of the reason why a lot of other database systems (like NoSQL DBs) have differing principles than the default SQL Server behavior.

    In the case of many NoSQL DBs, they even perform clustering with a sort of "eventual consistency", which means that reading will be performed on the closest server even if the data hasn't reached it yet.

    It would definitely be interesting to do some performance tests with "read uncommitted" and see how badly I could break a test script. I am assuming that if you read from a table where an insert has occurred and then the transaction fails, executing the select again will not return the uncommitted record? I assume that may break the ACID durability principle, as the insert was reversed but still read initially...

  • graham.wade 69740

    SSC Rookie

    Points: 26

    I use, and tell my team to use, the table hint "With (nolock)" to set read uncommitted. Has the advantage of being only applicable to that query and makes you be aware of the fact that you may have uncommitted data ( acceptable to us as developers 99% of the time).

  • MVDBA (Mike Vessey)

    SSC-Insane

    Points: 21757

    ReportThrall wrote:

    It would definitely be interesting to do some performance tests with "read uncommitted" and see how badly I could break a test script. I am assuming that if you read from a table where an insert has occurred and then the transaction fails, executing the select again will not return the uncommitted record? I assume that may break the ACID durability principle, as the insert was reversed but still read initially...

    at devcon 2008 Itzik ben-gan (an absolute sql legend) showed a demo with 2 query windows open - one trying to alter data in a loop, the other querying it using nolock.

    most of the time  he hit a phantom row or a missing row within15 seconds... it was quite an artificial test and you will never hit a table that hard in a production environment.

    Getting round the phantom rows issue can be managed by getting your clustered index correct.. I had an issue where I had clustered "date modified" - the rows were jumping around all over the place

    MVDBA

  • pailwriter

    SSC Rookie

    Points: 42

    Very nice article. Straight, to the point and broken down into simple terms.

    Great job aveek22.

    AS a budding DBA, I have found SQL Server Central to be a great learning source.

    Thank you to the team and contributors that keep this going.

  • aveek22

    SSC-Addicted

    Points: 484

    Thank you for your comments, @mvdba.

    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/

  • aveek22

    SSC-Addicted

    Points: 484

    Totally agree with you @bryant-mcclellan

    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/

  • aveek22

    SSC-Addicted

    Points: 484

    Thanks, @ReportThrall.  It is in my to-do list to compare the performance between different transaction isolations and publish it. I will keep you posted.

    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/

  • aveek22

    SSC-Addicted

    Points: 484

    That's correct, @grahamwade-69740. As per my understanding, I think the NOLOCK hint is applied to a table executing the query, whereas the READ UNCOMMITTED is at a session-level. Please correct me if wrong.

    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/

  • aveek22

    SSC-Addicted

    Points: 484

    Thank you @pailwriter. It's my pleasure to be helpful to the community.

    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: That's as I understand it too.

  • x

    SSC-Insane

    Points: 23578

    Nolock / read uncommitted is very popular nowadays, computer programs nowadays are just assumed to be "glitchy", and management loves deliverables that work "most of the time."

     

     

     

     

  • aveek22

    SSC-Addicted

    Points: 484

    Haha! Well said @patrickmcginnis59-10839. Reminded me of one of my old bosses.

    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/

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

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