Re: Lock timeout on oracle 8i

Hi Rudy,

as the other posters wrote your application code would definitely benefit from a re-design according to the way Oracle works.

However, if this is too expensive and you can afford bad performance, use the dbms_lock package. It contains one procedure request that allows to time out a lock request.

Downside is, you have to lock so-called unique identifiers. To create those, ues the hash_value function of the same package. You may feed it with the rowids of the read records. This would allow you to establish your own user-defined locking helping you to emulate the locking done by sqlserver. But bear in mind that you will pay for this comfort by performance!

Regards,

Martin

Rudy Susanto wrote:
>
> Hi all,
>
> I want to convert my t-sql to pl/sql, especially related to lock
> timeout, do i apply with the correct programming on oracle?
>
> ==> on sqlserver:
> declare @ename varchar(10)
>
> set lock_timeout 1500
>
> select @ename = ename
> from emp (rowlock,updlock)
> where empno=7900
> if @@error != 0 goto errhandler
> print 'ename: ' + @ename
> return
>
> errhandler:
> print 'Locking record(s) failed ...'
> return
>
> ==> on oracle:
> declare
> i number(3,0);
> v_ename varchar2(10);
>
> busy exception;
> pragma exception_init(busy,-54);
>
> begin
> i := 1;
> loop
> begin
> select ename into v_ename
> from emp
> where empno=7900 for update nowait;
> dbms_output.put_line('ename: ' || v_ename);
>
> exception
> when busy then
> if i < 3 then
> dbms_lock.sleep(0.5);
> else
> dbms_output.put_line('Locking record(s) failed ...');
> exit;
> end if;
> end;
>
> i := i + 1;
> end loop;
> end;
>
> Thanks in advance,
>
> Rudy Susanto
Received on Thu Jun 13 2002 - 03:06:55 CDT