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博客

实验DBMS_LOCK

最新文章