| General Information |
| Note: The source code for this package is not wrapped |
| Source |
{ORACLE_HOME}/rdbms/admin/userlock.sql |
conn / as sysdba
@?/rdbms/admin/userlock.sql
conn uwclass/uwclass
desc user_lock |
| 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 |
None |
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 |
|
| Dependencies |
Execute is granted to PUBLIC |
| |
| 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 |
| Sleep |
user_lock.sleep(tens_of_millisecs IN NUMBER); |
| exec user_lock.sleep(500); |
