Bad things happen. Synchronization systems are no exception. However, Pervasync has measures to help you keep things under control.
As you know a sync session has two phases, Check-in and Refresh. Refresh should rarely go wrong, as it is a forced applying of central DB changes to local DB. If it does go wrong, you have to manually fix the error and try Refresh again. As a last resort, the local DB has to be re-loaded by either re-installing the client or re-subscribe the client from server side.
Most of the errors happen in the Check-in phase where client tries to apply local DB transactions to central DB. In the following we will talk about errors due to DB constraint violations and conflicts due to con-current updates of same records from different clients. The emphasis here is prevention.
Errors Due to DB Constraint Violations
The sync client check-in is just like other central DB client transactions. It has to meet all the DB constraints to be committed in central DB.
Create Local DB Constraints via Sync SQL
First thing to do to avoid the constraint violations during Check-in is to ensure the local transactions meet the constraints on client side. Pervasync does not sync central DB constraints to local DB except for NOT NULL and primary key constraints. However, Pervasync has a “Sync SQL” feature that allows you publish SQL statements to be applied to client DB during synchronization. On web admin console, locate the sync schema and click on “Add Sync Sql” to define the desired UNIQUE constraints, CHECK constraints or referential constraints, for example,
ALTER TABLE pvcdemo.tasks ADD CONSTRAINT tasks_unique UNIQUE (NAME)
Instead of local DB constraints, we recommend you ensure the constraints in the client application layer where you can give end users direct, meaningful feedback when a constraint is violated.
Sync Table Ranks: Check-in Order
Even through your local changes meet the referential constraints, during Check-in, you still may encounter referential constraint violations like “Parent Key Not Found” and “Child Record Found”. This has to do with the order of the DML (Data Manipulation Language) operations in the Check-in transaction.
Pervasync uses the sync table ranks to determine the check-in order. When you publish a sync table, you need to assign a rank number that indicates the referential relationship among the sync tables. Parent tables should have a smaller rank number than child tables. For Insert operations, tables with a lower rank number are done first so that parent table is inserted first to avoid the “Parent Key Not Found” error when child table is inserted. Deletes are done in the reverse order to avoid the “Child Record Found” error.
Conflict Detection and Resolution
In an online system, a client could lock a DB row (record) when making changes to it so that the client is sure that it is modifying an up-to-date version of the row. In a sync system, sync client keeps a local copy of the row and makes offline changes to it without locking the central DB row. While the client makes these offline changes, other sync clients or central DB users may be making changes to the same row in central DB. During check-in time, if we do not do anything to handle conflicts, the newly checked-in row will overwrite changes from other sources. This may break your business logic since essentially the client was modifying an out-of-date copy of the row and changes from other clients were not taken into consideration.
Pervasync provides conflict detection and resolution to help you handle the conflicts so that your business requirements are satisfied.
There are three types of conflicts:
-
Insert conflict – Insert causes primary key violation. Likely others have inserted a row with a same primary key value. To avoid insert conflicts, refer to section 5.6 Generating Unique Key Values in Distributed Databases.
-
Update Conflict – Record to be updated has a higher version number indicating it was updated or has been deleted.
-
Delete conflict – Record to be deleted has a higher version number indicating it was updated or has been deleted.
For each type of conflict, we provide three resolution methods:
-
FORCE_CHECK_IN – Apply client transaction ignoring the conflict. For delete operations, records are deleted regardless of their versions. If records to be inserted already exist, update the records. If records to be updated have been deleted, insert the records.
-
DISCARD: Discard the conflicting changes from the client and check in non-conflicting changes.
-
REPORT_ERROR: Treat conflicts as errors and notify the client. This will cause the sync session to fail. In this case client could do a REFRESH_ONLY sync to let the server side changes overwrite the conflicting client side changes. This effectively modified the client transaction so that it won’t cause conflicts in next check-in assuming others won’t make new changes on server again. Before check-in, the client application could give end users a chance to inspect and update the new version of the records. End users should be clear that until the check-in succeeds, all changes are temporary although they are committed locally.
To configure the resolution methods, edit the following parameters in <PERVASYNC SERVER HOME>/config/pervasync_server_<db>.conf.
pervasync.conflict.resolution.InsertExisting=REPORT_ERROR
pervasync.conflict.resolution.UpdateChanged=FORCE_CHECK_IN
pervasync.conflict.resolution.DeleteChanged=DISCARD
REFRESH-ONLY to the Rescue
Pervasync supports three sync directions, TWO_WAY, REFRESH_ONLY and CHECK_IN_ONLY. You can pass in sync direction to the sync() method of client SyncAgent object, or you can use the corresponding pvc.bat/pvc.sh sub commands sync, refresh and checkin.
In a two-way sync session, Check-in is always done before Refresh. The reason is that conflict detection is done on the server side during Check-in. Refresh force-applies changes on local DB without any conflict detection. If Refresh is done before a successful Check-in, some local changes may be silently overwritten.
However, when Check-in encounters some errors or conflicts and cannot proceed, it is often useful to do a REFESH-ONLY sync to intentionally overwrite client changes with server changes so that a sub-sequent Check-in can go through. Examples are server has added or dropped a column, or server sets the conflict resolution method to be REPORT_ERROR and a conflict is found. The algorithm is as follows.
-
Do two-way sync.
-
Catch sync error, present the error to end-user and ask for permission to do a REFESH-ONLY sync.
-
Do a REFESH-ONLY sync and give user a chance to edit the refreshed data.
-
Do two-way sync.
By the way, even a lot of online applications do not lock the records while end users are editing the data. Instead, when the user is done with the editing and submits the data, they do the conflict check and if a conflict is found, a new version of the data is returned for user to edit and re-submit. The algorithm is essentially the same as above.
Pervasync Blog 