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

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
  HOLDLOCK 
  WHERE where-clause

Example

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.