Re: Locking question when using Select clause with For Update and Skip locked

From: Alexander Fatkulin <afatkulin_at_gmail.com>
Date: Thu, 8 Mar 2007 13:49:35 +1000
Message-ID: <49d668000703071949jad51ddfq4849d9eddf4016a0@mail.gmail.com>

Harvinder,

I wouldn't rely on "for update ... skip locked" since it's undocumented. It's used internally for AQ purposes.

In one approach you can try to lock row with a "for update ... nowait" doing an exception handle:

SQL> declare
  2 l_empno number;
  3 resource_busy exception;
  4 pragma exception_init(resource_busy, -54);   5 begin
  6 for cur in (select * from emp order by empno)   7 loop
  8 begin

  9     select empno into l_empno --try to lock row
 10      from emp
 11      where empno=cur.empno
 12      for update nowait;
 13
 14     exit; --row were locked, exit loop
 15    exception --skip locked row
 16     when resource_busy then dbms_output.put_line('already locked:

'||cur.empno);
17 end;
18 end loop;
19 dbms_output.put_line('first unlocked: '||l_empno); 20 end;

In other approach you can add a locked flag into the table and use an autonomous transaction like this:

SQL> create or replace function get_empno return number is   2 pragma autonomous_transaction;
  3 l_empno number;
  4 begin
  5 update emp set
  6 lck=1
  7 where lck=0 and rownum<2
  8 returning empno into l_empno;
  9 commit;
10 return l_empno;
11 end;
12 /

Function created.

but in this case you should be more careful with a killed/died sessions cleanup. This will require to store not only the lock flag itself but also some session information (like audsid (of you are not using jobs) which can also be used as a lock flag itself) and check with v$session if holder is still alive.

>...is there any workaround to acheive the same functionality in
> Oracle?
>
> Thanks
> --Harvinder
>
>
>
> --
> http://www.freelists.org/webpage/oracle-l
>

-- 
Alexander Fatkulin
--
http://www.freelists.org/webpage/oracle-l

Received on Wed Mar 07 2007 - 21:49:35 CST