Availability Group synchronous commit - Write / Read Question

Question

we configured for our customer a avalabilty group with synchronous commit mode and two cluster. One cluster is the primary cluster and is writable and readable. The other cluster is the secondary cluster and is only readable. We use the secondary cluster for read only operations. Also we use this cluster on a asp.net portal for reading the DB and the primary cluster to writing to the DB. By testing the portal and the functionality of the AG we detected a problem. By changing data in the portal in some db tables and clicking "save", the refresh of the site shows old data until we refresh the site again. The first question is what exactly means synchronous commit mode? Can I read data from the secondary exactly after the data is commited on the primary?

I write a table and script on the db to test the read / write functionality.

Create a table for testing:

CREATE Table temp.tblAuthors
(
   Id int identity primary key,
   Author_name nvarchar(50),
   country nvarchar(50)
)

First script for inserting 30000 data rows on the primary cluster:

SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
GO

BEGIN TRANSACTION A;

DECLARE @Id INT

SET @Id = 1

WHILE @Id <= 30000
BEGIN
    INSERT INTO TEMP.tblAuthors
    VALUES (
        'Author - ' + CAST(@Id AS NVARCHAR(10)),
        'Country - ' + CAST(@Id AS NVARCHAR(10)) + ' name'
        )

    SET @Id = @Id + 1
END

COMMIT TRANSACTION A;

PRINT CONVERT(VARCHAR, SYSDATETIME(), 121)

Second script for read the secondary cluster every 2 milliseconds in a loop:

DECLARE @i INT = 1;
DECLARE @PrintVarchar NVARCHAR(max)

WHILE (@i <= 60000)
BEGIN
    WAITFOR DELAY '00:00:00.002'

    SELECT Count(*),
        CONVERT(VARCHAR, SYSDATETIME(), 121)
    FROM TEMP.tblAuthors

    PRINT @PrintVarchar

    SET @i = @i + 1;
END

At first step I run the second script with the connecton to the secondary cluster to track the point of time I can see the data on the secondary cluster. In the next step I run the first script to insert data into table on the primary cluster. At last I check the datetime of the commit on primary and the datetime on the secondary at the moment the inserted data is readable on secondary.

The result:

Commit on primary:     2019-11-26 06:55:58.9978911 
Readable on secondary: 2019-11-26 06:55:59.8104941

The data on secondary is readable approximately after one second after commit on the primary cluster. That explains the save-refresh problem on the portal. We save data on the primary and the refresh is just faster as the latency after the commit.

Can any body explain this phenomenon? Is this case for the asp.net portal the right one or should we use for the portal the primary cluster only?

Sorry for my bad english.

Best regards,

Alex

Answer 1

Your portal application does not have readonly workload. It should use the Primary cluster for the all the database access.

The synchronous mode guarantees the transaction logs are hardened on the secondaries, and ack tehy primary node, which then commits the transaction. It does not mean the data of the changes are available on the secondary when the transaction is committed. The secondary has a redo thread to apply the transaction on the secondary, this is where the latency comes from.