Bi directional table replication in symmetricDS
Bi directional table replication is not trivial because you must deal with every kind of conflict that could arise. One example conflict is when a row is inserted on both the server and client that would together violate a unique key. Both the client and the server both allow the insert/update to occur because they do not know that there is an incoming update that would violate the key.
The synchronization engine on both sides think: "Oh no, we both told our users that it was OK to add this row based on what we knew, but we can't synchronize now because it would violate uniqueness."
The synchronization engine has a choice:
1. Take the last insert/update by timestamp and destroy the request that came first. This is undesirable because the first person thought they committed, successfully when in reality their transaction was erased without anyone's consent. 2. Reject both rows, if I can't make everyone happy, I'm not letting anything through, and log the conflict to an external table to be dealt with later. The two users who thought they submitted data will find their transaction in a queue. 3. Merge the rows, take a little from one, and a little from the other, and create a hybrid row. Or take one or the other based on some priority system or based on how filled out it is.
This is just one example of a conflict. There are hundreds of conceivable circumstances where a conflict would arise that you must plan for.
The hundreds of conceivable circumstances where a conflict could happen must have a remedial action defined in the configuration table: sym_conflict
.
You could direct symmetricDS to merge the rows according to rules, deny the transactions until a human has reviewed the rows, or even program it to throw out the baby with the bathwater. This is defined in Chapter 3.8 of the user guide, configuring data conflict and resolution.
The number of potential conflicts are based upon the configuration and limitations of your database table. As you add conditions and limitations on your table, the number of potential conflicts increases exponentially. If you have unique keys, you need to prepare for unique key conflicts and define resolutions. If you have primary/foreign keys on other tables, you need conflict resolutions for those. If you get 90% of the conflicts but miss a few, then when the conflicts occur, the databases will not be identical on client and server.