Re: Locking outside COMMIT WORK

In <3250B368.717A@netcomuk.co.uk> Ian Goddard <igoddard@netcomuk.co.uk> writes: >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. Unfortunately, I dont think this is the case. A 'Hold' cursor, in my understanding, simply keeps the cursor open beyond the transaction. The commit work still forces ALL locks to be dropped regardless of the type of isolation or cursor. Therefore my problem is not one of holding the rows open, but keeping them unbder wraps until I am finished with the processing. The reason I use a select into temp (with repeatable read), rather than a hold cursor, is that I think a cursor will only read (and lock) the rows as they are accessed, but a select into temp reads the whole set NOW, and with repeatable read, this locks up all the rows I am interested in. If wiser heads can refute or clarify this time of locking point, please do so. >> 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.