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 at 19:38
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://docs.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

3
  • 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
    2 days ago
  • @Clement you are right; found MS doc where it is confirmed - edited the answer. Please remove the downvote 2 days ago
  • Thank you, I marked as accepted
    – Clement
    2 days ago

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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