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