Link to home
Create AccountLog in
Avatar of IVL

asked on

Partial & global rollback


We have a procedure inserting/updating/deleting records in 1 table.
The procedure is called from a loop in a global procedure handling data per partition.
There is no Oracle partinioning on the table, the table has a field called Viewdate which identifies a partition.
So, the main procedure loops over a number of view dates and calls the other procedure to treat 1 viewdate at the time.
1 partioin cantains about 3mio records.
After treatment some validations take place.
A validation takes place on Partition level, now we want to add a second validation globally on the table level.

Due to the size, we prefer not to run everything in 1 transaction for performance reasons.
Currently for the partition level validation we used Rollback/Commit to accept or reject the data.
Now adding table level validation on the main procedure we can't rollback anymore the comitted partitions.

Naturally we could use an intermediate staging table to solve this, but we would prefer not to do that for performance reasons.

Is there a way to use a 2 level commit so we rollback faild validations on partition level and at the end when all partitions are treated depending on the table level validation commit or rollback everything ?

I konw there is something like global transactions to allow something similar when targetting multiple databases, but we never used it. Maybe we could use this kind of 2 phase commit using the 2 procedures altough there is only 1 (destination) table in play ?

Avatar of johnsone
Flag of United States of America image

Link to home
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
Avatar of IVL


Thanks a lot !
This works and is exactly what we were looking for.