0

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.

Eric
  • 2,120
  • 1
  • 17
  • 34
  • My understanding was that the transaction is not released until the data is fully committed on all nodes. That is, after all logs have been processed. Are you sure you're operating in SAFE/synchronous mode? – Bacon Bits Aug 27 '15 at 20:43
  • what kind of data latency are you experiencing, which is preventing the offloading of the reporting? – Greg Aug 27 '15 at 20:47
  • It takes at least 1 second for the replica to serve up the updated data. We see this when we open a popup window that allows the user to modify a record, and then fall back to the primary window, which calls a data refresh method, and the query returns the data in a state prior to the update. Wait 1 second, re-run the query, and it returns the updated data. – Eric Sep 02 '15 at 12:46
  • Just because data has been committed to a log, does not mean that it will be returned by a query of the database. It takes time for the log to be processed, which will ultimately affect queries. This is just one of the many things about SQL that I did not know before, but have had to accept as I spend more time with Always On. – Eric Sep 02 '15 at 12:49

0 Answers0