SQL Anywhere database replication - Re: Replication Anomaly 1

Author

Re: Replication Anomaly 1

Rob Waywell

2006-02-15, 11:23 am

underprocessable

Pavel Karady

2006-02-20, 7:23 am

Hi Rob,

thank you very much for your inquiry.

> Basically, if a remote does 2 updates and the consolidated does 1 or more
> updates between replication cycles, then the first update from the remote
> is detected as a conflict, but the second update is not a conflict.

Got that. Since the remote sends two statements in a corresponding row, so
the VERIFY clause of second statement contains the values from the first
statement, the second UPDATE is not a conflict. In our case, here are the
UPDATEs received at cons:

I. 02/10 17:54:45. UPDATE DBA.t5
SET entry='17:53:51.562 2006/02/10'
VERIFY (entry)
VALUES ('05:05:05.005 2005/05/05')
WHERE id=1
I. 02/10 17:54:45. UPDATE DBA.t5
SET entry='04:04:04.004 2004/04/04'
VERIFY (entry)
VALUES ('17:53:51.562 2006/02/10')
WHERE id=1
I. 02/10 17:54:45. COMMIT

It is clear (for me now) that the second UPDATE is not a conflict, since the
value in VERIFY matches.

> Assuming the default conflict resolution behaviour of "last one in wins",
> then this leads the cons to send down the result of the resolve update for
> the first update but since there was no conflict for the second update,
> then no conflict resolution update is sent down for the second update.

From the remote log, we can see that this has been sent back:

I. 02/10 17:54:48. UPDATE DBA.t5
SET entry='17:53:51.562 2006/02/10'
VERIFY (entry)
VALUES ('02:02:02.002 2002/02/02')
WHERE id=1
I. 02/10 17:54:48. COMMIT

Where takes the consolidate the '02:02:02.002 2002/02/02' value (in the
VERIFY) from? The value has been overwritten twice, but assuming that the
default behavior is to take the value that was there *before the conflicting
UPDATE* (which apparently was '02:02:02.002 2002/02/02'), it makes sense.

Then, the cons takes the value from the first UPDATE statement and pings it
back to remote. Since there was no conflict in the second UPDATE, the value
of second UPDATE (although it's the last and correct) does not get flushed
back.

> In most systems, this behaviour is never observed because the systems
> either:
> a) never make multiple updates to the same column within a replication
> cycle

This is it. As you can see from my example, i did neither. What I did was
update of two different columns. I'll get back to this later.

> or
> b) continue making updates which let the system resolve itself

I can't imagine this (yet) :)

> Since you are observing this behaviour, the common solution is to force a
> conflict. This can be done by tracking a Last_Updated column and having
> the RESOLVE UPDATE trigger set the incoming value of the Last_Updated
> column *backwards* any time a conflict occurs.
>
> eg: SET New_Row.Last_Updated = DateAdd(second, -1, New_Row.Last_Updated)

Yes, this could be done and it's a fine solution. But adding an extra
trigger to every of our tables really isn't what we are eager to do..
fortunately, a simpler solution exists for us, and that's expanding the
trigger to emulate the DEFAULT TIMESTAMP too.

Rob, really thanks for all this.

Now for the updating-different-columns, so I can see, in my example, the
mismatching values are caused by UPDATING the same column in the same record
twice, with first causing the conflict, while the second does not. So, if we
take a look at the two updates issued on the remote database:

-- Remote database - log in as DBA
UPDATE t5
SET userid = 'Monicca'
WHERE id = 1;

UPDATE t5
SET entry = '2004-04-04 04:04:04.004'
WHERE id = 1;

COMMIT;

and we see from the cons log (mentioned in this text), what was actually
sent, we can see that from the 1st UPDATE, only changing the "entry" column
has been sent. This looks like a strange behavior to me so far.

The trigger has updated the value of "userid" to 'DBA', because we're logged
in as DBA and the trigger is BEFORE UPDATE, and then, the value is changed
immediately to 'Monicca', at least that's what I think. But it never gets
replicated.

Why?

Pavel

Pavel Karady

2006-02-20, 7:23 am

Additional information...

> The trigger has updated the value of "userid" to 'DBA', because we're
> logged in as DBA and the trigger is BEFORE UPDATE, and then, the value is
> changed immediately to 'Monicca', at least that's what I think. But it
> never gets replicated.
> Why?

It's not all what happens. The BEFORE UPDATE trigger updates the userid to
'DBA', then the userid gets regularly updated to 'Monicca', but the
subsequent UPDATE, the UPDATE of "entry" column, sets the userid back to
'DBA' thanks to the trigger.

The message agents are running *without* the -t switch.

So I guess the regular UPDATE is not replicated, because the agent sees no
difference between 'DBA' at the start of transaction and 'DBA' in the end of
transaction..?

Pavel

Sponsored Links