oracle编程艺术触发器,实验DBMS_LOCK

dbms_lock实验。(oracle 9i&10g编程艺术第六章2.5节)

创建一个带主键的表，和一个触发器，触发器会防止两个或多个会话同事插入相同的值。这个触发器使用DBMS_UTILITY.GET_ HASH_VALUE 来计算主键的散列值，得到一个0～1 073 741 823 之间的数(这也是Oracle 允许我们使用的锁ID 号的范围)。然后使用DBMS_LOCK.REQUEST 根据这个ID 分配一个排他锁(也称独占锁，exclusive lock)。一次只有一个会话能做这个工作。

grant execute on dbms_lock to scott;create table demo (x int primary key);create or replace trigger demo_biferbefore insert on demofor each rowdeclarel_lock_id number;resource_busy exception;pragma exception_init(resource_busy,-54);beginl_lock_id :=dbms_utility.get_hash_value(to_char( :new.x),0,1024);if ( dbms_lock.request( id => l_lock_id,lockmode => dbms_lock.x_mode,timeout => 0,release_on_commit => TRUE ) <> 0 )thenraise resource_busy;end if;end;/

SQL> grant execute on dbms_lock to scott;Grant succeeded.SQL> conn scott/tigerConnected.SQL> create table demo (x int primary key);Table created.SQL> create or replace trigger demo_bifer2  before insert on demo3  for each row4  declare5     l_lock_id number;6     resource_busy exception;7     pragma exception_init(resource_busy,-54);8  begin9     l_lock_id :=10             dbms_utility.get_hash_value(to_char( :new.x),0,1024);11     if ( dbms_lock.request12             ( id => l_lock_id,13                     lockmode => dbms_lock.x_mode,14                     timeout => 0,15                     release_on_commit => TRUE ) <> 0 )16     then17             raise resource_busy;18     end if;19     end;20  /Trigger created.SQL> insert into demo values(1);1 row created.SQL> insert into demo values(1);insert into demo values(1)*ERROR at line 1:ORA-00054: resource busy and acquire with NOWAIT specifiedORA-06512: at "SCOTT.DEMO_BIFER", line 14ORA-04088: error during execution of trigger 'SCOTT.DEMO_BIFER'