Example: Acquiring an exclusive lock
A
session tries to acquire a lock named
tq84 lock test in exclusive mode:
declare
lockhandle varchar2(128);
ret pls_integer;
begin
dbms_lock.allocate_unique_autonomous(
'tq84 lock test',
lockhandle
);
ret := dbms_lock.request(
lockhandle,
dbms_lock.x_mode, -- exclusive access,
timeout => 0
);
if ret = 0 then
dbms_output.put_line('Lock was sucessfully acquired');
elsif ret = 1 then
raise_application_error(-20800, 'Lock is already exclusively acquired, lockhandle = ' || lockhandle);
else
raise_application_error(-20800, 'Error! Ret = ' || ret);
end if;
end;
/
If no other session has already acquired this lock in exclusive mode, this PL/SQL block prints
Lock was sucessfully acquired
If another session now executes the same block, the following error is thrown:
ORA-20800: Lock is already exclusively acquired, lockhandle = 10737418641073741864187
It's now possible to determine the session that owns the lock with the following statement:
select
ses.sid,
ses.username,
ses.osuser,
lck.ctime secs_allocated,
ses.program,
ses.module
from
v$lock lck join
v$session ses on lck.sid = ses.sid
where
lck.type = 'UL' and
lck.id1 = substr('10737418641073741864187', 1, 10);
The first session releases a lock like so:
declare
lockhandle varchar2(128);
ret pls_integer;
begin
dbms_lock.allocate_unique_autonomous(
'tq84 lock test',
lockhandle
);
ret := dbms_lock.release(
lockhandle
);
if ret != 0 then
raise_application_error(-20800, 'Error! Ret = ' || ret);
end if;
end;
/