The Final Wrap
Having discussed most of the issues, it is now time to introduce some topics to a greater degree of depth to finalize the Asta SkySync overview. Firstly, recall earlier where the concept of primary keys was introduced. A question that may have occurred to you at the time was "But how do disconnected users ensure that they have a unique PK for the consolidated database if they add a new row?"
Maintaining Unique Primary Keys With Primary Key Pools
It is often convenient to use a single column as the primary key for tables. For example, each customer should be assigned a unique identification value. If all the sales representatives work in an environment where they can maintain a direct connection to the database, assigning these numbers is easily accomplished. Whenever a new customer is inserted into the customer table, automatically add a new primary-key value that is greater than the last value.
In a disconnected environment, assigning unique values for primary keys when new rows are inserted is not as easy.
When a sales representative adds a new customer, they are doing so to a remote copy of the Customer table. You must prevent other sales representatives, working on other copies of the Customer table, from using the same customer identification value.
A remote database is usually a small subset of the consolidated database. For this reason, you cannot normally use automatically generated primary-key values. Features that automatically generate primary-key values rely on having the entire table available.
One efficient means of solving this problem is to assign each user of the database a pool of primary-key values to assign as the need arises. For example, you can assign each sales representative 100 new identification values. Each sales representative can freely assign values to new customers from his own pool. To implement a primary-key pool:
- Add a new table to the consolidated database and to each remote database to hold the new primary-key pool. Apart from a column for the unique value, these tables should contain a column for a user name, to identify who has been given the right to assign the value. Note that there is one entry per key (not per pool).
- Assign more new values to remote users who insert many new entries or who synchronize infrequently.
- Every time a remote user synchronizes, ensure that you top up their pool and download the new pool values to them.
- Modify the application that uses the remote database so that when a user inserts a new row, the application uses one of the values from the pool. The application must then delete that value from the pool so it is not used a second time.
- Ensure that, upon processing the upload stream, the Asta SkySync synchronization server deletes rows from the consolidated pool of values that a user has deleted from his personal value pool in the remote database.
You may also recall from earlier, that we introduced the topic of conflict resolution. This is how conflict resolution is dealt with in Asta SkySync:
Conflicts arise during the upload of rows to the consolidated database. If two users modify the same row, a conflict is detected when the second of the rows arrives at the Asta SkySync synchronization server. When conflicts can occur, you should define a process to compute the correct values, or at least to log the conflict.
No conflicts arise in the remote database as a result of synchronization. If a downloaded row contains a new primary key, the values are inserted into a new row. If the primary key matches that of a pre-existing row, the other values in the row are updated.
Conflicts are not the same as errors. Conflict handling can be an integral part of a well-designed application, allowing concurrency, even in the absence of locking.
Note that for conflict resolution to work, two rows must be passed. One contains the old values; the other contains the new values. Only if the old values match the consolidated data does the update occur. If they do not match, a conflict has occurred and we pass to conflict resolution. This should only be true of updates. This is because PK pools guarantee PK uniqueness on inserts and we really don't care if someone else already deleted a record before we did.
Asta SkySync permits the user to declare whether they want an attended or an unattended synchronization / replication to occur. If the session is attended, the user is notified of every conflict and can choose between the options offered to resolve that conflict. The options are Skip, Cancel, Correct, Refresh and Merge. If the session is unattended, the user chooses from consolidated takes precedence, remote takes precedence, latest takes precedence, earliest takes precedence, ignore and log and ignore.
When rows are deleted from the consolidated database, there needs to be a record of the row so it can be removed from any Remote databases that have the row.
One technique is to not delete the row. Data that is no longer required can be marked as inactive by changing a status column in the row.
A second technique is to have a shadow table that stores the primary-key values of deleted rows. When a row is deleted, an entry is made in the shadow table.
Handling failed downloads
Bookkeeping information about what is downloaded must be maintained in the download transaction. This information is updated atomically with the download being applied to the Remote database.
If a failure occurs before the entire download stream is applied to the Remote database, the Asta SkySync synchronization server does not get confirmation for the download and rolls back the download transaction. Since the bookkeeping information is part of the download transaction, it is also rolled back. Next time the download stream is built, it will use the original bookkeeping information.