Click here to monitor SSC
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in
Home       Members    Calendar    Who's On

Add to briefcase

Replication -- Will blocking occur at the Subscriber like at the Publisher? Expand / Collapse
Posted Monday, May 13, 2013 2:36 PM


Group: General Forum Members
Last Login: Wednesday, November 30, 2016 8:09 PM
Points: 103, Visits: 685
Hello, I have a table on Database A that is frequently updated by Application A.

Application B needs to frequently SELECT from this table in a time critical process, so obviously some blocking occurs.

I was thinking of Replicating the table to Database B and do the SELECT from that table.

What I am unsure of if I will get exactly the same symptoms i.e if an UPDATE lock occurs on Database A (Publisher) therefore blocking SELECTS then an UPDATE lock will also occur on Database B (Subscriber) therefore still blocking basically more complexity for no gain!!!

Is this what would happen?

Post #1452320
Posted Monday, May 13, 2013 5:24 PM



Group: General Forum Members
Last Login: Friday, December 2, 2016 3:25 AM
Points: 7,933, Visits: 14,355
It could, but remember that the original statement is not what is replicated, the resulting CRUD operations are (minus the R's) so blocking should be reduced.

Said another way, imagine you issue a MERGE statement that has a 5-table join in the source and a very complex WHEN MATCHED clause to determine when to update. The MERGE could take 10 seconds to complete and cause lots of blocking, but it may only update 3 rows. In this scenario only 3 UPDATE statements would be Replicated and they would have the Primary Key in the WHERE clause which, all things being reasonable, should complete in well under a second on the Subscribers.

There are no special teachers of virtue, because virtue is taught by the whole community. --Plato
Post #1452366
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse