Solved

Merge Replication Conflicts

Posted on 2010-11-22
4
476 Views
Last Modified: 2012-05-10
I am using LINQ to SQL in my code of a form that does import some sales data, also I surround the code with a transaction object. Importing the sales subtracts logically the product quantity from the inventory table.
However, if I import on one replica the sales and on the other also do some actions that affect the inventory record of the same product, the merge replication chooses a winner and that renders the record with wrong on hand qty, because the loser (the imported sales) does not roll back all the sales imported!
example:
replica1, productA, import sales of 10 pieces, on hand qty was 20, then it ends being 10
replica2,productA enter goods of 5 pieces, on hand qty was 20, then it ends being 25
after replication and winning of replica2, the inventory is 25, and the sales records are not rolled back! which makes the data corrupted.
0
Comment
Question by:TheSilverBullet
  • 2
  • 2
4 Comments
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 34190726
what are your replication intervals? they might be often enough to avoid such merge replication conflicts, aka updates on both sides.

anyhow: the merge replication IS transactional, but it cannot resolve a transaction on both sides "at the same time" (= in the same replication interval) to update the same record, at it will have to choose a winner.
you have to know that replication does not replay the SQL, but sends the "old/new" data (aka what is in the transaction log: there is no SQL in there. only data info)

another path to solve this might be to split the table into 2, or have 2 columns for the qty:
qty_sold
qty_bought

so, the process selling will only update 1 of the 2 columns, and the process "buying" (aka adding to the store) update the other. a computed column (qty) could do the difference ...

0
 

Author Comment

by:TheSilverBullet
ID: 34194247
Thank you so much for important contribution. what I have is
Transaction
{
update sales table;
update inventory table by subtracting sold qty;
}

on the other replica at the same moment:
Transaction
{
update purchase table;
update inventory table by adding purchased qty;

}

here for the same product there is a conflict on its inventory table record (I am replicating every 60 seconds, but one could go offline in the merge replication for a while), what the agent does is selecting the second as a winner. I thought -since it is transactional in nature- it would rollback the whole first transaction if any line within it get conflicted with another higher priority transaction.
"replication does not replay the SQL" that clarifies it.
I will try to split, sounds a good suggestion.
0
 

Author Comment

by:TheSilverBullet
ID: 34194294
splitting into two columns, still could raise the same issue: what if two purchases done at the same time on two different replicas? in that case it would affect the Purchase Part of the splitting as well and conflict it.
Is there any way to access the conflict viewer data thru SQL queries, only for readonly. that could help to make me program a tool to auto resolve the conflict.
0
 
LVL 142

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 500 total points
ID: 34194563
then I only see the following solution:
the inventory table should be updated on a central store, and this when the purchase and sales tables are updated (including by replication) on that same "central" store.

so, your application should not update the "local replica", but only the purchase/sales table. that change being replicated to the "central" store (aka the one where the inventory is hold), that replication triggers the update of the inventory (which in turn gets replicated to the other replicas )

this however means that the time between the purchase/sales record and the update in the same database for the inventory takes (at least) 2 replication cycles.

you could, of course, hold each time also local sales numbers, to ensure that at least "local sales" will be taken into account when considering if an inventory is "empty" (aka close to being empty) ...
this is indeed a big design issue...
0

Featured Post

NAS Cloud Backup Strategies

This article explains backup scenarios when using network storage. We review the so-called “3-2-1 strategy” and summarize the methods you can use to send NAS data to the cloud

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Where to download and how to install sqldmo.dll 5 82
SQL Server stored proc 2 19
Need a SQL Server 2014 plug-in to scan the DB schema 4 42
SQL query with cast 38 42
     When we have to pass multiple rows of data to SQL Server, the developers either have to send one row at a time or come up with other workarounds to meet requirements like using XML to pass data, which is complex and tedious to use. There is a …
If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
Windows 10 is mostly good. However the one thing that annoys me is how many clicks you have to do to dial a VPN connection. You have to go to settings from the start menu, (2 clicks), Network and Internet (1 click), Click VPN (another click) then fi…
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

770 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question