Is it possible to use Read Uncommitted isolation level on read-only Availability Group secondary?

Question

We are using availability groups in SQL 2019 Enterprise edition. We are using the enterprise feature to allow the AG secondary nodes to be in Read-only mode, and then running reporting queries against the secondary by connecting to the listener with ApplicationIntent=ReadOnly parameter.

For locking and performance reasons, we have some queries that run using Read Uncommitted isolation level on the primary.

It seems that on the secondary all isolation levels are converted to RCSI regardless of specified locking/isolation level - presumably because it's essential that there are no locks that could block the AG sync.

Is it possible to run queries in Read Uncommitted on the secondary, which would presumably also ensure no locks are taken but, and, under some circumstances can perform better, or will it always have to be RCSI for queries on the Read-Only secondary?

Answer 1

bzzt

Read Uncommitted/NOLOCK doesn't mean your queries don't take locks, it means your queries ignore locks taken by other queries. The hints really should have been called NORESPECT instead. The current naming is quite misleading. They leave you open to returning dirty reads.

Queries under this isolation level will still take schema stability locks, like any other read query.

A much cleaner implementation of this is RCSI, which gives you the last known good version of rows that are currently locked by modification queries rather than potentially returning data from in-flight data from them.

To answer your question, usually local query hints will override database-level settings like Read Committed Snapshot Isolation or Snapshot Isolation. But even query level locking hints are ignored by queries against readable secondaries:

Offload read-only workload to secondary replica of an Always On availability group

All queries that run against the secondary databases are automatically mapped to snapshot isolation transaction level, even when other transaction isolation levels are explicitly set. Also, all locking hints are ignored. This eliminates reader/writer contention.

If you're already using one or both of those row-versioning isolation levels, there shouldn't be any reason outside of edge cases to use Read Uncommitted/NOLOCK.