Data consistency in SQL AlwaysOn Availability Group

Cat Mucius 71 Reputation points
2021-07-14T12:06:40.967+00:00

I have an AlwaysOn cluster of SQL Server 2019, containing an Availability Group of 3 replicas in Synchronous mode.

According to Microsoft documentation:

  1. The secondary replica hardens the log and returns an acknowledgement to the primary replica.
  2. On receiving the confirmation from the secondary replica, the primary replica finishes the commit processing and sends a confirmation message to the client.

This article goes into greater detail and explains that:

  1. In the secondary replica, Log Receive gets the log records from the primary replica and writes to Log cache. This process is repeated on each secondary replica participating in synchronous-commit mode.
  2. On each secondary replica, Redo thread exists, and it writes all changes mentioned in log records to the data page and index page. It flushes the log for hardening on secondary database log.
  3. As stated earlier, in synchronous data commit, primary replica waits for the acknowledgement from the secondary replica. At this stage, secondary replica sends an acknowledgement that transaction hardening is completed on secondary.
  4. Once Primary replica, receives an acknowledgement from the secondary replica, it sends the transaction completion message to the client.

So if I understand right:
If I update a record via Primary replica successfully, this updated value should be immediately available for clients querying the Secondary replicas.

However, when I test this, this doesn't work so.

I run a simple batch file, looking like this: 

sqlcmd -E -S tcp:SQL-AG-Listener -d TestDB -Q "BEGIN TRANSACTION; UPDATE TestSyncTable SET CurrentTime='%currentTime%'; COMMIT TRANSACTION;"  
sqlcmd -E -S tcp:SQL-Server01 -d TestDB -Q "SELECT * FROM TestSyncTable" -K ReadOnly  
sqlcmd -E -S tcp:SQL-Server02 -d TestDB -Q "SELECT * FROM TestSyncTable" -K ReadOnly  
sqlcmd -E -S tcp:SQL-Server03 -d TestDB -Q "SELECT * FROM TestSyncTable" -K ReadOnly

So I'm updating the CurrentTime field via the Primary replica (hosting the AG Listener) and then reading it right away via all three replicas. Each sqlcmd command is a separate client process, so it opens its own independent TCP connection.

And then I see something like this: 

SQL-Server01: CurrentTime = 20:02:19.93  
SQL-Server02: CurrentTime = 20:02:16.94  
SQL-Server03: CurrentTime = 20:02:19.93

(Reformatted the output for better readability here)

As far as I've seen, the Primary replica always returns the updated value. And the Secondaries also do - but only some short delay.

So the question is: why? Shouldn't Synchronous mode guarantee that the result of reading operation is consistent with the writing one? If the Secondary replica sends acknowledgement only after its Redo thread updates the data page - then how can it be?

Thanks,
Mucius.

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
12,705 questions
Windows Server Clustering
Windows Server Clustering
Windows Server: A family of Microsoft server operating systems that support enterprise-level management, data storage, applications, and communications.Clustering: The grouping of multiple servers in a way that allows them to appear to be a single unit to client computers on a network. Clustering is a means of increasing network capacity, providing live backup in case one of the servers fails, and improving data security.
957 questions
Transact-SQL
Transact-SQL
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,552 questions
Accepted answer
  1. Eleven Yu (Shanghai Wicresoft Co,.Ltd.) 10,676 Reputation points Microsoft Vendor
    2021-07-15T02:31:27.55+00:00

    Hi,

    Synchronous commit only guarantees the transaction is fully committed to the log on all partners, the log record will still have to be replayed by the redo thread which is single thread. If the redo queue is large, it can take a short while.

    With SQL Server AlwaysOn Availability Groups, when you configure a secondary in synchronous-commit mode for read-only access, there can be data access latency.

    The article mentioned the Data latency on secondary replica.

    The primary replica sends log records of changes on primary database to the secondary replicas. On each secondary database, a dedicated redo thread applies the log records. On a read-access secondary database, a given data change does not appear in query results until the log record that contains the change has been applied to the secondary database and the transaction has been committed on primary database.

    This means that there is some latency, usually only a matter of seconds, between the primary and secondary replicas.

    Another article also explains why changes from primary replica are not reflected on secondary replica for an Always On availability group

    114871-image.png

    Thanks,

    If the Answer is helpful, please click "Accept Answer" and upvote it. Thanks.

    1 person found this answer helpful.

1 additional answer

Sort by: Most helpful
Most helpful Newest Oldest
  1. Erland Sommarskog 100.9K Reputation points MVP
    2021-07-14T22:05:23.75+00:00

    I don't think the article you found on Sqlshack is accurate. I'm not an expert in the AG field, but my understanding is that synchronuos means that the primary waits until the operation has been written to the transaction log on the secondary. But applying that log record to the data page is done on a separate thread asynchronously. Which means that there can be a short delay.

    Note that if there would be a failover, all these log records will be applied before the secondary comes online as a primary, so in that sense there is no data loss.

    1 person found this answer helpful.