December 1, 2017 at 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.
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
December 1, 2017 at 5:49 am
alex.palmer - Friday, December 1, 2017 2:48 AMHiI'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" 😉
December 1, 2017 at 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
December 5, 2017 at 10:44 am
alex.palmer - Friday, December 1, 2017 6:01 AMThanks for your reply PerryI 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" 😉
December 6, 2017 at 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.... 🙂
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.....
December 13, 2017 at 9:43 am
kevaburg - Wednesday, December 6, 2017 6:57 AMWe 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