Question Transaction question.

ron

Member
OE 11.7.4 on Linux

Hello ....

We're developing a system to extract details from database "A" using replication triggers. When the trigger executes we want to copy the details we're after to table "X" -- and write a controlling record to table "Y". So ... an "X" record will contain the data we want, and the corresponding "Y" record will only have date/time, transaction number and ROWID.

A "chaser" program will scan table "Y" and use each record to get the data in "X" and send it away.

I know that's just a simplistic overview ... lots of details under the cover.

The issue is making sure that the details copied into table "X" have actually been committed. The trigger logic will copy the transaction number and the "chaser" program will check whether the transaction is still active. If it is not active we take that to mean that the transaction is no longer active -- and therefore the update has been committed.

Now how the difficult bit ....

If all of these records are in the same database (ie, "A") then the update and the records written to "X" and "Y" are all scoped by the exact same transaction and so if for any reason the update is aborted and rolled-back the update will be undone and the records in "X" and "Y" will be removed. All Good.

But how about if tables "X" and "Y" are in a separate database "B"? The transaction scoping the activity on "X" and "Y" is local to database "B" and presumably if the original transaction is rolled-back then the records in "X" and "Y" will remain -- which is a problem.

Can anyone confirm that this is what will happen? And if it will happen -- is there a way to "fix" the problem??

Ron.
 

Rob Fitzpatrick

ProgressTalk.com Sponsor
Hi Ron,

What you are building sounds a lot like Change Data Capture. Your X is the change table and Y is the change-tracking table.

You're on 11.7.4, where the CDC add-on product is available. Why not use CDC instead of building all of these mechanisms manually?
 

TomBascom

Curmudgeon
I agree with Rob - why reinvent the wheel? Not only does this duplicate CDC, it is also basically a clone of the old Pro2 trigger based replication. My guess is that somebody doesn't want to buy the licenses and thinks it is cheaper to have the local coders "just" whip something up ;)

To answer the question - if you split the process across two databases then, no, you are not guaranteed that the updates will 100% always in every possible situation be synchronized. There is a (very narrow) window when database B has committed but database A has not quite finished committing. Unless you enable two-phase commit but that comes with so many performance and management issues that nobody actually does it (and it wouldn't surprise me if it silently disappears from the product just like "raw partitions" did...)

I have seen implementations of Pro2 with the Pro2 replQueue on a different database than the source data. The thought was that this would reduce pressure on transaction throughput in the main database. To my eyes that effort failed - it did not result in a reduction and actually seems to have caused *more* bi activity (because now two bi files need to be updated instead of one).
 

ron

Member
I should have mentioned that we spent a lot of time with Progress discussing both Pro2 and CDC to satisfy this requirement but both presented problems. It had nothing at all to do with licencing -- we would have preferred to use either Pro2 or CDC.

Our requirement is to capture every individual change. Pro2 sometimes coalesces updates -- so it wouldn't work. CDC would work perfectly well technically but it writes all of its data into the same DB. We have over 25 copies of our primary DB and the difficulties presented by all the CDC data being copied to each one was "too much".

Tom, I believe you have confirmed that there is a problem at least by having the (equivalent of) the ReplQueue table in a second DB. Maybe we have to compromise and put just that one table in the primary DB and the others (the large ones containing the change data) in a second DB.
 
Last edited:

Rob Fitzpatrick

ProgressTalk.com Sponsor
Then I assume some other requirement or constraint that we haven't yet heard was what prevented you from moving forward with CDC?
 

ron

Member
I edited the reply to say why Pro2 and CDC were not used -- but maybe you missed the edit.
 

TomBascom

Curmudgeon
Another possibility would be to use audit logs. You would still have the problem of enabling it and properly managing it everywhere but if you don't need the replication in real time it might have some attractions.

(Yes, your edit didn't appear until after my "that's intriguing".)
 

ron

Member
Unfortunately, Tom, the changes are required in "near real time". We did look at the audit feature in Pro2 -- but it was much too "batchy". We currently do use Auditing -- but prefer to avoid it. :(
 

ron

Member
We believe the transaction problem will be handled by having the equivalent of the replqueue in the source database ("A") and all the rest in the second database. That's how we are proceeding.

I want to include statistical accounting ... count a replication initiation by a trigger -- and then count as the item flows through two more stages. There is a table ("tally") keyed on table name + date + hour to hold the statistics. But I realise that will be a very bad move because a "tally" record will be locked within the transaction that the trigger is servicing ... and that could persist for several minutes -- causing a major bottleneck.

Can anyone suggest a nice easy way to deal with this? I can get the trigger to write to an OS file for some other program to use to update tally -- but that's a bit "untidy".

Ron.
 

TomBascom

Curmudgeon
You could have an app server handle the tallying. On the one hand that puts the increment process outside of the update transaction which avoids your locking issue. On the other hand you are outside the transaction so if a rollback occurs your stats will lose accuracy.

Or you could increment sequences.
 
Top