I'm thinking about using SQL Server Availability Groups in SQL Server 2012 to offload reporting on our main database to a second read only server that is kept in sync through the availability group.

My question is when I run big reports on the read-only server that would normally create lots of blocking if run on the read/write version do the locks propagate from the read only server back to the read/write server? Or do the locks stay only on the read-only server and just cause blocking on data waiting to be synced?

Trying to see if having a read-only server would resolve some issues I am having.

  1. AlwaysOn secondaries map all reads to versioned reads, which are (almost) lock-free
  2. Even with versioned reads there are some (few) locks in place, eg. schema-stability locks on obejcts. There is never any sort of propagation of locks from secondaries to primaries. Furthermore, there cannot be any propagation of locks from secondaries to primaries simply because the secondary is a back-in-time copy of the primary. There is no point in propagating a lock on object 'foo' from secondary to primary because the primary may had dropped 'foo' 2 hours ago and the secondary did not catch up yet. The propagation of locks downstream from primary to secondary goes through the log records and as such the locks are re-acquired on the secondaries when the log is redone, therefore the lock is always applied to the appropriate structure (if 'foo' existed on the primary when the lock was acquired, then by definition exists on the secondary at the moment the lock is re-played).
  3. Secondary reads may block redo (eg. by holding on a schema-stability lock when the redo is trying to acquire a schema-modification). The redo resumes when the secondary reads releases the lock. And 'redo' in this context means 'applying log from the primary on the secondary', ie. catching up.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.