Subject RE: [firebird-support] Re: Database replication - problem with uncommited transactions.
Author Alan McDonald
> I think I need to explain a little bit further my problem.
> When the replication process starts it looks on another table which was
> the last record sent from the audit table to the destination database.
> The process should send cronologically all records following the last

actually the chronology isn't or shouldn't be relevant. if a record is
inserted and then updated 15 times, it's the last, most recent committed
update which is relevant. The fact that you replicate that same record more
than once in a replication cycle is also irrelevant. IN this case, you would
replicate the insert once, then replicate the update 15 times with exactly
the same content. All you need to do is manage the inserts first, then the
updates then the deletes. So if your source finally deletes the record
before a repl cycle begins, the record will be inserted, updated 15 times,
then deleted.

> sent record. The problem is that sometimes when the process starts
> there
> are some uncommited records in the audit table between other commited

if they are uncommitted, then they can't be seen so the repl engine can't
act on them.

> records. In this situation the process sends all visible records and
> saves which record is the NEW "last sent record". Sometime after the

the engine should use a 2 phase commit and finally delete the the audit
record after taking appropriate action. The audit table is or should be
empty after a repl cycle.

> replication ends, those uncommited records are commited and are never
> sent since they got primary keys previous to the new last sent record.
> This is why I must only send those audit records which have primary
> keys
> that are previous to the uncommited records.
> Hope this clarifys my problem.
> Thanks.