SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Readable Secondary Node and Isolation Level


Readable Secondary Node and Isolation Level

Author
Message
alex.palmer
alex.palmer
Ten Centuries
Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)

Group: General Forum Members
Points: 1135 Visits: 388
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
Perry Whittle
Perry Whittle
SSC Guru
SSC Guru (167K reputation)SSC Guru (167K reputation)SSC Guru (167K reputation)SSC Guru (167K reputation)SSC Guru (167K reputation)SSC Guru (167K reputation)SSC Guru (167K reputation)SSC Guru (167K reputation)

Group: General Forum Members
Points: 167783 Visits: 18492
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" ;-)
alex.palmer
alex.palmer
Ten Centuries
Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)

Group: General Forum Members
Points: 1135 Visits: 388
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
Perry Whittle
Perry Whittle
SSC Guru
SSC Guru (167K reputation)SSC Guru (167K reputation)SSC Guru (167K reputation)SSC Guru (167K reputation)SSC Guru (167K reputation)SSC Guru (167K reputation)SSC Guru (167K reputation)SSC Guru (167K reputation)

Group: General Forum Members
Points: 167783 Visits: 18492
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" ;-)
kevaburg
kevaburg
SSChampion
SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)

Group: General Forum Members
Points: 12711 Visits: 1271
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.... Smile

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.....
Perry Whittle
Perry Whittle
SSC Guru
SSC Guru (167K reputation)SSC Guru (167K reputation)SSC Guru (167K reputation)SSC Guru (167K reputation)SSC Guru (167K reputation)SSC Guru (167K reputation)SSC Guru (167K reputation)SSC Guru (167K reputation)

Group: General Forum Members
Points: 167783 Visits: 18492
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.... Smile

I'm not saying a word BigGrin

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" ;-)
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum








































































































































































SQLServerCentral


Search