Transaction within a Transaction

IDS 7.31.TC5 on NT 4.0 SP 5. We have an application that, within the space of a single financial and database transaction, performs a number of lookups and updates (nothing like rocket science there then...). A 'control' row is created right at the start of the transaction, and at certain stages, records a date and time and the stage the transaction is at, until at the end of the entire transaction, all of the details are committed. If, however, during the life of the transaction, it falls over (either programmatically or otherwise) we still want to be able to record the dates and times and the stage it was at at the time of failing, such that these details won't be ROLLED BACK should any problems arise (this is so that we can determine how far the actual transaction got before rolling back). Now, I know that there are many ways (to leave your lover) to program such a tracking system, but in essence, my question is:- How can you commit data to a table during a transactions that isn't committed until the entire transaction is successful ?? We have discussed using STORED PROCS to call SYSTEM commands to run a separate UPDATE script to update the relevant control record but we think the row may be locked as its in the current transaction (being executed from the parent process that called this routine). If any of you know of such a method to update a row within a table that is already being used within a transaction, please let me know. Cheers Sean

Here's one suggestion : 1. Create your initial audit trail row BEFORE commencement of the transaction. 2. Record programmatic failures as part of the ROLLBACK code. e.g IF blah_blah_1 = 'not good' THEN ROLLBACK WORK; CALL update_audit( trx_id, "Failed at blah blah 1"); RETURN; END IF; 3. Record error condition failures by using a tailor-made function that (a) First, rolls back work (b) Then, Updates the initial audit trail record Invoke this by strategically placing WHENEVER ERROR clauses within your code. For example, -- Start of blah blah 1 LET g_trx_id = <whatever>; LET g_trx_posn = 'blah blah 1'; -- parameter may not be passed WHENEVER ERROR CALL update_audit(); ... -- Start of blah blah 2 LET g_trx_posn = 'blah blah 2'; WHENEVER ERROR CALL update_audit(); Unfortunately, global variables will have to be used to pass data to update_audit(). You could merge handling of programmatic & error condition failures into the single function. Caution : WHENEVER ERROR affects all code up to the next WHENEVER ERROR clause or end of file. 4. This still does not handle system crashes. SYSTEM -> Stored Proc should work fine, if you have created the initial audit trail record outside the base transaction. However, the costs of a SYSTEM call are prohibitive. HTH Rudy Tom Jones wrote: > IDS 7.31.TC5 on NT 4.0 SP 5. > > We have an application that, within the space of a single financial and > database transaction, performs a number of lookups and updates (nothing like > rocket science there then...). A 'control' row is created right at the start > of the transaction, and at certain stages, records a date and time and the > stage the transaction is at, until at the end of the entire transaction, all > of the details are committed. > > If, however, during the life of the transaction, it falls over (either > programmatically or otherwise) we still want to be able to record the dates > and times and the stage it was at at the time of failing, such that these > details won't be ROLLED BACK should any problems arise (this is so that we > can determine how far the actual transaction got before rolling back). > > Now, I know that there are many ways (to leave your lover) to program such a > tracking system, but in essence, my question is:- > > How can you commit data to a table during a transactions that isn't > committed until the entire transaction is successful ?? > > We have discussed using STORED PROCS to call SYSTEM commands to run a > separate UPDATE script to update the relevant control record but we think > the row may be locked as its in the current transaction (being executed from > the parent process that called this routine). > > If any of you know of such a method to update a row within a table that is > already being used within a transaction, please let me know. > > Cheers > > Sean

Tom Jones wrote: > IDS 7.31.TC5 on NT 4.0 SP 5. > > We have an application that, within the space of a single financial and > database transaction, performs a number of lookups and updates (nothing like > rocket science there then...). A 'control' row is created right at the start > of the transaction, and at certain stages, records a date and time and the > stage the transaction is at, until at the end of the entire transaction, all > of the details are committed. > > If, however, during the life of the transaction, it falls over (either > programmatically or otherwise) we still want to be able to record the dates > and times and the stage it was at at the time of failing, such that these > details won't be ROLLED BACK should any problems arise (this is so that we > can determine how far the actual transaction got before rolling back). > > Now, I know that there are many ways (to leave your lover) to program such a > tracking system, but in essence, my question is:- > > How can you commit data to a table during a transactions that isn't > committed until the entire transaction is successful ?? > > We have discussed using STORED PROCS to call SYSTEM commands to run a > separate UPDATE script to update the relevant control record but we think > the row may be locked as its in the current transaction (being executed from > the parent process that called this routine). > > If any of you know of such a method to update a row within a table that is > already being used within a transaction, please let me know. If you're working out at the application level, rather than in a stored procedure, then you could consider using two separate database connections, A and B. You'd use A to record the progress and B to do the actual work. The transactions for A and B would be independent. The solution using unlogged temporary tables would work but feels grubby and error prone to me. Recording the rollback info after the ROLLBACK will also work, but there is a finite window where the ROLLBACK is complete but the altered log data is not completed where an interrupt would lose the information about the rollback. That's not to say that using 2 connections is any better on that score; there is a window of vulnerability while you switch connections and deal with the log data. -- Jonathan Leffler (jleffler@informix.com, jleffler@earthlink.net) Guardian of DBD::Informix v0.95 -- see http://www.perl.com/CPAN #include <disclaimer.h>