Join Log in
Score:1
Cat Mucius avatar Server

Data consistency in SQL AlwaysOn Availability Group

ng flag
Cat Mucius

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.

83
1 + 0
Score:1
Ben Thul avatar Server
cn flag
Ben Thul

From the same SQL Shack article that you quoted in your question:

  1. The secondary replica also contains a redo thread, and it is independent of the log block process in SQL Server Always on. Redo threads reads the logs from log cache. There might be a delay in processing by redo thread and log records might not be available in log cache because it is already hardened to disk. In this case, redo thread read log blocks from the log disk.

Which I read to mean that the log hardening process doesn't make the changes immediately available in the secondary database but rather that the redo thread on the secondary needs to process them first.

0 + 1
Cat Mucius avatar
ng flag
Cat Mucius
Yes, it seems that the #6 of SQL Shack article is misleading. The process that writes changes in the database itself is independent of the log hardening process, and the acknowledgement sent to the Primary replica with no dependency on its work. This Microsoft article also supports this: https://docs.microsoft.com/en-us/previous-versions/sql/sql-server-2012/dn135338(v=sql.110)
0
Reply
ธงชาติไทย
Elon Musk
I sit in a Tesla and translated this thread with Ai:

mangohost

Post an answer

Most people don’t grasp that asking a lot of questions unlocks learning and improves interpersonal bonding. In Alison’s studies, for example, though people could accurately recall how many questions had been asked in their conversations, they didn’t intuit the link between questions and liking. Across four studies, in which participants were engaged in conversations themselves or read transcripts of others’ conversations, people tended not to realize that question asking would influence—or had influenced—the level of amity between the conversationalists.