Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
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
Author
Message
Posted Monday, May 13, 2013 2:36 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, November 18, 2014 10:43 AM
Points: 100, Visits: 505
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 SELECTs...so basically more complexity for no gain!!!

Is this what would happen?

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


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 6:02 AM
Points: 7,135, Visits: 12,744
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