Re: Locking outside COMMIT WORK

Bryan Tonnet wrote: > > In <3250B368.717A@netcomuk.co.uk> Ian Goddard <igoddard@netcomuk.co.uk> writes: > > 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. You are correct though you could use a scroll cursor and after opening it do a fetch last. This has exactly the same effect as building an explicit temp table. It actually builds an internal temp table containing all the rows that the cursor requires. Your method is probably more appropriate as you have control over where the temp table is created and it's extent sizing. > >> 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. There are a couple of other options that I can think of. They aren't specifically database related more application. 4) Delete the rows that you copied into the temp table. That way nobody can change them while you are processing the temp table. You would have to know that nobody could insert new rows with the same key and that your temp table would survive a system failure. This isn't normally a viable option :-)) 5) A less extreme method is to revert back to the old application level locking method using a lock flag on the table. The nice thing is that with a database that supports triggers you don't have to change all your application programs to check the lock flag. Create update and delete triggers that will generate an exception if anyone tries to update or delete a row that has the lock flag set unless it is an update that does nothing but unlock the row. In your process run through and set the locked flag on the rows you would have moved to the temp table as a single transaction. Now you have a set of rows that are safe from being updated or deleted while you get on and run your process. At the end reset the lock flags. I believe that with the Universal Server you will be able to extend this to stopping other users from inserting a row that meets the criteria of the rows you have locked. Cheers - Jim -- ------------------------------------------------------------------------ Jim Gordon DHL Airways Inc. jgordon@us.dhl.com ------------------------------------------------------------------------ My opinions are my own. They may vary with time but they remain mine!