Re: Locking outside COMMIT WORK

You describe a problem that may sometimes be real and hard to solve. Sometimes it can be solved by a redesign of the database. If not you may be able to run another program, lock the rows that shouldn't change, return them to your main program (via another table or some other means) and then go on with your loop. When the loop is finished you have to tell the other program that it is. You may use another table again for this, or some other inter program communication. Hope it helps although it isn't as elegant as it should be. batonnet@zeta.org.au (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 Nils.Myklebust@idg.no NM Data AS, P.O.Box 9090 Gronland, N-0133 Oslo, Norway My opinions are those of my company The Informix FAQ is at http://www.iiug.org