AlwaysOn Availability Groups unable to query against readable secondary replica database: Wait Type HADR_DATABASE_WAIT_FOR_TRANSITION_TO_VERSIONING
Recently a customer reported an interesting issue, while querying against recently added readable replica, SELECT statement is shown as suspended and session is shown as waiting on HADR_DATABASE_WAIT_FOR_TRANSITION_TO_VERSIONING
Here is a simple way to reproduce the scenario
Now on current primary replica, change one of the remote replica to readable
However, running a SELECT Statement against newly available readable replica, query appeared to be "suspended"
Upon more investigation, it appeared to be waiting on with a wait type HADR_DATABASE_WAIT_FOR_TRANSITION_TO_VERSIONING
The behavior is by design as mention in the SQL Server product documentation and applicable to all version of SQL Server that supports availability group.
"As soon as a readable secondary replica joins the availability group, the secondary replica can start accepting connections to its secondary databases. However, if any active transactions exist on a primary database, row versions will not be fully available immediately on the corresponding secondary database. Any active transactions that existed on the primary replica when the secondary replica was configured must be committed or rolled back. Until this process completes, the transaction isolation level mapping on the secondary database is incomplete and queries are temporarily blocked."
This signals that there is at least one transaction open on the primary system which had been opened before you switched the secondary to become a readable secondary. As mentioned, when switching to a readable secondary, we need to provide committed versions before the Redo Thread is performing changes to the data. However, with a transaction still open on the primary that has been open before switching to readable secondary, such a committed version of the data could not be provided on the secondary. Hence the select will wait until the transition is successful. The transition becomes successful, when all the transactions that were open before switching are committed or rolled back on the primary.