Dimitry Sibiryakov wrote:

>On 27 Jan 2004 at 8:36, Jonathan Neve wrote:
>
>
>
>>> Primary keys of replicated tables must be unique not only on
>>>database level, but in all involved databases.
>>>
>>>
>>>
>>Funny, my replicator has none of these problems! :-) :-) :-)
>>
>>
>
> Oh, really? How do you manage the case when two different records
>were inserted into two different databases with the same value of PK?
>

There are two different situations. Either the PK value is calculated
automatically, as for example, with a generator (in which case something
needs to be done in order to ensure that the same PK value is never
used), or else the PK value is typed in directly by the end user (not a
very good thing, I know), in which case it's up to the end user to make
sure they don't do so.

In the former case, you simply have to tell the replicator how each
table has to be synchronized. If you use a generator, you need to tell
the replicator which one you use, and then when it replicates, it will
fetch the generator value from the server, and update it locally, before
sending the record, thus avoiding all problems. If the PK value is
calculated in any other way, you can define a stored procedure (or any
other SQL statement), that will get executed on the server before
sending the record, in order to get the correct PK value.

In the latter case, in which the end user gives a value to the PK
himself, none of the above is done. The replicator simply looks at the
remote database, and if it sees that a record with the same PK value is
already there, it simply performs an UPDATE. If it sees that there is
nothing in the remote database, it performs an INSERT. If it sees that
there is no such record in the local database either, then it performs a
DELETE. So if two users had modified the same record in two different
databases, then whichever one replicates last will crush the changes
made by the other user. In other words, there is no difference between
two confliting updates (which simply crush each other), and two
conflicting inserts. The one that gets there first does an INSERT, and
the next one does an UPDATE.

This structure makes it so that my replicator shouldn't ever fail,
unless there is a lock violation. If it does fail, it will try again the
following time. It will also be copied into another table so that we can
know that there is a statement that isn't going through. This means that
it should work properly without any problem. If the end-user does
something they shouldn't do (ie, put twice the same PK value, or update
the same record simultaneously), there isn't really anything we can do
about it (how can we know that someone in another database performed a
simultaneous update on the same record?); the replicator has no way of
knowing that this situation isn't normal. What do you do in this sort of
situation? Have you found some way of telling that there has been a
simultaneous update?

Regards,
Jonathan Neve.


[Non-text portions of this message have been removed]