使用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
	Success
1	Timeout
2	Deadlock
3	Parameter error
4	Already own lock specified by  id  or  lockhandle
5	Illegal lock handle

[email protected]> grant execute on dbms_lock to scott; --執行DBMS_LOCK必須先授予權限

Grant succeeded.

[email protected]> set echo on

[email protected]> create table demo ( x int primary key );

Table created.

[email protected]> 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.

[email protected]> insert into demo(x) values (1);

1 row created.

[email protected]> 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程式設計藝術深入了解資料庫體系結構（第三版）》