SQL Anywhere database replication - Strange update bounce back corrupting data (ASA 9.0.2)

Author

Strange update bounce back corrupting data (ASA 9.0.2)

Jeremy Swift

2005-04-21, 9:23 am

I have a situation where series of inserts and updates to tables
(including trigger-generated inserts and updates) in one ASA 9.0.2
(3044) database have successfully replicated to the consolidated db
but then a couple of updates have replicated back from consolidated to
remote again. These reapply intemediate results to the first database
leaving the data inconsistent. Data on the consolidated is left in the
correct state.

The bouncing statements are the result of direct updates from the
application and not triggers. They each update a single row in a
single table. This pair of statements is followed by a near identical
pair (same tables, same PK values, same columns updated, just later
values) within the same transaction scope that don't bounce.

No FKs, no triggers that would behave differently, no constraints.
i.e. no reason for the bounce back. Can anybody see what I am missing?

Reg Domaratzki \(iAnywhere Solutions\)

2005-04-21, 1:23 pm

Multiple updates to the same row on the remote which cause a conflict on the
consolidated will show this problem. Assume the following scenario :

Starting Point : c1 = 21 for pkey value 1 on both consolidated and remote

On cons :

update t1 set c1 = 22 where pkey = 1

On remote :

update t1 set c1 = 23 where pkey = 1
update t1 set c1 = 24 where pkey = 1
update t1 set c1 = 25 where pkey = 1

Now, run dbremote on the remote, then the consolidated. The message
generated from the remote contains SQL that also contains the old values
from the remote. The updates look something like :

update t1 set c1 = 23 verify (c1) values ( 21 ) where pkey = 1
update t1 set c1 = 24 verify (c1) values ( 23 ) where pkey = 1
update t1 set c1 = 25 verify (c1) values ( 24 ) where pkey = 1

When the messages are applied to the consolidated database by dbremote, we
check to see if the old value from the remote matches the current value on
the consolidated. If it doesn't, then a conflict has occurred. When a
conflict occurs, RESOLVE CONFLICT triggers on that table fire, and given a
lack of these triggers, the default is that "last one in wins". Normally,
updates from rem1 are not sent back to rem1 the next time messages are sent,
but in this case, the update that caused the conflict needs to be bounced
back to rem1, because the update on the consolidated that sets the value to
22 will be sent down to rem1. We also send the update that sets the value
to 23 back to rem1, so it will be back in synch after the conflict.

The problem occurs on the next update in the message. The current value on
the consolidated is 23 and that matches the old value from the remote, so no
conflict occurs, and the second (and third) update is not echoed back to the
remote.

The next time this row is updated on the remote, your databases will be back
in synch. If you update the row multiple times again, and another conflict
occurs, you won't be back in synch. You can also work around this issue by
adding a timestamp column to the table with a default of timestamp. This
guarantees that every update will cause a conflict, since the timestamp
values won't match.

--
Reg Domaratzki, Sybase iAnywhere Solutions
Sybase Certified Professional - Sybase ASA Developer Version 8
Please reply only to the newsgroup

iAnywhere Developer Community : http://www.ianywhere.com/developer
iAnywhere Documentation : http://www.ianywhere.com/developer/product_manuals
ASA Patches and EBFs : http://downloads.sybase.com/swx/sdmain.stm
-> Choose SQL Anywhere Studio
-> Set "Platform Preview" and "Time Frame" to ALL

"Jeremy Swift" <jeremy.swift@wmann.com> wrote in message
news:l3cf6110bi829sk
r7r8poceubvtt2l3g4l@
4ax.com...
> I have a situation where series of inserts and updates to tables
> (including trigger-generated inserts and updates) in one ASA 9.0.2
> (3044) database have successfully replicated to the consolidated db
> but then a couple of updates have replicated back from consolidated to
> remote again. These reapply intemediate results to the first database
> leaving the data inconsistent. Data on the consolidated is left in the
> correct state.
>
> The bouncing statements are the result of direct updates from the
> application and not triggers. They each update a single row in a
> single table. This pair of statements is followed by a near identical
> pair (same tables, same PK values, same columns updated, just later
> values) within the same transaction scope that don't bounce.
>
> No FKs, no triggers that would behave differently, no constraints.
> i.e. no reason for the bounce back. Can anybody see what I am missing?

Jeremy Swift

2005-04-21, 8:23 pm

Reg,

Thanks for the comprehensize response, but my situation doesn't
involve any non-replicated updates on the consolidated db. Here's a
picture of what's happening.

On the remote we have:
....
Begin transaction
update t1 set c1=21 where pkey = 1
update t2 set cA=21 where pkeyA = 1 and pkeyB = 'X'
update t1 set c1=22 where pkey = 1
update t2 set cA=22 where pkeyA = 1 and pkeyB = 'X'
commit
....

Translating the log file shows these applied in the order listed, by
one of the users.

Translating the log file on the consolidated confirms that after
replication the identical transactions were applied and in the same
order. There are single additional triggered inserts after updates two
and four and these are shown as successfully done in the log & the
data, by the dba (ie replication) account.

Then after the next replication cycle the remote database log shows:
....
update t1 set c1=21 where pkey = 1
update t2 set cA=21 where pkeyA = 1 and pkeyB = 'X'
....

by its replication account (dba).

In other words the first two statements from our original set have
been reapplied, but not the last two. This means that e.g. the
consolidated db correctly shows t1.c1 as 22, but the remote db shows
t1.c1 as 21.

Replication logs match these. Also there are no other updates in any
of the logs on the primary keys involved.

Incidentally, I said that the databases were 9.0.2, but the remotes
also include one that is still at 9.0.1 (1862). The problem has
occurred more than once but not every time the same part of the
application runs.

Jeremy

On 21 Apr 2005 10:14:01 -0700, "Reg Domaratzki \(iAnywhere
Solutions\)" < Spam_bad_rdomarat@ia
nywhere.com> wrote:

>Multiple updates to the same row on the remote which cause a conflict on the
>consolidated will show this problem. Assume the following scenario :
>
>Starting Point : c1 = 21 for pkey value 1 on both consolidated and remote
>
>On cons :
>
>update t1 set c1 = 22 where pkey = 1
>
>On remote :
>
>update t1 set c1 = 23 where pkey = 1
>update t1 set c1 = 24 where pkey = 1
>update t1 set c1 = 25 where pkey = 1
>
>Now, run dbremote on the remote, then the consolidated. The message
>generated from the remote contains SQL that also contains the old values
>from the remote. The updates look something like :
>
>update t1 set c1 = 23 verify (c1) values ( 21 ) where pkey = 1
>update t1 set c1 = 24 verify (c1) values ( 23 ) where pkey = 1
>update t1 set c1 = 25 verify (c1) values ( 24 ) where pkey = 1
>
>When the messages are applied to the consolidated database by dbremote, we
>check to see if the old value from the remote matches the current value on
>the consolidated. If it doesn't, then a conflict has occurred. When a
>conflict occurs, RESOLVE CONFLICT triggers on that table fire, and given a
>lack of these triggers, the default is that "last one in wins". Normally,
>updates from rem1 are not sent back to rem1 the next time messages are sent,
>but in this case, the update that caused the conflict needs to be bounced
>back to rem1, because the update on the consolidated that sets the value to
>22 will be sent down to rem1. We also send the update that sets the value
>to 23 back to rem1, so it will be back in synch after the conflict.
>
>The problem occurs on the next update in the message. The current value on
>the consolidated is 23 and that matches the old value from the remote, so no
>conflict occurs, and the second (and third) update is not echoed back to the
>remote.
>
>The next time this row is updated on the remote, your databases will be back
>in synch. If you update the row multiple times again, and another conflict
>occurs, you won't be back in synch. You can also work around this issue by
>adding a timestamp column to the table with a default of timestamp. This
>guarantees that every update will cause a conflict, since the timestamp
>values won't match.

Jeremy Swift

2005-04-22, 9:23 am

Reg,

Aha, think I've got it!

Taking your explanation of the conflict behaviour and applying it to
the situation I described in my last post I went and looked back at
earlier transactions affecting "t1" and "t2" and found a before
trigger that was incrementing "c1" and "cA". By firing on both sides
of the replication the consolidated server holds value for "c1" and
"cA" that are different from the remote, so there is a replication
conflict when my bouncing updates run. I'll make the before trigger
conditional on current remote user being null and all will be well.

Thanks for the speedy and pertinent advice! Hope you didn't spend too
long on my previous post before getting to this one ;-)

Jeremy

On 21 Apr 2005 14:23:02 -0700, Jeremy Swift <jeremy.swift@wmann.com>
wrote:
[color=darkred]
>Reg,
>
>Thanks for the comprehensize response, but my situation doesn't
>involve any non-replicated updates on the consolidated db. Here's a
>picture of what's happening.
>
>On the remote we have:
>...
>Begin transaction
>update t1 set c1=21 where pkey = 1
>update t2 set cA=21 where pkeyA = 1 and pkeyB = 'X'
>update t1 set c1=22 where pkey = 1
>update t2 set cA=22 where pkeyA = 1 and pkeyB = 'X'
>commit
>...
>
>Translating the log file shows these applied in the order listed, by
>one of the users.
>
>Translating the log file on the consolidated confirms that after
>replication the identical transactions were applied and in the same
>order. There are single additional triggered inserts after updates two
>and four and these are shown as successfully done in the log & the
>data, by the dba (ie replication) account.
>
>Then after the next replication cycle the remote database log shows:
>...
>update t1 set c1=21 where pkey = 1
>update t2 set cA=21 where pkeyA = 1 and pkeyB = 'X'
>...
>
>by its replication account (dba).
>
>In other words the first two statements from our original set have
>been reapplied, but not the last two. This means that e.g. the
>consolidated db correctly shows t1.c1 as 22, but the remote db shows
>t1.c1 as 21.
>
>Replication logs match these. Also there are no other updates in any
>of the logs on the primary keys involved.
>
>Incidentally, I said that the databases were 9.0.2, but the remotes
>also include one that is still at 9.0.1 (1862). The problem has
>occurred more than once but not every time the same part of the
>application runs.
>
>Jeremy
>
>On 21 Apr 2005 10:14:01 -0700, "Reg Domaratzki \(iAnywhere
>Solutions\)" < Spam_bad_rdomarat@ia
nywhere.com> wrote:
>

Reg Domaratzki \(iAnywhere Solutions\)

2005-04-22, 9:23 am

I was about two sentences into a response late last night that basically
said "but I thought that's what I described" when I decided to respond this
morning instead. I'm glad I waited, and more also glad you figured this
out.

--
Reg Domaratzki, Sybase iAnywhere Solutions
Sybase Certified Professional - Sybase ASA Developer Version 8
Please reply only to the newsgroup

iAnywhere Developer Community : http://www.ianywhere.com/developer
iAnywhere Documentation : http://www.ianywhere.com/developer/product_manuals
ASA Patches and EBFs : http://downloads.sybase.com/swx/sdmain.stm
-> Choose SQL Anywhere Studio
-> Set "Platform Preview" and "Time Frame" to ALL

"Jeremy Swift" <jeremy.swift@wmann.com> wrote in message
news:sosh61t2flpem2l
rcsb2v7v5kep5gpmvra@
4ax.com...
> Reg,
>
> Aha, think I've got it!
>
> Taking your explanation of the conflict behaviour and applying it to
> the situation I described in my last post I went and looked back at
> earlier transactions affecting "t1" and "t2" and found a before
> trigger that was incrementing "c1" and "cA". By firing on both sides
> of the replication the consolidated server holds value for "c1" and
> "cA" that are different from the remote, so there is a replication
> conflict when my bouncing updates run. I'll make the before trigger
> conditional on current remote user being null and all will be well.
>
> Thanks for the speedy and pertinent advice! Hope you didn't spend too
> long on my previous post before getting to this one ;-)
>
> Jeremy
>
> On 21 Apr 2005 14:23:02 -0700, Jeremy Swift <jeremy.swift@wmann.com>
> wrote:
>
the[color=darkred]
remote[color=darkred
]
we[color=darkred]
on[color=darkred]
a[color=darkred]
Normally,[color=dark
red]
sent,[color=darkred]

bounced[color=darkre
d]
to[color=darkred]
value[color=darkred]

on[color=darkred]
so no[color=darkred]
the[color=darkred]
back[color=darkred]
conflict[color=darkr
ed]
by[color=darkred]
This[color=darkred]
>

Sponsored Links