$page_title = $titlebar_text; $header_bar_text = $titlebar_text; ///////////////////////////////////// // get header include('rlib_header.php'); // print << 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
  EOM; ///////////////////////////////////// // get footer include('rlib_footer.php'); ?>