How to manage Data Latency on read-only AlwaysOn SQL Databases?
We have several read-only nodes in our AlwaysOn cluster, which are set to use Synchronous-commit mode, which ensures that the logs are updated on the read-only nodes before any update statements complete.  Even with this option, if we query a read-only node before the logs have been processed, we can read old data.  I would like to know a strategy to ensure that a read-only query will definitely return up to date information.  I had an idea that if I just used a different transaction type, like Serializable, that it might block the read-only query from actually getting the data until after the log file was processed, but I have not tried it, yet.  Does anyone know if this, or any other technique would solve this problem?  I would really like to move more queries to the read-only nodes, in an effort to offload CPU utilization from the primary node.
August 27th, 2015 3:44pm

Serializable locks the row. It is not worth using for read only purpose.

In your case you always connect to the secondary replica that is in sync commit mode.

You always get latest data. In sync commit mode data commits first at secondary and then at primary.

In this process you can only have sync commit between your primary and one secondary only.

Free Windows Admin Tool Kit Click here and download it now
August 27th, 2015 8:24pm

No can do. Your reads on the secondary will always be behind, for two reasons. The log records might be hardened but not yet REDOne yet. And also reads on secondaries are always using snapshot isolation. Here's a qoute about the latter, from BOL:

"All queries that run against the secondary databases are automatically mapped to snapshot isolation transaction level, even when other transaction isolation levels are explicitly set. Also, all locking hints are ignored."

August 30th, 2015 3:03pm

This topic is archived. No further replies will be accepted.

Other recent topics Other recent topics