This is a classic SQL Remote problem that centers around conflict resolution. It is quite reasonable for an update to bounce back to the remote if a conflict occurs.

Let's imagine that at remote "X", my name is changed from "Reggie" to "Reg" and at the consolidated my name is changed from "Reggie" to "Reginald". SQL Remote runs at remote "X" and sends the change from Reggie->Reg. SQL Remote runs at the consolidated and applies the change, overwriting "Reginald". It detects a conflict, since the old value at the remote is not the same as the current value in the consolidated. SQL Remote now needs to send operations to all remote databases. Everybody except remote "X" will get two updates. The first update of Reggie->Reginald, and then the update of Reginald->Reg. However, the remote that sent the update of Reggie->Reg will only get one update (Reggie->Reginald), because updates sent from Remote "X" are not echoed back to Remote "X" (infinite loops are bad). This would result in all databases expect Remote "X" having a value of "Reg", but remote "X" would have a value of "Reginald". For this reason, updates ARE echoed back to the remote database if a conflict occurred when applying the change at the consolidated. This will result in all databases having "Reg" when they were done applying messages.

How this can get you into trouble :

Let's now imagine that remote "X" changes the row twice. Once from Reggie->Rig, then noticing the typo, from Rig->Reg. The consolidated database changes it from Reggie->Reginald. SQL remote is run at Remote "X" and then SQL Remote is run at the consolidated. The updates that are generate by SQL Remote always include a VERIFY clause that includes the old value at the remote, so that a conflict can be detected at the consolidated. The two updates will look like :

UPDATE customer SET firstName='Rig' VERIFY ('Reggie') where pkey=whatever;
UPDATE customer SET firstName='Reg' VERIFY ('Rig') where pkey=whatever;

When the first update in the message is applied, a conflict will be detected because the current value at the consolidated is 'Reginald', and not 'Reggie'. A conflict is flagged, and this update will be echoed back to remote "X". When the second update in the message is applied, no conflict is detected, because the old value at the remote was 'Rig' and the current value in the consolidated is 'Rig'. The second update is NOT echoed back to remote "X", resulting in inconsistent data in the databases.

How do you work around this issue :

If this is a serious issue for you, consider adding a column that is replicated that tracks the last time the row is modified, but don't just use "timestamp default timestamp" at the consolidated and remote. That's the default you should use at the remote, but not consolidated. If both defaults are the same, the default won't be used at the consolidated when applying messages, because the value is explicitly given in the update from the remote. Use a before update trigger to update this value at the consolidated instead.

CREATE TABLE admin (
  a_pkey bigint NOT NULL DEFAULT GLOBAL AUTOINCREMENT,
  textcol char(64) NULL,
  last_mod timestamp NULL default NULL,
  PRIMARY KEY (a_pkey)
);

create trigger au_admin before update on admin
referencing new as new_row for each row
begin
  set new_row.last_mod = CURRENT TIMESTAMP;
end;

Now, the second update in the message in the above scenario will also be flagged as an conflict, because the last_mod column does not match, as it was changed when the first update occurs.

Reg