create table TEST23(  uq INTEGER ); -- это таблица с триггером, реализующим constraint
create table TEST_U(  id  INTEGER not null,  val INTEGER default 0 not null ); -- вспомогательная

-- вспомогательная функция, которая вставляет во вспомогательную таблицу ID и коммитит ее, 
-- чтобы показать всем сессиям. Функция должна быть атомарная (атомическая?), DBMS лок не показан
create or replace procedure CommitForAll(uq in integer) is
  PRAGMA AUTONOMOUS_TRANSACTION;
  n integer;
begin
  select id into n from test_u where id = uq;
exception
  when NO_DATA_FOUND then 
    insert into test_u values (uq,0);
    commit write nowait batch;
end CommitForAll;

-- собственно триггер. эмуляция поведения при вставлении дупликата реализована через update.
-- поскольку update после освобождения строчного лока не вылетает, а срабатывает,
-- приходится повторно проверять главную таблицу на предмет закоммиченных UQ
create or replace trigger tr_test23
  before insert on test23  
  for each row
begin
  if :new.uq is null then raise VALUE_ERROR; end if; -- better ORA-01400: cannot insert NULL

  -- remember the posted id for other sessions
  CommitForAll( :new.uq );

  -- first, check among session-visible data (commited +posted in this session)
  for t in (select * from test23 where uq = :new.uq)
  loop
    raise DUP_VAL_ON_INDEX;
  end loop;

  -- try causing row-level lock in helper table of uncommitted inserts
  update test_u u
     set val = u.val+1
   where u.id = :new.uq;

  -- if updated, lock worked; see if uq now among committed
  if SQL%ROWCOUNT > 0 then
    for r in (select * from test23 where uq = :new.uq)
    loop
      raise DUP_VAL_ON_INDEX;
    end loop;
  end if;
end tr_test23;

SQL> create table test24 ( id integer );
Table created

SQL> create trigger tr_test24
  2  before insert on test24
  3  for each row
  4  declare
  5    n integer;
  6  begin
  7    select count(*) into n from test24;
  8  end;
  9  /
Trigger created

SQL> insert into test24 values(0);
1 row inserted

SQL> commit;
Commit complete

SQL> insert into test24 select 1 from dual;
insert into test24 select 1 from dual
ORA-04091: table TEST.TEST24 is mutating, trigger/function may not see it
ORA-06512: at "TEST.TR_TEST24", line 4
ORA-04088: error during execution of trigger 'TEST.TR_TEST24'

---------------------------- session #1 ------------------------------
#1> insert into test23 select 1 from dual;
1 row inserted

#1> select * from test23;
                                     UQ
---------------------------------------
                                      1

---------------------------- session #2 ------------------------------
#2> insert into test23 select 2 from dual;

1 row created.

#2> select * from test23;

        UQ
----------
         2

#2> insert into test23 select 2 from dual;
insert into test23 select 2 from dual
            *
ERROR at line 1:
ORA-00001: unique constraint (.) violated
ORA-06512: at "TEST.TR_TEST23", line 43
ORA-04088: error during execution of trigger 'TEST.TR_TEST23'

#2> insert into test23 select 1 from dual;
--(sqlplus подвис)
---------------------------- session #1 ------------------------------
#1> rollback;
Rollback complete

#1> select * from test23;
                                     UQ
---------------------------------------

---------------------------- session #2 ------------------------------
#2 (sql+ проснулся, был блокирован на 'insert into test23 select 1 from dual;')

1 row created.

#2> select * from test23;

        UQ
----------
         2
         1
---------------------------- session #1 ------------------------------
#1> insert into test23 values(2);
(сессия 1 подвисла)
---------------------------- session #2 ------------------------------
#2> commit;

Commit complete.
---------------------------- session #1 ------------------------------
#1 (проснулась, висела на 'insert into test23 values(2);')
insert into test23 values(2)
ORA-00001: unique constraint (.) violated
ORA-06512: at "TEST.TR_TEST23", line 52
ORA-04088: error during execution of trigger 'TEST.TR_TEST23'

  -- get ready to use the row lock for this value
  PrepLock; -- :new.uq is implicit

  -- first, check among posted values in this session
  if FindPosted > 0 then raise DUP_VAL_ON_INDEX; end if;

  -- second, check among commited values
  if FindCommitted > 0 then raise DUP_VAL_ON_INDEX; end if;

  -- dequeue multiple sessions one by one
  while WaitOnLock != 0
  loop
    null;
  end loop;

  -- check again among commited values
  if FindCommitted > 0 then raise DUP_VAL_ON_INDEX; end if;

  -- count this id as posted within this session
  insert into test_t values ( :new.uq );

 function WaitOnLock return integer is
    n integer;
  begin
    select 1 into n from test_u where id = :new.uq for update nowait;
    return 0; -- first lock, no wait
  exception
    when others then -- catching ORA-00054 resource busy and NOWAIT specified
      select 0 into n from test_u where id = :new.uq for update;
      return 1;
  end WaitOnLock;