Hi Tom
We have a stored proc something like below:
Proc P1 (p_id in varchar2)
is
cursor c1
is
select a,b,c
from t1, t2
where t1.pk = t2.fk
and t1.id = p_id ;
v_id varchar2(10);
Begin
Open c1 (p_id);
fetch c1 into v_id;
close c1;
If nvl(v_id,0) = 0 then
insert into t1 (PK, id,...) values(sequence, v_id, .......);
else
update t1 set some_other_col = some_val
where t1.id = v_id;
end if;
End;
This procedure is called by some Java API. Usually this works fine but when there are 2 Java sessions running in parallel, it inserts duplicates if the v_id doesn't exist in the table. They are not actually duplicates because the Primary key is based on a sequence but if we think in terms of id, it is a duplicate.
To resolve this, if we use FOR UPDATE clause to the cursor, will it help in case of INSERTING new Ids as well?
It will be really great to hear from you. Many many thanks in advance for your help.
Best regards,
Ashu
If you must only have one row for each value of ID, there's one way to fix this:
Place a unique constraint on it!
Doing a select for update won't help. If you're adding a new row, the query will return nothing. So have nothing to lock to stop the other session.
A few other observations:
I don't understand why you're joining to t2 in the cursor. You're only inserting/updating t1. So this risks adding more duplicates when there's no child t2 row.
You can replace the if insert else update with a merge instead.
So I'd do something like:
create sequence s;
create table t (
pk int primary key,
id int unique,
c1 int
);
create or replace procedure p (
p_id int , p_v1 int
) as
begin
merge into t
using ( select p_id id, p_v1 v1 from dual ) s
on ( t.id = s.id )
when not matched then
insert ( pk, id, c1 )
values ( s.nextval, s.id, s.v1 )
when matched then
update set c1 = s.v1;
end p;
/
If you call p in two concurrent sessions, you'll see:
Session 1:
SQL> exec p ( p_id => 1, p_v1 => 1 );
PL/SQL procedure successfully completed.
Session 2 will then be blocked:
SQL> exec p ( p_id => 1, p_v1 => 2 );
Until session 1 commits/rollsback:
SQL> commit;
Commit complete.
Back in session 2:
SQL> exec p ( p_id => 1, p_v1 => 2 );
BEGIN p ( p_id => 1, p_v1 => 2 ); END;
*
ERROR at line 1:
ORA-00001: unique constraint (CHRIS.SYS_C0043990) violated
SQL> exec p ( p_id => 1, p_v1 => 2 );
PL/SQL procedure successfully completed.
SQL> select * from t;
PK ID C1
---------- ---------- ----------
1 1 2