Oracle USER_LOCK
Version 18.3.0.1

General Information
Library Note Morgan's Library Page Header
Coming to OpenWorld 2018? Be sure to visit the TidalScale booth in Moscone South and learn how to solve performance problems and lower costs with Software Defined Servers. Before you visit the booth, or if you can't make it this year, check out TidalScale at www.tidalscale.com. Be sure to click on the Solutions link and look through the Oracle resources.
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
Compiler Warnings SQL> @?/rdbms/admin/userlock.sql
drop package user_lock
*
ERROR at line 1:
ORA-04043: object USER_LOCK does not exist

SP2-0808: Package created with compilation warnings

SP2-0810: Package Body created with compilation warnings


Synonym created.

Grant succeeded.

SQL> sho err
Errors for PACKAGE BODY USER_LOCK:

LINE/COL ERROR
-------- -----------------------------------------------------------------
36/12 PLW-05004: identifier CONVERT is also declared in STANDARD or is
a SQL builtin

36/12 PLW-06010: keyword "CONVERT" used as a defined name
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
What's New In 12cR2
What's New In 18cR3