Search code examples
sql-serverhigh-availability

Using a read-only replica in synchronous commit mode, is it possible for a query to get different results on the secondary than the primary?

I'm using a simple availability group with a primary server and a secondary replica. They are configured like this:

enter image description here

I have situations where I will insert a row into Table A on the primary using a query like this:

INSERT INTO TableA(UniqueId, Column1)
SELECT 123, Column2
FROM @TVP

And after that I immediately query the values from TableA using a read-only connection.

SELECT Column1
FROM TableA
WHERE UniqueId = 123

Sometimes when making this query, we do not get any rows back. I assume this is because the read-only replica hasn't gotten the data from the primary replica yet but I thought that the insert query would not return until the data had been hardened to the secondary replica.

What is going on here?

Solution

  • With a synchronous AG replica, the commit is hardened on both the primary and secondary nodes when the INSERT transaction commits. However, the changes will not be visible on the secondary until a redo thread on the secondary applies the changes. The latency is typically short but can be delayed due to blocking on high resource utilization on the secondary.

    See the Data Latency topic in the documentation for more information.