Skip to Main Content

Breadcrumb

Question and Answer

Tom Kyte

Thanks for the question, jitender.

Asked: September 28, 2000 - 6:39 am UTC

Last updated: September 04, 2007 - 5:07 pm UTC

Version: 7.3.4

Viewed 10K+ times! This question is

You Asked

Hi Tom,
My question is if a transaction 'A' is waiting to get a lock on a few rows of a table which is presently held by another txn 'B' is there a possibility that the txn 'A' waits for the lock for some "time" and if it is not able to aquire it it comes out of the wait ie I want to know if there is any parameter which we can set so that the txn waits for so many seconds and comes out of the wait state ?

and Tom said...

No, you can either

o wait indefinitely.
o not wait at all.

You already know how to wait indefinitely... To not wait, you would

"select * from T where .... FOR UPDATE NOWAIT"

the rows. You'll get an ORA-54 if they are already locked by someone.

Rating

  (6 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

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.



Tom Kyte
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,


Tom Kyte
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

Tom Kyte
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?
Tom Kyte
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