How do you get Mobilink to synchronize tables so that the most recent updated table is not overwritten?

Question

Here is the scenario. I have an Oracle consolidated database. I am using Mobilink to synchronize Oracle with a SqlAnywere database that is being used on a handheld. If userA changes a record in the remote DB on their handheld device to "updated first" and then 10 minutes later userB updates the same record on their handheld device to "updated second" I want the consolidated database to always show "updated second" after the two devices are synchronized. Currently if userB synchronizes before userA the consolidated database will read "updated first".

Answer 1

Right now, you are using default conflict resolution in the MobiLink Server, so by default the last synch in wins. You'll need to implement your own conflict resolution scheme to handle this.

This will require two things to happen at the remote database :

1) There will need to be a column in the table at the remote database that synchronizes to the consolidated database that tracks the time that records were updated at the remote site.

2) You will have to trust the system clock at the remote sites. Should people figure out how conflicts are being resolved and they want to make sure their data wins the conflict, there is nothing stopping a user from changing the system time on their remote device to next week, updating their data, changing the system time back and then synchronizing.

At the consolidated, you'll need to implement conflict resolution, which isn’t that hard. As long as your table does not include any blobs, you can write your conflict resolution in the upload_update event for the table. Let’s assume a table at the remote database that looks like :

create table Admin (
  admin_id           bigint default global autoincrement(1000000) primary key,
  data               varchar(64) not null,
  rem_last_modified  timestamp not null default timestamp
);

Let’s also assume a table at the consolidated that looks very similar, but also has another last modified column to track when rows have changed at the consolidated.

create table Admin (
  admin_id           bigint default global autoincrement(1000000) primary key,
  data               varchar(64) not null ,
  rem_last_modified  timestamp not null default ‘1900-01-01’,
  cons_last_modified timestamp default timestamp
);

Typically, your upload_update event would look something like this :

call ml_add_table_script( 'v1', 'Admin', 'upload_update',
'update Admin set data = {ml r.data}, 
              rem_last_modified = {ml r.rem_last_modified}  
 where admin_id = {ml r.admin_id}'
);

Instead, we’ll re-write your upload_update event to call a stored procedure and also pass in the old row values from the remote database.

call ml_add_table_script( 'v1', 'Admin', 'upload_update',
'call admin_upload_update( {ml r.admin_id}, 
    {ml r.data}, {ml r.rem_last_modified}, 
    {ml o.data}, {ml o.rem_last_modified}’
);

The key to your stored procedure is that we are going to do an update, but the where clause of the update will include both the primary key values AND the old row values from the remote database. If someone has changed the row at the consoliated, this update will update zero rows, and we know a conflict occurs. If it updates a row, then there was no conflict. Your stored procedure will look something like this ( pseudo-SQL below ) :

create procedure admin_upload_update ( 
    @admin_id bigint, 
    @new_data varchar(64), 
    @new_rem_lmod timestamp,
    @old_data varchar(64), 
    @old_rem_lmod timestamp 
)
begin
    declare @cur_rem_lmod timestamp;
    update admin set data = @new_data, rem_last_modified = @new_rem_lmod
     where admin_id = @admin_id 
       and data = @old_data 
       and rem_last_modified = @old_rem_lmod;
    if @@rowcount = 0 then
        // conflict !!
        select rem_last_modified into @cur_rem_lmod 
          from admin where admin_id = @admin_id;
        if @new_rem_lmod > @cur_rem_lmod then
            // update using new_data and new_rem_lmod
        else 
            // do nothing, current values in cons wins
        end if;
    end if;  
end;

For more information on conflict resolution, see the following section of the v10 docs :

MobiLink - Server Administration

Synchronization Techniques

Handling conflicts

http://dcx.sybase.com/index.php#http%3A%2F%2Fdcx.sybase.com%2Fhtml%2Fdbmlen10%2Fml-conflicts-synch.html

Answer 2

Assuming you have implemented timestamp-based downloads or snapshot downloads, then the remote will be updated to match the consolidated, if the consolidated was updated by another remote since the last synchronization.

BTW, the type of conflict resolution you want is available if you set up a synchronization model (http://dcx.sybase.com/index.php#http%3A%2F%2Fdcx.sybase.com%2Fhtml%2Fdbmgen10%2Fmg-mg-about-s-5060632a.html), available in version 10 and above. Either in the Create Synchronization Model wizard, or in the Mappings page after the model is created, you can choose whether you want row-based or column-based conflict detection, and different types of conflict resolution. What you want corresponds to the "timestamp" conflict resolution option, where you pick an existing timestamp column.

FYI, the wizard explains the options more than the Mappings page, so I recommend exploring those options in the wizard first. Note that if the "Newer wins, using a timestamp column that you maintain" option is grayed, it means that there is no timestamp column in the synchronized table(s).

Once you've created a model, you can preview the generated scripts in the Events page. When you have finished setting up the model, you deploy it to create SQL and batch files and/or apply SQL directly to the databases.