使用DBMS_LOCK防止会话阻塞

REQUEST Function

This function requests a lock with a specified mode. REQUEST is an overloaded function that accepts either a user-defined lock identifier, or the lock handle returned by the ALLOCATE_UNIQUE procedure.

该函数请求一个指定模式下的锁。REQUEST 是一个重载函数接受用户定义的锁名称或者ALLOCATE_UNIQUE过程返回的lock handle。

Syntax

DBMS_LOCK.REQUEST(
   id                 IN  INTEGER ||
   lockhandle         IN  VARCHAR2,
   lockmode           IN  INTEGER DEFAULT X_MODE,
   timeout            IN  INTEGER DEFAULT MAXWAIT,
   release_on_commit  IN  BOOLEAN DEFAULT FALSE)
  RETURN INTEGER;

The current default values, such as X_MODE and MAXWAIT, are defined in the DBMS_LOCK package specification.

Parameters

Table 83-9 REQUEST Function Parameters

Parameter	Description
id or lockhandle	User assigned lock identifier, from 0 to 1073741823, or the lock handle, returned by ALLOCATE_UNIQUE, of the lock mode you want to change
lockmode	Mode that you are requesting for the lock. For the available modes and their associated integer identifiers, seeConstants.
timeout	Number of seconds to continue trying to grant the lock. If the lock cannot be granted within this time period, then the call returns a value of 1 (timeout).
release_on_commit	Set this parameter to TRUE to release the lock on commit or roll-back. Otherwise, the lock is held until it is explicitly released or until the end of the session.

Return Values

Table 83-10 REQUEST Function Return Values

Return Value	Description
0	Success
1	Timeout
2	Deadlock
3	Parameter error
4	Already own lock specified by id or lockhandle
5	Illegal lock handle

SYS@PROD1> grant execute on dbms_lock to scott; --执行DBMS_LOCK必须先授予权限

Grant succeeded.

SCOTT@PROD1> set echo on

SCOTT@PROD1> create table demo ( x int primary key );

Table created.

SCOTT@PROD1> 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 ); --将主键散列成1024个不同的锁ID
 11  	   if ( dbms_lock.request
 12  		    (  id		 => l_lock_id,
 13  		       lockmode 	 => dbms_lock.x_mode,   --分配了一个排它锁
 14  		       timeout		 => 0,
 15  		       release_on_commit => TRUE ) not in (0,4) )
 16  	   then
 17  	       raise resource_busy;
 18  	   end if;
 19    end;
 20  /

Trigger created.

SCOTT@PROD1> insert into demo(x) values (1);

1 row created.

SCOTT@PROD1> declare
  2  	   pragma autonomous_transaction;  --使用自治事务，模拟这段代码好像在另一个会话中执行来阻塞insert
  3    begin
  4  	   insert into demo(x) values (1);
  5  	   commit;
  6    end;
  7  /
declare
*
ERROR at line 1:
ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired
ORA-06512: at "SCOTT.DEMO_BIFER", line 14
ORA-04088: error during execution of trigger 'SCOTT.DEMO_BIFER'
ORA-06512: at line 4         <span style="font-family: 'Helvetica Neue', 'Neue Helvetica', Arial, sans-serif; font-size: 14px;">--前一次insert已经锁定了x列，产生了触发器中的ORA-54错误</span>

--参考来源《Oracle编程艺术深入理解数据库体系结构（第三版）》