Always On: Could select queries in a synchronous secondary replica affect queries in the primary replica?

Question

It is a usual piece of advice to redirect select queries to a secondary replica by declaring the connections as read-only. But I have a doubt regarding this.

I know two facts:

1. Transactions created in the primary replica must be commited first in the synchronous secondary replica.

2. Select queries acquire shared locks on the tables, which prevent other queries from performing modifications.

Does this means that selects queries in read-only connections, which can be sent to the secondary replica in order to improve performance, actually could affect overall performance as they were executed in the primary replica?

Note that this only would occur with synchronous replicas, not with asynchronous ones.

Thanks in advance,

Ignacio

Answer 1

Transactions created in the primary replica must be commited first in the synchronous secondary replica.

To clarify, the transaction log is hardened on the synchronous secondary when the primary transaction commits but it is the redo threads on the secondary that apply the changes to the replica. The commit on the primary does not wait for the redo to complete.

Select queries acquire shared locks on the tables, which prevent other queries from performing modifications.

Queries on the readable secondaries execute under snapshot isolation regardless of the session isolation level. DML operations are read-only and will not block the redo threads. Queries acquire shared schema stability locks to prevent concurrent DDL operations so only DDL (from the primary) may cause blocking.