tst> create or replace function get_unique(name varchar2) return varchar2 as
   2       pragma autonomous_transaction;
   3       handle varchar2( 128 );
   4   begin dbms_lock.allocate_unique(name, handle);
   5       commit;
   6       return handle;
   7   end;
   8   /

Function created.

tst> create or replace package p1 as
   2   type lock_rec is record (name varchar2( 128 ), handle varchar2( 128 ));
   3   type lock_rec_tab is table of lock_rec index by binary_integer;
   4   procedure set_lock(name varchar2);
   5   procedure clear_locks;
   6   procedure submit_locks;
   7   end;
   8   /

Package created.

tst> create or replace package body p1 as
   2   locks lock_rec_tab;
   3   procedure set_lock(name varchar2) is
   4       i number :=  1 ;
   5       ret number;
   6       rec lock_rec;
   7   begin
   8       if locks.first is not null then
   9           for i in locks.first..locks.last loop
  10               if locks(i).name=name then
  11                   return;
  12               end if;
  13           end loop;
  14       end if;
  15       rec.name := name;
  16       rec.handle := get_unique(name);
  17       ret := dbms_lock.request(rec.handle, release_on_commit=>true);
  18       if ret= 4  then
  19           return; /* already locked by me */
  20       elsif ret !=  0  then
  21           raise_application_error(- 20001 , 'Can''t lock for '||name);
  22       end if;
  23       locks(i) := rec;
  24   end;
  25   /* ----- */
  26   procedure clear_locks is
  27       i number :=  1 ;
  28       ret number;
  29   begin
  30       if locks.first is not null then
  31           for i in locks.first..locks.last loop
  32               ret := dbms_lock.release(locks(i).handle);
  33               if ret !=  0  then
  34                   raise_application_error(- 20001 , 'Can''t release lock for '||locks(i).name);
  35               end if;
  36               locks.delete(i);
  37           end loop;
  38       end if;
  39   end;
  40   /* ----- */
  41   procedure submit_locks is
  42   begin
  43       locks.delete;
  44   end;
  45   end;
  46   /

Package body created.

tst> drop table t1;

Table dropped.

tst> create table t1(name varchar2( 30 ));

Table created.

tst> create or replace trigger t1_bef
   2   before update or insert or delete on t1
   3   for each row
   4   begin
   5       if inserting then
   6           p1.set_lock(:new.name);
   7       elsif deleting then
   8           p1.set_lock(:old.name);
   9       else
  10           p1.set_lock(:old.name);
  11           p1.set_lock(:new.name);
  12       end if;
  13   end;
  14   /

Trigger created.

tst> create or replace trigger t1_aft
   2   after update or insert or delete on t1
   3   begin
   4       for r in (select name, count(*) cnt from t1
   5                 group by name
   6                 HAVING count(*) >  2  /* Our limit */ ) loop
   7           p1.clear_locks;
   8           raise_application_error(- 20003 , 'Too many count for (as example) '||r.name);
   9       end loop;
  10       p1.submit_locks;
  11   end;
  12   /

Trigger created.

tst> insert into t1 values('One');

 1  row created.

tst> /

 1  row created.

tst> /
insert into t1 values('One')
*
ERROR at line  1 :
ORA- 20003 : Too many count for (as example) One
ORA- 06512 : at "U1.T1_AFT", line  6 
ORA- 04088 : error during execution of trigger 'U1.T1_AFT'

ts2> insert into t1 values('One');

insert into t1 values('One')
*
ERROR at line  1 :
ORA- 20003 : Too many count for (as example) One
ORA- 06512 : at "U1.T1_AFT", line  6 
ORA- 04088 : error during execution of trigger 'U1.T1_AFT'


ts2> 

...
  20       elsif ret !=  0  then
  21           raise_application_error(- 20001 , 'Can''t lock for '||name);
  22       end if;
  23       locks(i+ 1 ) := rec;
  24   end;
...

tst> create or replace trigger t1_aft
   2   after update or insert or delete on t1
   3   begin
   4       for r in (select name, count(*) cnt from t1
   5                 group by name
   6                 HAVING count(*) >  2  /* Our limit */ ) loop
   7           p1.clear_locks;
   8           raise_application_error(- 20003 , 'Too many count for (as example) '||r.name);
   9       end loop;
 мы вставили две фамилии и не блокировали
 во второй тоже вставили две
 теперь не важно кто первый заблокирует
 проверки уже выполнены если будут комитеть
 получим четыре фамилии
  10       p1.submit_locks;
  11   end;
  12   /

define TAB_NAME=T1
define FLD_NAME=NAME
define MAX_COUNT= 2 
define LK_TIMEOUT= 2 

set verify off
column user_and_table new_value LK_PREFIX noprint
select '('||user||'.&&TAB_NAME.) ' user_and_table from dual
/
create table &&TAB_NAME(&&FLD_NAME varchar2( 30 ))
/
create or replace package &&TAB_NAME._lock_mgr as
	type lock_rec_tab is table of &&TAB_NAME..&&FLD_NAME%type index by binary_integer;
	locks lock_rec_tab;
	procedure set_lock(str varchar2);
end;
/
create or replace package body &&TAB_NAME._lock_mgr as
/* ----- */
function get_unq(str varchar2) return varchar2 is
	pragma autonomous_transaction;
	handle varchar2( 128 );
begin
	dbms_lock.allocate_unique('&&LK_PREFIX'||str, handle);
	commit;
	return handle;
end;
/* ----- */
procedure set_lock(str varchar2) is
	i number :=  0 ;
	ret number;
begin
	if locks.first is not null then
		for j in locks.first..locks.last loop
			if locks(j)=str then
				return;
			end if;
		end loop;
		i := locks.last;
	end if;
	ret := dbms_lock.request(get_unq(str), timeout=>&&LK_TIMEOUT,
			release_on_commit=>true);
	if ret= 0  or ret= 4  /* already locked by me */ then
		locks(i+ 1 ) := str;
	elsif ret= 1  /* locked by another session */ then
		for r in (select username, s.sid from v$session s, v$lock l, sys.dbms_lock_allocated la
				where l.type='UL' and l.lmode> 0  and l.id1=la.lockid
				and s.sid=l.sid and la.name='&&LK_PREFIX'||str) loop
			raise_application_error(- 20002 ,	'"'||str||'" locked by '||
					r.username||' (SID='||r.sid||')');
		end loop;
	else
		raise_application_error(- 20001 ,	'Can''t lock for "'||str||'"');
	end if;
end;
end;
/
create or replace trigger &&TAB_NAME._lock_before
before insert or delete or update of &&FLD_NAME on &&TAB_NAME
for each row
begin
	if inserting or updating then
		&&TAB_NAME._lock_mgr.set_lock(:new.&&FLD_NAME);
	end if;
	if updating or deleting then
		&&TAB_NAME._lock_mgr.set_lock(:old.&&FLD_NAME);
	end if;
end;
/
create or replace trigger &&TAB_NAME._lock_after
after insert or delete or update of &&FLD_NAME on &&TAB_NAME
declare
	nm &&TAB_NAME..&&FLD_NAME%type;
	cnt number;
begin
	if &&TAB_NAME._lock_mgr.locks.first is not null then
		for i in &&TAB_NAME._lock_mgr.locks.first..&&TAB_NAME._lock_mgr.locks.last loop
			nm := &&TAB_NAME._lock_mgr.locks(i);
			select count(*) into cnt from &&TAB_NAME where &&FLD_NAME=nm;
			if cnt > &&MAX_COUNT then
				&&TAB_NAME._lock_mgr.locks.delete;
				raise_application_error(- 20003 , 'Too many count for "'||nm||'"');
			end if;
		end loop;
	end if;
	&&TAB_NAME._lock_mgr.locks.delete;
end;
/
set verify on
undef LK_PREFIX
undef LK_TIMEOUT
undef MAX_COUNT
undef FLD_NAME
undef TAB_NAME

tst> insert into tab1 values( 1 , 'One',  1 );
One  1  I

 1  row created.

tst> insert into tab1 values( 2 , 'One',  1 );
One  1  I

 1  row created.

tst> insert into tab1 values( 3 , 'One',  1 );
One  1  I

 1  row created.

tst> update tab1 set name='Two' where id= 1 ;
Two  1  I
One  1  D

 1  row updated.

ts2> insert into tab1 values( 4 , 'One',  1 );

insert into tab1 values( 4 , 'One',  1 )
            *
ERROR at line  1 :
ORA- 00001 : unique constraint (U1.PK_TAB2) violated
ORA- 06512 : at "U1.TR_TAB1_NAME3_ST", line  5 
ORA- 04088 : error during execution of trigger 'U1.TR_TAB1_NAME3_ST'


ts2> select * from tab1;

        ID NAME                      OTDEL
---------- -------------------- ----------
          1  Two                            1 
          2  One                            1 
          3  One                            1