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?

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

1 Answer 1

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

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 Nov 27, 2021 at 14:28

Your Answer

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

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