How to insert data in table with constraint on column? – in parallel queries

insertoracleunique-constraint

I have an Oracle database with a table with a unique field in it:

create table t(n number, CONSTRAINT n_unq UNIQUE (n));

Then from a session-1 someone is trying to insert some data:

insert into t(n) values(1);
-- no commit yet

and in session-2 someone is trying to do the same:

insert into t(n) values(1);
-- hangs here, wating for commit/rollback in session-1

What is the correct way to overcome such pity situations?
Session-2 seemes trapped, it may hangs for a vary long time 'being caught' by session-1.

I know the way with sequences, but what if the task is more complicated, say: to write a function which checks if a value exists and return it, and if not exists then INSERT (and here is the trap just inroduced, when running the same code in parallel) and then return it.

-- Ex:
create or replace function f(pn number) return number
is
  pret number;
begin
  select n into pret from t where n = pn;
  return pret;
exception when no_data_found then
  insert into t(n) values (pn);
  -- hangs here in parallel calls f(<new_value>)
  return pn;
end;

Best Answer

Commit as soon as possible. Otherwise, just to give you an idea:

create table t(n number, CONSTRAINT n_unq UNIQUE (n));

create or replace function f(pn number) return number
is
  pret number;
  l_ret number;
begin
  select n into pret from t where n = pn;
  return pret;
exception when no_data_found then
  l_ret := dbms_lock.request(id => pn, timeout => 0, release_on_commit => true);
  dbms_output.put_line('lock return code: ' || l_ret);
  if l_ret = 0 then 
    insert into t(n) values (pn);
    return pn;
  else
    return -1; -- can not insert
  end if;
end;
/

Session 1:

set serveroutput on
declare
  l_ret number;
begin
  l_ret := f(1);
  dbms_output.put_line('f return code: ' || l_ret);
end;
/

lock return code: 0
f return code: 1

select * from t;

         N
----------
         1

Session 2:

set serveroutput on
declare
  l_ret number;
begin
  l_ret := f(1);
  dbms_output.put_line('f return code: ' || l_ret);
end;
/

lock return code: 1
f return code: -1

select * from t;

no rows selected

Session 1:

commit;

Session 2:

select * from t;

         N
----------
         1

set serveroutput on
declare
  l_ret number;
begin
  l_ret := f(1);
  dbms_output.put_line('f return code: ' || l_ret);
end;
/

f return code: 1

I am not sure this will really help you, the ideal solution would be to prevent multiple sessions trying to insert the same value in long transactions.

Related Question