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.

Comments (1)

  1. Tridib Dev says:

    Hi Chirag,
    Thanks for the article.
    I am facing the issue after Secondary SQL Server reboot after OS patching. Do not see any open transactions in primary prior to the reboot. There are two databases in the AG having this issue. And we have been waiting for more than 15 hours but still readable replica is not able to process any select query for those databases.
    Could you please help.

    Regards,
    Dev

Skip to main content