5

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?

Thank you!

Improve this question
3
  • How have you setup the replication? if you want to be able to write to both servers you would need it to be merge replication, if it's transactional replication, one server will be denoted as the 'publisher' and you will get errors when the publisher tries to send an insert of a new row to the subscriber where the ID has already been used...
    – Andrew Bickerton
    Commented Jan 4, 2012 at 11:02
  • 2
    Have you setup the IDENTITY ranges for your tables so that they don't overlap between the servers? Peer to Peer needs to be configured so that as much as possible conflicts are avoided.
    – MartinC
    Commented Jan 4, 2012 at 13:56
  • @Stewie: Is UserID your PK or do you have an IDENTITY (to clarify MartinC's quesation). Also add table definition to be sure
    – gbn
    Commented Jan 4, 2012 at 15:25

1 Answer 1

Reset to default
2

For the PK violations:

You need to ensure that each node in your Peer-to-Peer topology has an identity range island which will not conflict if inserts originate at that node. To do this you'll need to run DBCC checkident(talbename, reseed, start of range), ie. DBCC checkident(tablename, reseed, 100000) for Node 1, DBCC checkident(tablename, reseed, 200000) for Node 2, and so on..

For the Update-Delete conflicts:

You need to add a location specific identifier column to your table and extend this column to your PK. This will reduce the chances of conflicts occurring. If this is not an option, you will lose data as the change that occurred on the node with the highest Originator ID will win the conflict. This may or may not be okay for you.

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.