Dirty Reads in SQL Server AlwaysOn

Question

I have a pair of SQL Server 2014 databases set up as a synchronous AlwaysOn availability group.

Both servers are set to the Synchronous commit availability mode, with a session timeout of 50 seconds. The secondary is set to be a Read-intent only readable secondary.

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

Is this expected behaviour? If so, is there something I can do to ensure that reads from the secondary are up-to-date?

I'd like to use the secondary as a read-only version of the primary (as well as a fail-over), to reduce the load on the primary.

Dugald

Likely better for dba.stackexchange.com – Upend

It's not "dirty read", it's just latency. Read here: blogs.msdn.microsoft.com/sqlserverstorageengine/2011/12/22/… – Kinross

See dba.stackexchange.com/questions/84420/… . In short: yes, this is expected, and if you can't tolerate any latency, read from the primary. You could conceivably come up with your own mechanism for ensuring your reads are up to date (timestamp table with a trigger or suchlike) but in most cases this would be more trouble than its worth. Do not, in any case, blindly mix replicas in one workload in the expectation they're synchronized. – Aerophagia

It's expected behavior with Synchronous commit availability mode. There is only way as i know to reduce latency if you go with Asynchronous-commit mode. But, there is risk of data lose. Under asynchronous-commit mode, the primary replica commits transactions without waiting for acknowledgement that an asynchronous-commit secondary replica has hardened the log. Asynchronous-commit mode minimizes transaction latency on the secondary databases but allows them to lag behind the primary databases, making some data loss possible. – Alexia

Answer 1

There is no way you can get dirty reads unless you are using no-lock hint..

When you enable Read Only secondaries in AlwaysOn..Internally SQL uses rowversioning to store Previous version of the row..

further you are using Synchronous commit mode,this ensures log records are committed first on secondary,then on primary..

what you are seeing is Data latency..

This whitePaper deals with this scenario..Below is relevant part which helps in understanding more about it..

The reporting workload running on the secondary replica will incur some data latency, typically a few seconds to minutes depending upon the primary workload and the network latency.

The data latency exists even if you have configured the secondary replica to synchronous mode. While it is true that a synchronous replica helps guarantee no data loss in ideal conditions (that is, RPO = 0) by hardening the transaction log records of a committed transaction before sending an ACK to the primary, it does not guarantee that the REDO thread on secondary replica has indeed applied the associated log records to database pages.

So there is some data latency. You may wonder if this data latency is more likely when you have configured the secondary replica in asynchronous mode. This is a more difficult question to answer. If the network between the primary replica and the secondary replica is not able to keep up with the transaction log traffic (that is, if there is not enough bandwidth), the asynchronous replica can fall further behind, leading to higher data latency.

In the case of synchronous replica, the insufficient network bandwidth does not cause higher data latency on the secondary but it can slow down the transaction response time and throughput for the primary workload

Potoroo