BLOCKING

The five common DML statements that willblock in the database are INSERT, UPDATE,DELETE.MERGE and SELECT FOR UPDATE.

 

The solution to a blocked SELECT FOR UPDATEis trivial: simply add the NOWAIT clause and it will no longer block. Instead,your application will report back to the end user that the row is alreadylocked.

 

INSERTS的阻塞通常发生在允许终端用户生成主键或者唯一性列值的时候，比较简单的避免这种情况发生的办法是用SEQUENCE来生成主键或者唯一性列值。如果不能使用SEQUENCE的话，可以使用手动的锁，通过DBMS_LOCK包。

 

ORACLE有无限的ROW-LEVEL locking，但是有有限的enqueue locks.

要将执行DBMS_LOCK的权限赋给用户（SCHEMA）

SQL> grant execute on dbms_lock to ops;

授权成功。

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  /

触发器已创建

在一个session下执行
SQL> insert into demo values (1);

已创建 1 行。


换另一个session下

SQL> insert into demo values (1);
insert into demo values (1)
            *
ERROR 位于第 1 行:
ORA-00054: 资源正忙，要求指定 NOWAIT
ORA-06512: 在"OPS.DEMO_BIFER", line 14
ORA-04088: 触发器 'OPS.DEMO_BIFER' 执行过程中出错