Re: trying to mirror a db

After a long battle with technology, "Ike" <rxv_at_hotmail.com>, an earthling, wrote:
> If I have two databases, A and B, each with only one table, and the
> tables are supposed to miror each other (yet, B gets interacted with
> independent of A, and vice versa) and the table in A has a field for
> the timestamp of last activity for each record, as well as a field
> for the corresponing id of each record in B that the record in A
> corresponds to.
>
> I am trying to discern an alogirthm for maintining these two tables
> to be concurrent with each other. Does anyone have any ideas, given
> the description above for an algorithm to do this?

This is the grand problem of multimaster replication ;-).

An approach is to put a trigger on the table (in both places) that stores all updates in a secondary table, indicating a timestamp, in each case, as to when each change was made.

You then have a process that looks through the updates tables and tries to apply those updates to the "other" database.

If there are no conflicting updates, this will work out fine.

-> If you could restrict updates to taking place on one node, this

   becomes a single-master replication system; there are various    replication systems that work in precisely this fashion.

-> If you use node-local values for as many attributes as possible,

   then that hopefully minimizes the chances of conflicting    updates.

-> Unfortunately, if the same tuple is updated in both places, you

   need some way of managing conflicts. There is no single good    choice for this :-(.

-- 
(format nil "~S_at_~S" "cbbrowne" "gmail.com")
http://linuxfinances.info/info/spreadsheets.html
THAT COMMAND IS NOT KNOWN TO THIS PROGRAM.
MAYBE YOU SHOULD LOG IN? (TYPE HELP FOR DETAILS)