In Peer-to-Peer Transactional Replication we can allow for conflict detection and also to continue after a conflict.
Really changes to the data at different nodes should be partitioned so conflicts are not possible but not everyone sets it up this way so there is a rudimentary conflict resolution mechanism in place for us to use.
For an update-update conflict we’ll see mention of it in the conflict viewer in a format like the one below. One node skips the conflict ie preserves its value, while the other applies the update and so overwrites its value.
In the case above, both records end up being “Madrid” and we have data in sync. But how was this decided? The point to notice is the numbers above “peer 100” and “peer 1”.
We can see which node has which value when looking at the topology:
..and the number 1 was allocated when we set up the subscriptions :
In the conflict, the row that originated at the node with the highest ID wins. The value of 100 is assigned to the Publisher by default, and as above we left the default of 1 at the first subscriber.
Now – here’s the point – the publisher in this scenario will always beat the 1st subscriber. The second subscriber always wins against the first, the third always beats the second and so on. This is not something we can change afterwards.
So, we need to decide which nodes are the most important before we set this up!