Database Reference
In-Depth Information
SQL> VARIABLE result NUMBER
SQL> BEGIN
:result:=DBMS_LOCK.REQUEST(
lockhandle => :lockhandle,
lockmode => DBMS_LOCK.X_MODE,
timeout => 0,
release_on_commit => TRUE /* default is false */
);
END;
/
SQL> SELECT decode(:result,1,'Timeout') Result FROM dual;
RESULT
-------
Timeout
The second session was unable to obtain the lock held by the first session in exclusive mode.
The time spent waiting for the lock is accounted for by the wait event
enqueue
in Oracle9
i
and
enq: UL - contention
in Oracle10
g
. The abbreviation UL means user lock. After session 1 has
committed, session 2 is able to obtain the lock, since session 1 had specified
RELEASE_ON_COMMIT=
TRUE
in its lock request.
Session 1:
SQL> COMMIT;
Commit complete.
Session 2:
BEGIN
:result:=DBMS_LOCK.REQUEST(
lockhandle => :lockhandle,
lockmode => DBMS_LOCK.X_MODE,
timeout => 0,
release_on_commit => TRUE /* default is false */
);
END;
/
SQL> SELECT decode(:result,0,'Success') Result FROM dual;
RESULT
-------
Success
As you can see, very little code is required to lock resources for exclusive use with
DBMS_LOCK
.
Just like with AQ, there is no need for any resource-intensive polling, since a non-zero time-out
may be used when waiting for a lock. Waiting sessions sleep on the event
enq: UL - contention
,
in case another session holds the requested lock in an incompatible mode. The second parameter
of this wait event (
V$SESSION_WAIT.P2
) is the user lock identifier (
LOCKID
) in the dictionary view
SYS.DBMS_LOCK_ALLOCATED
.
