实验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_bifer
before insert on demo
for each row
declare
    l_lock_id number;
    resource_busy exception;
    pragma exception_init(resource_busy,-54);
begin
    l_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 )    
    then
        raise resource_busy;
    end if;
    end;
/


SQL> grant execute on dbms_lock to scott;

Grant succeeded.

SQL> conn scott/tiger
Connected.

SQL> create table demo (x int primary key);

Table created.

SQL> create or replace trigger demo_bifer
  2  before insert on demo
  3  for each row
  4  declare
  5     l_lock_id number;
  6     resource_busy exception;
  7     pragma exception_init(resource_busy,-54);
  8  begin
  9     l_lock_id :=
 10             dbms_utility.get_hash_value(to_char( :new.x),0,1024);
 11     if ( dbms_lock.request
 12             ( id => l_lock_id,
 13                     lockmode => dbms_lock.x_mode,
 14                     timeout => 0,
 15                     release_on_commit => TRUE ) <> 0 )
 16     then
 17             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 specified
ORA-06512: at "SCOTT.DEMO_BIFER", line 14
ORA-04088: error during execution of trigger 'SCOTT.DEMO_BIFER'

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/22779291/viewspace-630784/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/22779291/viewspace-630784/