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:
Handling Conflicts
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.
Handling deletes
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.
|
