CODE
Oracle Code Library
JOBS
Find Or Post Oracle Jobs
FORUM
Oracle Discussion & Chat
PSOUG Home Code Snippets Oracle Reference Oracle Functions PSOUG Forum Oracle Blogs Bookmark and Share
 
 Search the Reference Library pages:  

Free
Oracle Magazine
Subscriptions
and Oracle White Papers

Oracle USER_LOCK
Version 11.1
 
General Information
Source {ORACLE_HOME}/rdbms/admin/userlock.sql
conn / as sysdba

@?/rdbms/admin/userlock.sql

conn uwclass/uwclass

desc user_lock
Constants
Lock Mode Data Type Value
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
psdlcv psdlgt psdlrl psdwat
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
 
CONVERT
Determine if a lock can be converted convert(id NUMBER, lockmode NUMBER, timeout 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 release(id NUMBER) RETURN NUMBER;

0 = Success | 4 = Don't own lock ID
CREATE OR REPLACE PROCEDURE myproc(iKey in VARCHAR2) 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 NUMBER, lockmode NUMBER, timeout NUMBER, global number) RETURN NUMBER ;

0 = Success | 1 = Timeout | 2 = Deadlock | 3 = Parameter Error
SELECT user_lock.request(20296, 1, 0, 100)
FROM dual;
 
SLEEP
Sleep user_lock.sleep(tens_of_millisecs NUMBER);
exec user_lock.sleep(500)
 
Related Topics
DBMS_LOCK
 
Home      :      Code Library      :      Sponsors      :      Privacy      :      Terms of Use      :      Contact Us [53 users online]    © 2010 psoug.org