Resolving update-update conflict in peer-to-peer replication

Posted in: DBA Lounge, Microsoft SQL Server, Technical Track

Recently I had received a hand-off ticket which was about a replication issue. The system has been configured with the replication of Peer-to-Peer type.

One of the subscribers was throwing an error which was reading like A conflict of type ‘Update-Update’ was detected at peer 4 between peer 100 (incoming), transaction id 0x000000000011a1c3 and peer 100 (on disk). While I was working on this issue and trying to resolve it, I noticed that it wasn’t showing any records in msrepl_errors table or conflict_dbo_table. p2p1 p2p2

Here again, the Error Logs help, as they have the complete details logged in, which help us identify the table name and exact error with the record. If that hadn’t been the case, I would have followed the Replication Troubleshooting method describe in KB 3066750 to fix the issue.

At this time, with the information we had in hand, we reached out to the customer and resolved the issue by fixing it manually. I would like to mention that there are always two ways conflicts are handled in P2P replication:

1) Manually fix the conflict/data issue
2) Let the winner node have precedence about the data/conflict In P2P replication

At the time of configuration, we will have an option to choose which node will have precedence and can be declared the winner. This is decided by the way of originator_i; the highest originator_id will win. We will have to decide this carefully, as once the setup is done, orginator_id is allotted it can not be altered later.

Here are few reference article that will help you understand this topic better:

https://msdn.microsoft.com/en-IN/library/bb934199%28v=sql.105%29.aspx
https://msdn.microsoft.com/en-IN/library/ms151191%28v=sql.105%29.aspx https://blogs.msdn.com/b/change_sql_virtual_ip_from_dhcp_to_static_ip/archive/2015/11/04/conflicts-in-peer-to-peer-replication.aspx
https://blogs.msdn.com/b/repltalk/archive/2010/02/07/repltalk-start-here.aspx

 

email

Interested in working with Hemantgiri? Schedule a tech call.

About the Author

Data Platform Consultant
I am a Database Administrator by profession, and a student at a university called life by heart. I am passionate about SQL Server, photography, reading and sharing. Currently, I'm Data Platform Consultant @Pythian. I have been a Microsoft SQL Server MVP for four years, and a published author of the book - SQL Server 2008 High Availability. Keep in touch with me on twitter @hemantgirig

No comments

Leave a Reply

Your email address will not be published. Required fields are marked *