9

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 Answer 1

15
  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.
1
  • If the write on the primary escalated to a table lock (which if I understand correctly can block readers even with snapshot isolation), is that table lock also taken on the secondary during the redo? Oct 13, 2022 at 16:34

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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