Re: select ...for update wait

On 15 Dec 2004 09:04:05 -0800, "db2group88_at_yahoo.com" <db2group88_at_yahoo.com> wrote:

>You can do select .... for update wait, Specify WAIT to instruct the
>database to wait integer seconds for the row to become available and
>then return control to you. i am using oracle 10g with patch set 1 ,
>but looks like this doesn't work, one session has the row lock, the
>other session wait forever. For our application, we need to be able to
>wait for 60 sec to get the error message return if the row locked still
>unavailable. thanks for help

[andyh_at_testbox andyh]$ sqlplus test/test

SQL*Plus: Release 10.1.0.3.0 - Production on Wed Dec 15 19:11:09 2004

Copyright (c) 1982, 2004, Oracle. All rights reserved.

Connected to:
Oracle Database 10g Enterprise Edition Release 10.1.0.3.0 - Production With the Partitioning and Data Mining options

SQL> create table t (c number);

Table created.

SQL> insert into t values (1);

1 row created.

SQL> commit;

Commit complete.

SQL> select c from t for update;

         C

         1
SQL>  [ And then in another terminal: ]

[andyh_at_testbox andyh]$ sqlplus test/test

SQL*Plus: Release 10.1.0.3.0 - Production on Wed Dec 15 19:11:03 2004

Copyright (c) 1982, 2004, Oracle. All rights reserved.

Connected to:
Oracle Database 10g Enterprise Edition Release 10.1.0.3.0 - Production With the Partitioning and Data Mining options

SQL> select c from t for update wait 60;

 [ Time passes ... 60 seconds of it ]

select c from t for update wait 60

              *
ERROR at line 1:
ORA-30006: resource busy; acquire with WAIT timeout expired

SQL>  Do you have a counterexample where it doesn't work?

-- 
Andy Hassall / <andy@andyh.co.uk> / <http://www.andyh.co.uk>
<http://www.andyhsoftware.co.uk/space> Space: disk usage analysis tool