2

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:

  1. aaa
  2. bbb
  3. ccc

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:

  1. aaa
  2. bbb
  3. ccc
  4. ddd

Second:

  1. aaa
  2. bbb
  3. ccc
  4. eee

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:

  1. aaa
  2. bbb
  3. ccc
  4. ddd
  5. eee

in every database. How can it be done? Thank you.

user2275785
  • 199
  • 1
  • 19

1 Answers1

4

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

Austin Brougher
  • 526
  • 2
  • 9
  • I know that, but unfortunately I am working on existing project (school project) and I am doing only small part, where I want to develop sync mechanism to replace existing limited sync. Someone can think that conflicts on `INSERT` are common problem in star-shape network with central node, so SymmetricDS will implement some simple mechanism to resolve them. Maybe it can be done using IDatabaseWriterFilter (5.10.2) or using transforming data (4.8). But I can't find out how. – user2275785 Feb 27 '14 at 21:58
  • What database are you using? Can you insert a record and specify your own PK? What kind of volume of data are you working with? How often will records be inserted with the same pk? Sometimes you can divide up the range of the auto increment PK and start each of your client nodes a different starting position. Depending on your db, this could be as easy as inserting a record with the PK column included at the location you want that node to start incrementing. – Austin Brougher Feb 28 '14 at 15:04
  • Central node is using PostgreSQL and clients are using Apache Derby. It's impossible to determine which range can one client use (number of clients is unknown). In previous version of client, there was very limited insufficient sync (only INSERTs without foreign keys). Collisions were solved by "remapping" id to new id determined by central node and new id was sent back to the client which stored it in "remote_id" column. I suppose that database will contain lots of data in future and many collisions will happen... Users can use web app to work with central database directly too. – user2275785 Feb 28 '14 at 17:13