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

From: Powell, Mark D <mark.powell_at_eds.com>
Date: Thu, 8 Mar 2007 09:08:30 -0500
Message-ID: <D1DC33E67722D54A93F05F702C99E2A9984D18@usahm208.amer.corp.eds.com>

There should be no exit statement after the update statement. If the update succeeds you are at the end of the logical processing in the loop and will go around and get the next driving cursor row. If the row was locked you were routed to the exception clause and are again at the end of the loop and you want to again grab the next row.

Mark D Powell -- Phone (313) 592-5148

-----Original Message-----
From: oracle-l-bounce_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Alexander Fatkulin Sent: Wednesday, March 07, 2007 10:50 PM To: Harvinder.Singh_at_metratech.com
Cc: oracle-l_at_freelists.org
Subject: Re: Locking question when using Select clause with For Update and Skip locked

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


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

Received on Thu Mar 08 2007 - 08:08:30 CST