May 13, 2013 at 2:36 pm
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
May 13, 2013 at 5:24 pm
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
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply