I am trying to implement multi-master bidirectional synchronization for one central node and many clients using SymmetricDS. Clients are communicating only with central node (star topology). I have problem with dealing with conflicts in primary keys.
For example database contains table "person" with columns id, name,... In central db I have rows:
First client connects and downloads initial load, so it has same db. Second client do the same.
Now first and second clients create new row in their local db. First:
Second:
They will try sync, but there is conflict in 4. row. This conflict can be easily detected in SymmetricDS, but now I want to resolve it by increment key until it's inserted and then send changes back to the clients... So result will be:
in every database. How can it be done? Thank you.
When dealing with a multi-master bi-directional scenario its not a good idea to use an auto incrementing PK.
Standard practice is to create a GUID for the PK to avoid conflicts between your clients.
See the following site for more details.
Selecting an Appropriate Primary Key for a Distributed Environment