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。
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.
Table 83-9 REQUEST Function Parameters
Parameter | Description |
---|---|
| User assigned lock identifier, from 0 to 1073741823, or the lock handle, returned by |
| Mode that you are requesting for the lock. For the available modes and their associated integer identifiers, seeConstants. |
| 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 ( |
| Set this parameter to Otherwise, the lock is held until it is explicitly released or until the end of the session. |
Table 83-10 REQUEST Function Return Values
Return Value | Description |
---|---|
| Success |
| Timeout |
| Deadlock |
| Parameter error |
| Already own lock specified by |
| 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编程艺术深入理解数据库体系结构(第三版)》