Skip to Main Content
  • Questions
  • Parallel execution of Select .. for update with Insert

Breadcrumb

Question and Answer

Chris Saxon

Thanks for the question, Ash.

Asked: December 18, 2018 - 4:55 pm UTC

Last updated: December 19, 2018 - 3:36 pm UTC

Version: 11.2

Viewed 1000+ times

You Asked

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

and Chris said...

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

Rating

  (3 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Can't put unique constraint

Ash Kr, December 18, 2018 - 5:44 pm UTC

Hi Chris

Many thanks for your prompt reply. I did think of using Unique constraint before posting the question here. However unfortunately, the Id column can't be unique as this table has historical records of a particular id. There is a column name called deleted_date which is populated with sysdate for keeping historical records. At any point in time only one row for an id will have deleted_date as NULL so that it is treated as current active row. However the updates are fine.

Our problem is with INSERTS.

Many thanks,

Ashu
Chris Saxon
December 18, 2018 - 5:56 pm UTC

So how about unique ( id, deleted_date )?

not sure

Ash Kr, December 18, 2018 - 6:16 pm UTC

Thanks Chris!

That will definitely fix the inserts.

But just wondering if it will have negative impact in terms of performance on the updates when run in parallel for same ids? Are we going to encounter too many unique constraint violations for Updates?

Functionally it will work fine I guess though.

Many thanks Chris!
Connor McDonald
December 19, 2018 - 2:34 am UTC

In that instance, how about just enforcing uniqueness for the null entries ?

create unique index IX on MY_TABLE ( case when deleted is null then ID end )


good idea

Ash Kr, December 19, 2018 - 10:36 am UTC

Thanks Connor!

That is definitely a good idea. Why did this not cross MY mind? :-)

Thanks again!

Ash
Chris Saxon
December 19, 2018 - 3:36 pm UTC

Glad we could help.

More to Explore

PL/SQL demos

Check out more PL/SQL tutorials on our LiveSQL tool.

PL/SQL docs

PL/SQL reference manual from the Oracle documentation library