SQL & PL/SQL

Currently, we fetch a cursor on a few million rows using BULK COLLECT.
In a FORALL loop, we update the rows.

What is happening now, is that we run this procedure at the same time, and there is another session running a MERGE statement on the same table, and a DEADLOCK is created between them.

I'd like to add to the cursor the FOR UPDATE clause, but from what i've read,
it seems that this will cause ALL the rows in the cursor to become locked.

This is a problem, as the other session is running MERGE statements on the table every few seconds, and I don't want it to fail with ORA-0054 (resource busy).

What I would like to know is if there is a way, that only the rows in the
current bulk will be locked, and all the other rows will be free for updates.

To reproduce this problem:

1. Create test table:

create table TEST_TAB
(
ID1 VARCHAR2(20),
ID2 VARCHAR2(30),
LAST_MODIFIED DATE
);

2. Add rows to test table:

insert into TEST_TAB (ID1, ID2, LAST_MODIFIED)
values ('416208000770698', '336015000385349', to_date('15-11-2009 07:14:56', 'dd-mm-yyyy hh24:mi:ss'));
insert into TEST_TAB (ID1, ID2, LAST_MODIFIED)
values ('208104922058401', '336015000385349', to_date('15-11-2009 07:11:15', 'dd-mm-yyyy hh24:mi:ss'));
insert into TEST_TAB (ID1, ID2, LAST_MODIFIED)
values ('208104000385349', '336015000385349', to_date('15-11-2009 07:15:13', 'dd-mm-yyyy hh24:mi:ss'));


3. Create test procedure:

CREATE OR REPLACE PROCEDURE TEST_PROC IS

TYPE id1_typ is table of TEST_TAB.ID1%TYPE;
TYPE id2_typ is table of TEST_TAB.ID2%TYPE;

id1_arr id1_typ;
id2_arr id2_typ;

CURSOR My_Crs IS
SELECT ID1, ID2
FROM TEST_TAB
WHERE ID2 = '336015000385349'
FOR UPDATE;

BEGIN
OPEN My_Crs;

LOOP

FETCH My_Crs bulk collect
INTO id1_arr, id2_arr LIMIT 1;

Forall i in 1 .. id1_arr.COUNT
UPDATE TEST_TAB
SET LAST_MODIFIED = SYSDATE
where ID2 = id2_arr(i)
and ID1 = id1_arr(i);

dbms_lock.sleep(15);
EXIT WHEN My_Crs%NOTFOUND;

END LOOP;
CLOSE My_Crs;
COMMIT;

EXCEPTION
WHEN OTHERS THEN
RAISE_APPLICATION_ERROR(-20000,
'Test Update ' || SQLCODE || ' ' || SQLERRM);
END TEST_PROC;

4. Create another procedure to check if table rows are locked:

create or replace procedure check_record_locked(p_id in TEST_TAB.ID1%type) is

cursor c is
select 'dummy'
from TEST_TAB
WHERE ID2 = '336015000385349'
and ID1 = p_id
for update nowait;

e_resource_busy exception;
pragma exception_init(e_resource_busy, -54);
begin
open c;
close c;
dbms_output.put_line('Record ' || to_char(p_id) || ' is not locked.');
rollback;
exception
when e_resource_busy then
dbms_output.put_line('Record ' || to_char(p_id) || ' is locked.');
end check_record_locked;


5. in one session, run the procedure TEST_PROC.
6. While it's running, in another session, run this block:

begin
check_record_locked('208104922058401');
check_record_locked('416208000770698');
check_record_locked('208104000385349');
end;

7. you will see that all records are identified as locked.
Is there a way that only 1 row will be locked, and the other 2 will be unlocked?

Thanks,
Yoni.