Below provided function executed at the same time from 2 different sessions. And both Sessions has got the same PPID from the function though For Update clause was mentioned in the select and updated the status subsequently. Table was created with parallel option enabled. We have the same process in a different environment but table was created with No parallel. we have not faced any issue in this environment. Can you please explain why it is behaving differently for Parallel and No parallel.
Function get_NextProcess_id(p_state_cd number, p_plan varchar2,p_iteration number)
return number as
ppid number;
begin
select parallel_process_id into ppid from process_tbl
where rowid =
(select rowid from
(select rowid from process_tbl
where state_cd = p_state_cd and plan = p_plan and status = 'PEND' order by parallel_process_id)
where rownum=1)
for update;
update process_tbl
set status='PROG'
where risk_state_cd = p_state_cd and plan = p_plan
and iteration_nbr = p_iteration
and parallel_process_id = ppid;
dbms_output.put_line('PPID:'||ppid) ;
commit;
return ppid;
exception
when others then
Rollback;
dbms_output.put_line('Error :'||SQLERRM) ;
return SQLCODE;
end;
end;
Parallel/noparallel is a red herring. Both environments can return the same PPID.
This is due to Oracle's read consistency model. Imagine you have one session calling the function. Another session calls the function before the first commits with the same inputs. The second session can't see the update yet. So when session 2 issues the select for update, it attempts to lock the same row as session 1!
The following shows this. Create a table with repeated c1 values and a flag to say whether they are processed or not:
create table t (
x integer primary key,
c1 integer not null,
flag integer not null
);
insert into t
select rownum, mod(rownum, 10), 0 from dual
connect by level <= 100;
commit;
Now in session 1, run the following:
CHRIS>declare
2 l integer;
3 begin
4 select x into l from t
5 where rowid = (select rowid from (
6 select rowid from t
7 where c1 = 0 and flag = 0 order by x
8 )
9 where rownum = 1
10 )
11 for update;
12
13 dbms_output.put_line(l);
14
15 update t
16 set flag = 1
17 where x = l;
18 end;
19 /
10
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.22
CHRIS>
This returns 10. Notice the information is not committed yet. Run the code again in a second session. It will be blocked waiting for session 1. Once session 1 commits, the output of session two will be the same!
CHRIS>declare
2 l integer;
3 begin
4 select x into l from t
5 where rowid = (select rowid from (
6 select rowid from t
7 where c1 = 0 and flag = 0 order by x
8 )
9 where rownum = 1
10 )
11 for update;
12
13 dbms_output.put_line(l);
14
15 update t
16 set flag = 1
17 where x = l;
18 end;
19 /
10
PL/SQL procedure successfully completed.
Elapsed: 00:01:10.00 <-- elapsed 1min+ waiting for the other session
CHRIS>
This is because the second session can't see the update from the first when it starts. Therefore both select the same row.
It's likely you see this more in the parallel environment because this adds overhead. This can make the function take longer to execute. Therefore you're more likely to have overlapping sessions.
Based on what you've supplied, I'm assuming there's multiple rows that match your criteria. You want to update the first of these that are pending. To do this correctly you need to change your code.
The select for update at the start needs to lock
all the rows that match your inputs. The update then modifies the first pending row:
Session 1:
CHRIS>declare
2 cursor c (val integer) is
3 select * from t
4 where c1 = val
5 for update;
6
7 pc1 integer := 0;
8 l integer;
9 begin
10
11 open c (pc1);
12
13 update t
14 set flag = 1
15 where rowid = (select rowid from (
16 select rowid from t
17 where c1 = pc1 and flag = 0 order by x
18 )
19 where rownum = 1
20 )
21 returning x into l;
22
23 close c;
24
25 dbms_output.put_line(l);
26 end;
27 /
10
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.85
Executes quickly and returns 10.
Start session 2 before committing session 1. It will be blocked. You can then commit session 1, releasing session 2 to update the next row
CHRIS>declare
2 cursor c (val integer) is
3 select * from t
4 where c1 = val
5 for update;
6
7 pc1 integer := 0;
8 l integer;
9 begin
10
11 open c (pc1);
12
13 update t
14 set flag = 1
15 where rowid = (select rowid from (
16 select rowid from t
17 where c1 = pc1 and flag = 0 order by x
18 )
19 where rownum = 1
20 )
21 returning x into l;
22
23 close c;
24
25 dbms_output.put_line(l);
26 end;
27 /
20
PL/SQL procedure successfully completed.
Elapsed: 00:00:47.35
CHRIS>
Note the time taken and the different value returned.
Note: this can impact the scalability of your solution (the select for update can block more other sessions). You may want to investigate alternative solutions (e.g. queueing).