Locking outside COMMIT WORK

One of the real frustrations I have had in the past is with situations where it would be beneficial to maintain locks outside of a transaaction. A hypothetical might be; set isolation to repeatable readselect 'sql statement' into temp temp_table This reads a few rows that are used in a main loop and these records *must not change* during processing of the loop. This is followed by set isolation to <what it was before>foreach some_other_previously_declared_cursor begin work do some processing (which involves referencing our temp_table rows) update/insert/delete records if error then rollback work else commit work end if end foreach Obviously, each time the foreach loop commits the changes, the locks placed by the 'repeatable read' select statement are released (and the records may therefore be modified before the processing is completed). For this type of situation I can think of only 3 alternatives; 1) Begin Work before the start of the temp_table select and Commit Work only after the entire process is complete....but my main loop will process hundreds of thousands of records each with several sub-updates on other tables so the program will bomb on excessive locking. 2) Same as 1) but lock the entire table(s) at the start of the transaction....but these are busy (in use) tables and my process may take some time to complete. This will be unacceptable in a production environment. 3) Re select the reference records after each Commit Work and hope like hell that someone doesn't sneak in and shuffle things around whilst you're picking up your locks again. Is there a non-bodgy way around this, or is it one of those things we just have to learn to live with. Up till now I've done the latter, but maybe someone here can bump me up to a new awareness level [or at least give me some tips on motorcycle maintenance]. Thanks in advance Bryan Tonnet batonnet@zeta.org.au