CODEWITHSUNDEEP
replicationfirebirdsymmetricds
Turab
Turab

Reputation: 191

How are the replication conflicts resolved using a 3rd party?

Been searching for a specific info but couldn't find; forgive me for being new at this.

I will try to replicate a Firebird DB using SymmetricsDS. This is an ERP database; which in my mind will have 1 master and 2 slaves. I will have 2 slave servers which will work locally and local machines will connect them as clients.

Say for example I am a client of local slave 1. I am creating a new customer which will automatically get a customer ID 100. At the same time a client of the local slave (server) 2 creates a new customer and it takes the same customer ID. Now when these two slaves sync to the master; there will be a conflict.

I know this sounds quite noob; you know you can't hide it.

What would be the best approach to prevent this; rather solving?

Upvotes: 1

Views: 147

Answers (2)

Boris Pavlović
Boris Pavlović

Reputation: 64632

I suppose the system is legacy and you don't have the ability to change how it works. In a similar occasion I have solved this problem letting each slave generating sequences. I've added a write filter in symmetricDs on the master node that will intercept each push from a slave and add a unique prefix per slave. If data has to be synced back to the slaves after data is routed to each slave add a write filter to symmetric slave that will strip the added prefix.

For example maximum number of slaves is 99. Let's say slave 1 creates a sequence 198976, assuming the sequence length is 10, use slave's ID, pad left the sequence with zeros and add the slave id as prefix: (0)100198976. If slave 17 generated the same sequence, master node's filter would change it to 1700198976.

If the same data has is changed on the master and has to be sent back to the slave that generated it, write filter on the slave will strip the first two digits (after left padding with 0 in case of one digit slave IDs). Slave 1's sequence from master (0)100198976 will become again 198976; and slave 17's sequence from master 1700198976 will become 198976.

If the whole length of the ID column has been used on the slaves, alter the column on the master by widening the it to accommodate for the width of slave IDs

Upvotes: 0

ain
ain

Reputation: 22759

I don't think there is one "the best" approach. It depends on system specific details what works best... anyway, some options are:

UUID

Use UUID as customer ID. Since version 2.5 Firebird has some built in support for generating and converting UUIDs.

Segmented generators

On each local slave init the customer ID sequence so that IDs generated by it doesn't overlap with other slaves. Ie if you use 32 bit integers as PK and need max two slaves you dedicate top bit as "slave ID". That means that on first slave you start the sequence from zero while at the second you starti it from 2147483648 (bin 1000 0000 0000 0000 0000 0000 0000 0000). See the ALTER SEQUENCE statement for how to set the sequence's starting value.

ID server

You could have a service which generates IDs. Whenever slave needs ID for a customer it recuests it from a special service. To help with the perfomance it probably makes sense to request new IDs in patches and cache them for later use.

Upvotes: 3

Related Questions