upload_update to resolve conflicts

[former_SQLA_member1694913]

I've put together an upload_update stored procedure to handle conflicts. Everything seems to be working except the client is still getting the -828 error:

E: 2019-01-19 18:28:11 SQL statement failed: (-828) A conflict with the dbmlsync download phase was detected

The sp has an update statement with a where clause comparing the current consolidated column value to the pre-image client value. I have messages being sent to the console from the sp and I can see the current value <> the pre-image value. Then the @@rowcount is 0. Then an update statement is ran setting the consolidated current value to the post-image client value. The conflict should be resolved. And I've confirmed that the values are as the should be at the consolidated and client.

Is it expected, even if conflict detection and resolution is in the upload_update script, to still get the dmblsync -828 error?

Appreciate the help.

[VolkerBarth · Accepted solution]

In my understanding, this error is displayed when the download contains rows that have been modified on the remote after the upload has been sent, so it is not related to conflicts at the consolidated.

Is there a chance there are transactions in the remote that can modify the uploaded rows after the sync process has started? (If so, the LockTables extended option may help.)

[regdomaratzki · Accepted solution]

I've upgrade Volker's comment to an answer. The -828 error is reported by dbmlsync because a row at the remote database was modified after the upload was sent, and that same row was also in the download stream from the MobiLink Server, so the change would be over-written. Volker's suggestion to look into the LockTables extended option is also excellent.

This error is unrelated to the coding of the upload_update script. You need to examine what is occurring at the remote site after the upload is sent. Running dbtran on the transaction log (I would suggest -a -z -d switches) could help you sort out why the conflict is occuring at the remote site if locking tables during the synchronization is not a viable option.

Reg

[former_SQLA_member1694913 · Accepted solution]

Thank you Reg and Volker. I worked on the upload_update fix after reading the sa17 mobilink server management docs where it said that there were three options to handle conflicts: 1. manage the upload_update script (as noted above) 2. use a resolve_conflict script with upload_fetch(not the easier option) 3. use an end_synchronization script ( with no example).

I'll take a look a look at the transaction log on the remote.

[VolkerBarth · Accepted solution]

You are relating to that doc topic, right?

To clarify: This topic deals with conflicts from the MobiLink consolidated/server's point of view, in particular update conflicts, as described here. - Basically, the remote's previous state of the row is different from the consolidated's current state of that row.

In contrast, the type of conflict marked by a -828 error appears at the remote, when a row that should be downloaded from the cons has been modified on the remote after the update has been sent (and therefore has been modified outside of the syncing process). - That different situation is not covered by the MobiLink's server-wise conflict handling, so to solve that, the "update conflict strategies" from the mentioned doc topic won't help. - As that situation appears at the remote site, it is explained in the "MobiLink and SAP HANA Remote Data Sync - Client Administration" docs in the topic Initiation of synchronization.

[former_SQLA_member1694913 · Accepted solution]

Volker, thanks for the clarification. It makes sense now that the download is failing and what's causing it. I looked at the remote transaction logs and was able to pinpoint the update during a synchronization session but after the upload was sent. Question now is, is Locking the only solution to this? I've been looking at the hook procedures, particularly the error handling hooks in the hopes of finding a way to ignore these errors. But, it also seems like dbmlsync is handling it correctly by rolling back the download and forcing another sync in the hopes of sending the conflicted data in the next upload.

[VolkerBarth · Accepted solution]

Question now is, is Locking the only solution to this?

Well, apparently the "rollback & retry" approach is another solution, in case such concurrent updates are rare. Otherwise, I guess that Reg can provide way more hints...