Click here to view and discuss this page in DocCommentXchange. In the future, you will be sent there automatically.

SQL Anywhere 11.0.0 » MobiLink - Server Administration » Using MobiLink Technology » Synchronization techniques » Handling conflicts » Detecting conflicts


Detecting conflicts with upload_fetch scripts

If you define an upload_fetch or upload_fetch_column_conflict script for a table, the MobiLink server compares the pre-image of an update to the values of the row returned by the upload_fetch script with the same primary key values. If values in the pre-image do not match the current consolidated values, the MobiLink server detects a conflict.

The upload_fetch script selects a single row of data from a consolidated database table corresponding to the row being updated. A typical upload_fetch script has the following syntax:

SELECT pk1, pk2, ...col1, col2, ...
FROM table-name
WHERE pk1 = {ml r.pk1} AND pk2 = {ml r.pk2} ...
   AND col1 = {ml r.col1} AND col2 = {ml r.col2} ...

See upload_fetch table event.

The upload_fetch_column_conflict event is similar to upload_fetch, but it only detects a conflict when two users update the same column. Different users can update the same row, as long as they don't update the same column, without generating a conflict.

See upload_fetch_column_conflict table event.

You can have only one upload_fetch or upload_fetch_column_conflict script for each table in the remote database.

Locking the row on the consolidated database

It is possible that a row might change on the consolidated database after the upload_fetch script detects a conflict and before the conflict resolution is completed. To avoid this problem, which could result in incorrect data, you can implement the upload_fetch or upload_fetch_column_conflict scripts with a row lock.

In SQL Anywhere consolidated databases, you can use either the UPDLOCK or HOLDLOCK keywords, but UPDLOCK is better for concurrency. For example:

SELECT column-names from table-name WITH (UPDLOCK) 
  WHERE where-clause

For Oracle, DB2 LUW and DB2 mainframe, use FOR UPDATE. For example:

SELECT column-names FROM table-name
  WHERE where-clause
    FOR UPDATE OF column_name1, column_name3, column_name6

Specifying the column names you want to update, as shown in the example above, preserves computer resources and improves performance.

For Microsoft SQL Server, use HOLDLOCK. For example,

SELECT column-names FROM table-name WITH (HOLDLOCK)
  WHERE where-clause

For Adaptive Server Enterprise, use HOLDLOCK. For example,

SELECT column-names FROM table-name
  WHERE where-clause

You define an upload_fetch script. The MobiLink server uses the script to retrieve the current row in the consolidated database and compares this row to the pre-image of the updated row. If the two rows contain identical values, there is no conflict. If the two rows differ, then a conflict is detected and MobiLink calls the upload_old_row_insert and upload_new_row_insert scripts, followed by resolve_conflict.

See Resolving conflicts with resolve_conflict scripts.