Oracle USER_LOCK
Version 12.1.0.1

General Information
Library Note Morgan's Library Page Footer
The Library is currently in the process of being upgraded from Oracle Database Version 12.1.0.1 to 12.1.0.2. Demos are being upgraded to reflect the new Container paradigm as well as EBR (Edition Based Redefinition) and may contain references to CDBs, PDBs, and other objects you may not be familiar with such as CDB_OBJECTS_AE: Welcome to 12c.

Interested in Oracle GoldenGate? Check out the IGGOUG,
Purpose Routines that allow a user to request, convert and release locks managed by the rdbms lock management services. All lock ids are prepended with the 'UL' prefix so that they cannot conflict with DBMS locks.
Note The source code for this package is not wrapped
AUTHID DEFINER
Constants
Name Data Type Value
badseconds_num NUMBER -38148
global NUMBER 1
local NUMBER 0
maxwait NUMBER 32767
nl_mode NUMBER 1
ss_mode NUMBER 2
sx_mode NUMBER 3
s_mode NUMBER 4
ssx_mode NUMBER 5
x_mode NUMBER 6
Dependencies
DBMS_SYS_ERROR    
Documented Yes
First Available Not known
Installation conn / as sysdba

@?/rdbms/admin/userlock.sql

conn uwclass/uwclass@pdbdev

desc user_lock
Lock Compatibility Rules

When another process holds "held", an attempt to get "get" does  the following
Held NL SS SX S SSX X
NL Success Success Success Success Success Success
SS Success Success Success Success Success Fail
SX Success Success Success Fail Fail Fail
S Success Success Fail Fail Fail Fail
SSX Success Success Fail Fail Fail Fail
X Success Fail Fail Fail Fail Fail
Security Model Owned by SYS with EXECUTE granted to PUBLIC
Source {ORACLE_HOME}/rdbms/admin/userlock.sql
Subprograms
 
CONVERT
Determine if a lock can be converted user_lock.convert(id IN NUMBER, lockmode IN NUMBER, timeout IN NUMBER) RETURN NUMBER;

0 = Success | 1 = Timeout | 2 = Deadlock | 3 = Parameter Error | 4 = Don't own lock ID
SELECT user_lock.convert(2000, 1, 0)
FROM dual;
 
RELEASE
Release an existing lock user_lock.release(id IN NUMBER) RETURN NUMBER;

0 = Success | 4 = Don't own lock ID
-- code source: http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:340617419132

CREATE OR REPLACE PROCEDURE myproc(iKey in VARCHAR2) AUTHID DEFINER IS
 vHashVal  NUMBER;
 vLockStat NUMBER;
 vIdx      NUMBER;
BEGIN
  -- compute Hash value for iKey
  vHashVal := 0;

  FOR vIdx IN 1 .. LENGTH(iKey)
  LOOP
    vHashVal := mod(vHashVal * 64 + ASCII(SUBSTR(iKey,vIdx,1)), 2000000000);
  END LOOP;

  -- create a user lock for hash(iKey)
  vLockStat := user_lock.request(vHashVal, user_lock.x_mode, user_lock.maxwait, user_lock.global);

-----------------------------------
-- CRITICAL SECTION:
-- SOME CODE THAT MUST NOT RUN
-- CONCURRENTLY FOR THE SAME iKey
-----------------------------------

  -- release the user lock for hash(iKey)

  vLockStat := user_lock.release(vHashVal);
EXCEPTION
  WHEN OTHERS THEN
    BEGIN
      -- release the user lock for hash(iKey)
      vLockStat := user_lock.release(vHashVal);
    END;
END myproc;
/
 
REQUEST
Request a lock user_lock.request(
id       IN NUMBER,
lockmode IN NUMBER,
timeout  IN NUMBER,
global   IN NUMBER)
RETURN NUMBER;

0 = Success | 1 = Timeout | 2 = Deadlock | 3 = Parameter Error
See RELEASE Demo Above
 
SLEEP
Invoke a PL/sQL Sleep user_lock.sleep(tens_of_millisecs IN NUMBER);
exec user_lock.sleep(500);

Related Topics
DBMS_LOCK
Packages