I am in process of doing a SQL Anywhere 5 to SQL Anywhere 12 upgrade. The client uses SQL Remote to synchronize between a consolidated database and roughly 40 remote databases.

In v5, when a given record is updated both at the consolidated database and at the remote database, an error is thrown (good) when DBREMOTE.EXE is run at the consolidated database. In contrast, in v12, no error is thrown (bad) when DBREMOTE.EXE is run at the consolidated database and the latest version of the record is automatically applied. I consider this “bad” because manual intervention is required to determine what the record should actually contain.

When the same row is updated in two places, the update of that record needs to be blocked when DBREMOTE is run and manual intervention is required to determine what the record needs to contain.

So, I need help with:

1) forcing an update conflict situation - what options can I set out of the box to force the update conflict? Is there a UI screen or table to show conflicts? 2) what methods do I need to implement to throw an error and have it display in the DBREMOTE window (as was the case in v5)? 3) I need a sample of code, if I need to implement triggers, etc.

Thanks,

Brendan

asked 05 Jun '12, 17:37

Brendan's gravatar image

Brendan
36115
accept rate: 0%

1

AFAIK in V5.5, conflicts are not detected as errors unless you have coded RESOLVE UPDATE triggers.

Do you have RESOLVE UPDATE triggers in your 5.5 implementation that somehow did not get upgraded?

(05 Jun '12, 20:29) Breck Carter

I have coded no such RESOLVE UPDATE triggers. If they are instituted automatically and I wasn't aware of that, how would I find that out? I didn't find any property for that in Sybase Central v5.5

(05 Jun '12, 20:43) Brendan

Maybe said another way, can I create RESOLVE UPDATE triggers and perform a SIGNAL exception to force the error and fail the update?

(05 Jun '12, 20:48) Brendan

What version of v5 are you using?

Have you set the VERIFY_ALL_COLUMNS option to 'ON'?

FWIW, I'm not aware that the handling of update conflicts has changed between v5.5.05 and current versions at all (but experts like Reg will know better!). To cite the v5.5.05 docs:

Default conflict resolution

By default, the UPDATE still proceeds, so that the User 2 update (the last to reach the consolidated database) becomes the value in the consolidated database, and is replicated to all other databases subscribed to that row.

In general, the default method of conflict resolution is that the most recent operation (in this case that from User 2) succeeds, and no report is made of the conflict. The update from User 1 is lost. SQL Remote also allows custom conflict resolution, using a trigger to resolve conflicts in a way that makes sense for the data being changed.

AFAIK, it's still the simple model as following (I assume each row is identified by a PK):

  • Conflicts can only be detected in the consolidated database, by comparing the contents of the current row in the consolidated and the contents of the UPDATE statement's VERIFY clause (i.e. the previous contents of the remote row). The setting of VERIFY_ALL_COLUMNS will then decide whether differences in all columns or just in the columns that have been updated at the remote will be taken into account. - So if the cons has only changed column1 and the remote has changed column2, and VERIFY_ALL_COLUMNS if 'OFF', then there is no update conflict, as the the current value of column2 in the cons and the old value of column2 in the remote are identical.
  • If there is a difference between the "current values" in the consolidated and the "remote's old values" - then that row will cause the "update conflict" situation.
  • The default conflict behaviour is to simply apply the UPDATE at the consolidated and send it out to other remotes - including the triggering remote. So by default the last UPDATE arriving at the consolidated "wins".
  • For particular conflict resolution, you have to use RESOLVE UPDATE triggers, as Breck has suggested. There are some samples in the docs (and it seems the docs for v5.5 and v12.0.1 are quite similar in that respect - call it a mature feature:), cf. this page.

Note, there's no sample for a "Report the conflict into a table" handling but we could surely give hints.

However, in order to stop SQL Remote when detecting a conflict (if that really is a requirement) instead of an automated conflict handling, I think you would then have to raise an exception from within the UPDATE RESOLVE trigger.

permanent link

answered 06 Jun '12, 03:54

Volker%20Barth's gravatar image

Volker Barth
30.6k304455662
accept rate: 32%

edited 06 Jun '12, 04:02

Comment Text Removed

I have tested what you have indicated regarding raising an exception from within the UPDATE RESOLVE trigger and it works. Many thanks.

The issue is that changes from the consolidated database to the remote site overwrite the changes at the remote site. How can this be similarly blocked? I know that the documentation states that "at remote databases, no RESOLVE UPDATE triggers are fired when a message from a consolidated database contains an UPDATE conflict". I guess the way to avoid situations where the same record is updated at two locations, only one location should really be making a change between syncs.

(06 Jun '12, 13:44) Brendan
Replies hidden

Caveat Emptor: My permit to work on SQL Remote expired some time ago :)

Perhaps you could figure out how to use a regular trigger on the remote database, if there is a timestamp column.

Some of the intricacies of triggers-versus-SQL-Remote are discussed here: http://dcx.sybase.com/index.html#1201/en/sqlremote/wrtrig.html

(06 Jun '12, 16:02) Breck Carter
1

...and ask your new question as a new question, since there are some SQL Remote gurus around here who might think this question's been answered.

(06 Jun '12, 16:03) Breck Carter

I guess the effect you are seeing is expected (but I have not tested this!):

The exception in the UPDATE RESOLVE trigger will prevent the UPDATE from the remote happening at the cons, and the conflicting UPDATE from the cons (or another remote) will reach the remote and update the values there. Therefore the remote update will get lost (although altogether, this should still lead to consistent data).

If you really want to solve such conflicts "manually", I would think you will have to track them in a particular table at the remote (by means of the trigger) and "correct them" afterwards by a manual UPDATE. This update from the cons will then be sent to all remotes as usual. - Another means would be SQL Remote hook procedures...

Note: I still would expect a general rule would be easier: An UPDATE RESOLVE trigger should usually be able to decide itself if the cons or the remote values (or a combination of both) are "as wanted" - and then just continue without an exception. That would be a preferred situation IMHO.

(07 Jun '12, 07:43) Volker Barth
Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:

×78

question asked: 05 Jun '12, 17:37

question was seen: 983 times

last updated: 07 Jun '12, 07:43