Hi,
I have 5 sub processes which are updating and commiting different records of a single table.I have a problem here.I want to rollback all the transaction if any one of processes fails.Can any one of you gurus suggest how to go about it ?
Thanks
Tushar Johri
I dont think you can do this in any straight forward manner since each process is hitting the database in its own session. When the rollback is issued, it will only impact that session. If you need to do this, I would create a TEMP table that is a mirror of the working table. Process the records for each script in that TEMP table, then when the work is done, update the working table.
...or you could use something like a semaphore. Keep a status flag in an OS table for all of your processes to check. When one updates this to say that a failure occurred, start rolling back.
If you are periodically committing records, then use some kind of common batch update identifier in your table. This will help you delete all records common to the batch. If you can't add columns to the table that you are loading, create a new table that keeps key values of records that you are working with.
Thomas
Hi,
Let me thank google and tmarikle for replying to my query.I will try to go ahead with semaphore technique and let us see if it works out
Thanks
Tushar