运用dbms_lock和autonomous_transaction实现insert contention.

当两个或多用户向同一个表中出入一样的数据时（比如有相同的primary key), 第二个session的insert 会hang住。如果第一个操作不commit或者rollback, 第二个session的insert会一直hang下去。 因此在实际应用中， 这个特性不能提供很好的用户交互。 dbms_lock可以弥补这一缺憾。 

dbms_lock给用户提供了这样一个机制。 它允许用户自己定义一个锁， 当另外一个session去做相同的操作的时候， oracle会把这个操作去试图获得同样的锁。 如果第二个session没用获得到，那说明已经有人在做和自己类似的操作了， 那么自己或者会报错或者hang;如果自己能获得这个锁， 那么表示自己是当前在进行此操作的唯一一个session.归纳起来， dbms_lock相比于oracle lock, dbms_lock提供了一些参数可供用户选择，如timeout时间。 

下面我们来看一下怎么实现上面的那个列子。 

 

19:06:17 lab@ORCL>create table test_lock(id number primary key);

 

表已创建。

 

已用时间:  00: 00: 00.43

 

 

 

create or replace procedure allocate_unique(lockname_in        in varchar2,

                                            lockhandle_out      out varchar2,

                                            expiration_secs_in in integer) is

                                            pragma autonomous_transaction;

begin

dbms_lock.allocate_unique(lockname => lockname_in,lockhandle => lockhandle_out,expiration_secs => expiration_secs_in);

commit;

end allocate_unique;

 

 

SQL> create or replace trigger tr_lock_test

  2  before insert on test_lock

  3  for each row

  4  declare

  5  v_lk_handle varchar2(128);

  6  v_expiration_sec int :=10;

  7  resource_busy exception;

  8  pragma exception_init(resource_busy,-54);

  9  begin

 10  allocate_unique('Walmart_lock_test',v_lk_handle,v_expiration_sec);

 11  if(dbms_lock.request(v_lk_handle,dbms_lock.x_mode,dbms_lock.maxwait,true)=1) then

 12  raise resource_busy;

 13  end if;

 14  end;

 15  /

 

Trigger created

 

In Session A:

 

19:46:43 lab@ORCL>insert into test_lock values(1);

 

已创建 1 行。

 

已用时间:  00: 00: 00.01

 

 

In Session B:

 

19:46:47 lab@ORCL>insert into test_lock values(1);

insert into test_lock values(1)

            *

第 1 行出现错误:

ORA-00054: 资源正忙, 但指定以 NOWAIT 方式获取资源

ORA-06512: 在 "LAB.TR_LOCK_TEST", line 9

ORA-04088: 触发器 'LAB.TR_LOCK_TEST' 执行过程中出错

 

 

已用时间:  00: 00: 00.06

 

 

Caveat: why do we need lab.allocate_unique procedure who is an autonomous transaction? 

The Reason is in  dbms_lock there is a commit. But in trigger oracle can't permit commit in the trigger. So i choose to use autonomous transaction. Actually this is one example of autonomous transaction. 

 

eg:

 

procedure allocate_unique(lockname in varchar2,

   lockhandle out varchar2,

   expiration_secs in integer default 864000);

  --  Given a name, generate a unique lockid for this lock.  This procedure

  --    always performs a 'commit'.