Readable Secondary Node and Isolation Level

  • Hi

    I've been looking into using availability groups in our environment.  I've read that if the secondary is readable then all selects execute with snapshot isolation.

    Does this mean that the queries need to be written to run with snapshot isolation or suffer from unreliable results as detailed by Craig Freeman here?

    Logically I can't see why this issue wouldn't apply, but I can't find any blog\article that raises it as a concern.

    Am I missing something? if I am, what is it?

    Cheers

    Alex

  • alex.palmer - Friday, December 1, 2017 2:48 AM

    Hi

    I've been looking into using availability groups in our environment.  I've read that if the secondary is readable then all selects execute with snapshot isolation.

    That is correct, the impact of row versioning is mentioned in my stairway at this link

    http://www.sqlservercentral.com/articles/stairway/107542/

    alex.palmer - Friday, December 1, 2017 2:48 AM


    Does this mean that the queries need to be written to run with snapshot isolation or suffer from unreliable results as detailed by Craig Freeman here?

    All queries are converted to snapshot automatically

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

    "Ya can't make an omelette without breaking just a few eggs" 😉

  • Thanks for your reply Perry

    I have already read all of your excellent Stairway to AlwaysOn series.

    Just to be absolutely clear then. If we want to use a readable secondary all queries should be analysed to make sure they return the expected values under the snapshot isolation

  • alex.palmer - Friday, December 1, 2017 6:01 AM

    Thanks for your reply Perry

    I have already read all of your excellent Stairway to AlwaysOn series.

    Just to be absolutely clear then. If we want to use a readable secondary all queries should be analysed to make sure they return the expected values under the snapshot isolation

    That would make absolute sense

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

    "Ya can't make an omelette without breaking just a few eggs" 😉

  • We have a situation here in-house whereby NOLOCK is written by default into every query.  I realise that is not a good idea but that is how it is and noone wants to change it so please don't shout at me.... 🙂

    The point is that this behaviour (essentially a READ UNCOMMITTED statement) is overwritten by the behaviour of the HAG.  In this case it is irrelevant what the isolation level is, HAGs dictate the use of READ COMMITTED SNAPSHOT and ignores any attempts to try anything else.

    At least that is my understanding.....

    To that end, we have a lot of testing to do to ensure this behaviour doesn't interfere with our setup here.....

  • kevaburg - Wednesday, December 6, 2017 6:57 AM

    We have a situation here in-house whereby NOLOCK is written by default into every query.  I realise that is not a good idea but that is how it is and noone wants to change it so please don't shout at me.... 🙂

    I'm not saying a word 😀

    kevaburg - Wednesday, December 6, 2017 6:57 AM


    The point is that this behaviour (essentially a READ UNCOMMITTED statement) is overwritten by the behaviour of the HAG.  In this case it is irrelevant what the isolation level is, HAGs dictate the use of READ COMMITTED SNAPSHOT and ignores any attempts to try anything else.

    At least that is my understanding.....

    Your understanding is correct

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

    "Ya can't make an omelette without breaking just a few eggs" 😉

Viewing 6 posts - 1 through 5 (of 5 total)

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