All geek questions in one place

Dirty Reads in SQL Server AlwaysOn

I have a SQL Server 2014 database configured as an AlwaysOn synchronous availability group.

Both servers are configured for availability Synchronous commitwith a session timeout of 50 seconds. The secondary is Read-intent onlyreadable secondary.

If I write to the primary, and then immediately read from the secondary (through ApplicationIntent=ReadOnly), I constantly read dirty data (i.e. the state before writing). If I wait about a second between writing and reading, I get the correct data.

Is this the expected behavior? If so, can I do something to make sure that readings from the secondary are relevant?

I would like to use the secondary version of the primary version (as well as for failure) to read in order to reduce the load on the primary.

+4
source share
1 answer

It is impossible to get dirty reads unless you use a hint without locking.

When you enable Secondary Second Only in AlwaysOn..Internally SQL uses rowversioning to store the previous version of the row.

then you use the synchronous commit mode, this ensures that the journal entries will be first on the secondary and then on the primary.

what you see is data latency.

This whitePaper deals with this scenario..Below is an important part that helps to understand more about this.

, , , , .

, . , ( RPO = 0) ACK , , REDO .

, . , , . . ( , ), , .

,

+3

Source: https://habr.com/ru/post/1655067/

More articles:


All Articles