I have a peer to peer transactional replication set between two identical databases, each one of the databases is being used by a different program on two different servers. Since we implemented the replication I keep getting a PK violation on a table both programs update although the insert statement doesn't enter a primary key and lets SQL server take care of it. In this case the table name is Statistics: Violation of PRIMARY KEY constraint 'PK_Statistics'. Cannot insert duplicate key in object 'dbo.Statistic'.
My insert statement:
INSERT INTO [StackExchange].[dbo].[Statistic] ([UserId] ,[CreateDate] ,[IsError] ,[CustomData] ,[ModuleId] ,[StatisticTypeId]) VALUES (19,GETDATE(),'true','test replication',1,1)
Also, the event viewer in the servers is filled with conflicts data:
This is the frequent error I get on server 2:
A conflict of type 'Update-Delete' was detected at peer 2 between peer 1 (incoming), transaction id 0x000000000031dea3 and peer (null) (on disk), transaction id (null)
This the the same transaction error I get on server 1:
A conflict of type 'Delete-Update' was detected at peer 1 between peer 2 (incoming), transaction id 0x0000000000245950 and peer 1 (on disk), transaction id 0x000000000031dea3
What would you advise me to do in that case?