Subject transactions and timestamps
Author lt_col_blair

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, 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