Nag, November 16, 2001 - 12:20 pm UTC
select * from T where .... FOR UPDATE NOWAIT
Tom, if we do the above, the problem is that if the required rows are already locked by someone else, then my process will fail. May be that is the reason why nowait option is very scarcely used.
1.When the above said error happens , is it possible to find out which process or application or user has blocked those rows, and throw an exception giving the information to the user.
2.Do you recommend the use of nowait option.
November 16, 2001 - 1:55 pm UTC
Scarcely used? Hardly. Every single forms application on the planet uses it thousands, millions of times. It is heavily used.
It is not used by applications that have "lost update" issues (bugs) perhaps but in many properly written programs, it is used heavily.
What would you have your process do? If the rows are locked by another session -- you can either wait, OR you can not wait.
It is totally your choice -- do you want to wait? do you want to not wait -- you decide. In the END you better make sure you don't overwrite someone elses changes (the major use of select for update is to lock the row before you let the client change it to prevent others from doing the same).
No, it is not really possible to find the session, you can find all candidate sessions.
Yes, I recommend using nowait when its appropriate.
Third choice --- Can wait
A reader, November 16, 2001 - 2:38 pm UTC
Hi,
In our application, we can do for example a "loop", say 1000
times to "select .... from .... for update no wait", and handle the exception(just do nothing) within the loop, if the lock is finished with 1000 loops, then it will succeed and break the loop to continue next step in the process; Otherwise, it will fail after 1000 times and then stop the whole process. Of course, we need to measure the time to execute the whole process in order to determine the loop times. All this stuffs are for the purpose of increasing "automatical concurrency".
Tom, please comment on that.
Thanks,
November 16, 2001 - 3:19 pm UTC
why do it 1000? why not do it twice, sleep a second in between?
Not understanding your exact process and needs, I cannot really comment on how best to attack your problem. I personally would not loop 1,000 times for anything.
9i facility
Connor, November 17, 2001 - 7:53 am UTC
I believe 9i has a timeout setting now.
select * from MY_TABLE for update WAIT 5
November 17, 2001 - 10:14 am UTC
indeed:
ops$tkyte@ORA9I.WORLD> select * from emp for update wait 5;
select * from emp for update wait 5
*
ERROR at line 1:
ORA-30006: resource busy; acquire with WAIT timeout expired
thanks for pointing that out Connor...
9i good but.....
A reader, November 17, 2001 - 4:05 pm UTC
Not all of the people need/have to update to 9i right now, loop or sleep still be a good choice to work around for the version below 9i.
What if want to continue after exception
ns, August 30, 2007 - 8:45 am UTC
I have a requirement where I'm updating one date column for a given set of rows . But if somebody else has locked any of these rows, I want to leave those rows unupdated and update all others at that point.
My night batch process will find out these un-updated rows and update in the night.
How can I do that?
September 04, 2007 - 5:07 pm UTC
don't update anything until the batch job would be my only answer.
garbage in, garbage out.
If you can skip updating any row
then
You can skip updating all rows
this is a lot like using "when others then null", a bad idea, a bug.
Please don't do this, it hurts me to see....
What if want to continue after exception
ns, August 30, 2007 - 8:46 am UTC
Forgot to write I'm using Oracle 9.2.0.8.0 .
Thanks