Re: Locking question when using Select clause with For Update and Skip locked
- From: "Alexander Fatkulin" <afatkulin@xxxxxxxxx>
- To: Harvinder.Singh@xxxxxxxxxxxxx
- Date: Thu, 8 Mar 2007 13:49:35 +1000
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
Other related posts:
