> Hi,
>
> I want to do a constant synchronization between one main DB on one
> server and multiple "satellite DBs" on other servers.
> Whenever a record (of the relevant tables) is changed in the main DB,
> an event is sent to a client app that runs on each of the sattelite
> servers. This app then checks for changed records and transfers them
> to it's local DB.
> To mark/locate the changed records each table contains a timestamp
> field that is updated via a trigger.
> The satellite app loads only those records for transfer that have a
> timestamp newer than the time of it's last check.
>
> Now I thought of a situation where this attempt doesn't work:
> 2 users update different records in the same table of the main DB:
>
> 1) Satellite app did it's last check at 12:10:00
> 2) user 1 updates a record at 12:15:00
> 3) user 2 updated a different record in the same table at 12:16:00
> 4) user 2 commits immediately -> event is posted at 12:16:01
> 5) satellite app receives the event and reads all records with TS>12:
> 10:00. It finds the latest change to be 12:16:00, since user 1 hasn't
> commited his transaction yet.
> 6) user 1 commits transaction at 12:17:00
> 5) satellite app receives the event and reads all records with TS>12:
> 16:00. It doesn't find the record user 1 has changed since it has a
> timestamp of 12:15
>
> ...so, short conclusion: my idea using timestamps doesn't work.
>
> Leaves one question:
> Is there a way to find the "records committed since <???>" ?
> Does anyone have another idea, how this could be solved?
>
> Thanks a lot for any hints.
> Lutz Kutscher

there are numerous replication regimes and methodologies around. Check out
www.ibphoenix.com for a full list of tools.
I prefer the method of triggered insertion of PK, tablename, operation
(I/U/D) into a changes table. The replication process is a 2 phase commit of
updates/inserts/deletes to the target database. A special user is created
and all triggers fire unless the replication user is the one doing the
insert/update/delete.
Alan