RE: Locking question when using Select clause with For Update and Skip locked
- From: "Powell, Mark D" <mark.powell@xxxxxxx>
- To: <oracle-l@xxxxxxxxxxxxx>
- Date: Thu, 8 Mar 2007 09:08:30 -0500
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@xxxxxxxxxxxxx
[mailto:oracle-l-bounce@xxxxxxxxxxxxx] On Behalf Of Alexander Fatkulin
Sent: Wednesday, March 07, 2007 10:50 PM
To: Harvinder.Singh@xxxxxxxxxxxxx
Cc: oracle-l@xxxxxxxxxxxxx
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
>
>
>
> --
> //www.freelists.org/webpage/oracle-l
>
--
Alexander Fatkulin
--
//www.freelists.org/webpage/oracle-l
--
//www.freelists.org/webpage/oracle-l
Other related posts:
