Re: Configure Oracle ODBC to not wait for locks

From: Mark E. Hansen <meh_at_NOSPAMunify.com>
Date: Mon, 04 Dec 2000 08:01:58 -0800
Message-ID: <3A2BBFF6.DDC8710F_at_NOSPAMunify.com>


Barbara Kennedy wrote:
>
> Ahh. I see. No, the description is much clearer. I don't think I need to
> see any code. There are several ways to take care of this.
> You could choose to not use the for update in the select. (both in the
> scanning process and in the application that is looking at rows.) I know
> you don't want user A to read a row, make a decision and change the row that
> was changed underneath him. (a sensible concern) Instead of the update or
> delete being
> update mytable set ... where rowid=
> do
> update mytable set... where rowid= and col1=original_value and
> col2=original_value...
> If the value has changed underneath you then you will find out that you have
> 0 rows updated. (since you specify rowid, it will find the row effeciently)
> You can then choose to deal with that situation. You can do that also with
> the scanning piece. It won't delete a row that has changed.

Yes, in fact I see now that I don't really even need the "for update" in my scan as I'm not performing an "in-place" delete using the cursor, but [Quoted] rather using a separate "delete <table> where ..." statement.

However, won't the delete statement hang if the row is locked by another process? (by the way, I don't have any control on the other processes, so I can't prevent them from doing any DML operations.)

It is true that I don't need to delete any row that is currently locked by another process (for whatever reason) but I can't allow my process to hang waiting for the lock to be released.

>
> Now that I think about it why not just delete the rows instead of scanning
> them.
> delete from mytable where <date field> < <some date> ;
>
> But there might be some reason why not. (some business requirement)

Yes, in fact they expect this scan/delete to take some time, and want a dialog indicating progress and allowing for a Cancel button. The only thing I could think of was to delete individual rows and update the dialog every so many rows...

I sincerely appreciate your thoughts on this "problem".

>
> Jim
>
Received on Mon Dec 04 2000 - 17:01:58 CET

Original text of this message