Understanding conflict resolution is important in peer-to-peer if you intend to enable it. There is an argument that you should enable conflict detection and not the resolution. Knowing that a conflict has happened allows you to investigate and then to initialize one of the nodes after plugging the hole in the application logic. Even so, some people have conflict resolution enabled. It certainly helps with update-update conflicts (provided you assign the OriginatorIDs correctly – see related post on this site). For insert-insert conflicts you’d expect a failure which can’t be resolved. That’s what used to happen.
Consider a simple table tRegion. We have a PK on the RegionID column and one site inserts “Rome” and the other node “Paris”. Both have a RegionID value of 2 so there will be a conflict when we synchronize.
Publisher:
Subscriber:
Synchronization at each site looks pretty innocuous:
The nodes are defined as below – the publisher has an OriginatorID of 100 and the subscriber a value of 1 so the publisher will win any conflicts.
The conflict viewers show us an interesting story:
The crucial bit is that “The incoming insert was applied to peer 1”!!! In other words we now have data in sync and both regions are “Rome”. How is this possible?
Well the magic is in the insert stored procedure -“sp_MSins_dbotRegion1335813640” in this case. It will convert the insert into an update for the lower OriginatorID where there is an insert-insert conflict :).
Note that this could have unintended consequences! Foreign key records continue to point at this PK record. We could have eg the case that all the Paris streets added at the subscriber now belong to the Rome region. So – be very careful if you are going to use conflict resolution!