Re: Locking outside COMMIT WORK

Bryan, It seems that a CURSOR WITH HOLD does much of what you want, especially if combined with FOR UPDATE. Without being able to lay hands on a manual I can't remember whether the combination is allowed or not. Ian Bryan Tonnet wrote: > > 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 read> select '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