Oracle | USER

Oracle USER_LOCK Version 21c

General Information

Library Note

Morgan's Library Page Header

ACE Director Alum Daniel Morgan, founder of Morgan's Library, is scheduling complimentary technical Workshops on Database Security for the first 30 Oracle Database customers located anywhere in North America, EMEA, LATAM, or APAC that send an email to asra_us@oracle.com. Request a Workshop for your organization today.

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

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

Subprograms

CONVERT	REQUEST	SLEEP
RELEASE

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: 
      https://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);