Lesson 10: Creating a table for conflict detection and resolution

This lesson assumes you have completed all preceding lessons. See Lesson 1: Setting up a MobiLink consolidated database.

Conflicts can arise during the upload of rows to the consolidated database. If two users modify the same row on different remote databases, a conflict is detected when the second row arrives at the MobiLink server. You can detect and resolve conflicts using synchronization scripts.

Inventory example

Consider a scenario where two clients are selling up to ten items simultaneously. The first client sells three items, so they update the inventory on their client database, sync_mlintro_remote, to indicate seven remaining items. Their database then synchronizes with the consolidated database, mlintro_consdb. The second client then sells four items, so they update the inventory on their remote database, sync_mlintro_remote2, to indicate six remaining items. A conflict is detected when they attempt to synchronize with mlintro_consdb because the inventory value has changed.

You must obtain the following row values to programmatically resolve this conflict:

The current value in the consolidated database.

The value in the consolidated database is 7 after sync_mlintro_remote synchronizes.
The new row value uploaded by the sync_mlintro_remote2 database.
The old row value obtained by the sync_mlintro_remote2 database during the previous synchronization.

You can use the following business logic to calculate the new inventory value and resolve the conflict:

current consolidated - (old remote - new remote)

For this inventory example, the following values are substituted into the business logic:

7 - (10-6) = 3

The (old remote - new remote) expression provides the number of items sold by second client rather than the absolute inventory value.

Create the conflict resolution tables

You create additional tables in the consolidated database for conflict resolution purposes. These tables are named Product_old and Product_new. Their purpose is to temporarily store problematic values in the event of a conflict.

Connect to your consolidated database from Interactive SQL if you are not already connected.

You can start Interactive SQL from Sybase Central or at a command prompt.

From Sybase Central, right-click the MLconsolidated - DBA database and click Open Interactive SQL.

At a command prompt, run the following command:

dbisql -c "DSN=mlintro_consdb"

Execute the following SQL statements:

CREATE TABLE Product_old (
    name            VARCHAR(128) NOT NULL PRIMARY KEY,
    quantity        INTEGER,
    last_modified   TIMESTAMP DEFAULT TIMESTAMP
);

CREATE TABLE Product_new (
    name            VARCHAR(128) NOT NULL PRIMARY KEY,
    quantity        INTEGER,
    last_modified   TIMESTAMP DEFAULT TIMESTAMP
);

Proceed to Lesson 11: Creating scripts for conflict detection and resolution.