1

I read here and here that, when connecting to a Sql Server read-only replica, the isolation level used is ALWAYS SNAPSHOT. It says it ignores SET TRANSACTION ISOLATION LEVEL statement and any locking hints.

Is this true?

Is it also true for Azure SQL Database replicas?

What happens if the snapshot isolation level is disabled?

Improve this question
1
  • Try disabling it and edit your observation into the question.
    – Michael Green
    Nov 24, 2021 at 19:38

1 Answer 1

Reset to default
2

when connecting to a Sql Server read-only replica, the isolation level used is ALWAYS SNAPSHOT. It says it ignores SET TRANSACTION ISOLATION LEVEL statement and any locking hints. Is this true?

Microsoft documentation says the same, see Benefits section of link below:

https://learn.microsoft.com/en-us/sql/database-engine/availability-groups/windows/active-secondaries-readable-secondary-replicas-always-on-availability-groups?view=sql-server-ver15

Is it also true for Azure Sql Database replicas ?

On both SQL Server and Azure SQL Database, when you connect to readable secondary, transaction isolation level will be Snapshot

What happens if the snapshot isolation level is disabled?

Even if you disable ALLOW_SNAPSHOT_ISOLATION or READ_COMMITTED_SNAPSHOT on database on primary replica, sessions on secondary will still use Snapshot mode because row versioning will still be used internally

Improve this answer
2
  • You contradict yourself by first saying that I can trust it should be using SNAPSHOT isolation and then saying it is actually using RCSI. SNAPSHOT and RCSI are different isolation levels.
    – Clement
    Nov 27, 2021 at 8:15
  • @Clement you are right; found MS doc where it is confirmed - edited the answer. Please remove the downvote
    – Aleksey Vitsko
    Nov 27, 2021 at 14:28

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.