Oracle USER_LOCK Demos

Oracle USER_LOCK Version 11.2.0.3

General Information

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

Installation

conn / as sysdba

@?/rdbms/admin/userlock.sql

conn uwclass/uwclass

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	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

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

SELECT user_lock.request(20296, 1, 0, 100)
FROM dual;

SLEEP

Invoke a PL/sQL Sleep

user_lock.sleep(tens_of_millisecs IN NUMBER);

exec user_lock.sleep(500);