Re: Lock timeout on oracle 8i

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
>

Well, Oracle handles locks different (in a better way - we Oracle dba's think). You only get a locking problem if somebody else has updated the row where empno=7900 and hasn't commited and you want to update the row too, or the row/table is selected for update (as you do). If you dont apply "nowait", you will wait until the lock is released, ie. the locking session has commited or rolled back. Anybody can read the table/row even if it is locked and they "see" the row as it was when the query started and they will only see your changes if the query starts after you commit. This is called consistent read. All selected data are consistent to the query start time. Tom Kyte's book has a good chapter on this and the different handling by Oracle, SQL-Server.... Read it. Well the hole book (about 2/3 read by now) is *GREAT*. Well done Tom. And then there is the Oracle Consepts and locking pages in the manuals.

A common way of (no locking) handling the serial nature in manual entry systems, is to have a last_change date column on the table. From this timestamp the application can decide to commit changes, reload data, commit changed columns only .... but that is another *long* story.

rgds

/Svend Jensen Received on Fri May 24 2002 - 13:41:13 CDT