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

Question

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;

• McNets: A sequence can avoid this issue. If n must be unique, let the server do the job.
• egor7: Sequence is not an option in more general example. Please see an example just added.

Answer 1

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.