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
Posted Monday, May 13, 2013 2:36 PM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Wednesday, April 09, 2014 1:56 PM
Points: 93, Visits: 463
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: 2 days ago @ 9:25 AM
Points: 7,070, Visits: 12,523
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

Believe you can and you're halfway there. --Theodore Roosevelt

Everything Should Be Made as Simple as Possible, But Not Simpler --Albert Einstein

The significant problems we face cannot be solved at the same level of thinking we were at when we created them. --Albert Einstein

1 apple is not exactly 1/8 of 8 apples. Because there are no absolutely identical apples. --Giordy
Post #1452366
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse