Please help understand the meaning of the param
expiration_secs in
sys.dbms_lock.allocate_unique
Does it represent the time till which the named DB lock stays on, if not unlocked/commit/rollback by the same session?
https://docs.oracle.com/cd/E24693_01/appdev.11203/e23448/d_lock.htm#i1002533 My assumption in basic english, was it should have meant the allocated lock_handle auto release time. But it seems to be intended for some other purpose not sufficiently explained in the document.
I humbly accept my lack of understanding.
When you create a user lock, the database stores it in sys.dbms_lock_allocated.
The expiration_secs for sys.dbms_lock.allocate_unique relates to when the database will purge rows from this table.
By default the expiry date is 10 days in the future:
select sysdate from dual;
SYSDATE
12-FEB-2019 03:19:13
declare
handle varchar2(100);
begin
dbms_lock.allocate_unique (
lockname => 'test_lock',
lockhandle => handle
);
end;
/
select * from sys.dbms_lock_allocated
where name = 'test_lock';
NAME LOCKID EXPIRATION
test_lock 1073742910 22-FEB-2019 03:19:14 So when does the database clean these out?
As MOS note 840840.1 says:
The DBMS_LOCK_ALLOCATED table is periodically cleared out by calling the allocate_unique()
procedure when the modulus of the lockid is 0 and the expiration column is less than the current
date.
On every 100th attempt to create a new entry, allocate_unique purges expired entries from the
table. If the entry already exists, allocate_unique updates the expiry time.The lock I just created doesn't expire for another ten days. So even if I create a bunch more, it doesn't expire:
declare
handle varchar2(100);
begin
for i in 1 .. 100 loop
dbms_lock.allocate_unique (
lockname => 'test_lock_' || i,
lockhandle => handle,
expiration_secs => 30
);
end loop;
end;
/
select case
when regexp_like ( name, '[0-9]+' ) then 'new'
else 'orig'
end lck,
min ( to_number ( regexp_substr ( name, '[0-9]+' ) ) ) mn_num,
max ( to_number ( regexp_substr ( name, '[0-9]+' ) ) ) mx_num,
min ( expiration ), max ( expiration ), count(*)
from sys.dbms_lock_allocated
where name like 'test_lock%'
group by case
when regexp_like ( name, '[0-9]+' ) then 'new'
else 'orig'
end;
LCK MN_NUM MX_NUM MIN(EXPIRATION) MAX(EXPIRATION) COUNT(*)
orig <null> <null> 22-FEB-2019 03:19:14 22-FEB-2019 03:19:14 1
new 1 100 12-FEB-2019 03:19:45 12-FEB-2019 03:19:45 100 But I created all these with a 30s timeout.
So if we wait a while and create a bunch more locks, these are all purged:
exec dbms_lock.sleep ( 35 );
declare
handle varchar2(100);
begin
for i in 101 .. 200 loop
dbms_lock.allocate_unique (
lockname => 'test_lock_' || i,
lockhandle => handle,
expiration_secs => 30
);
end loop;
end;
/
select case
when regexp_like ( name, '[0-9]+' ) then 'new'
else 'orig'
end lck,
min ( to_number ( regexp_substr ( name, '[0-9]+' ) ) ) mn_num,
max ( to_number ( regexp_substr ( name, '[0-9]+' ) ) ) mx_num,
min ( expiration ), max ( expiration ), count(*)
from sys.dbms_lock_allocated
where name like 'test_lock%'
group by case
when regexp_like ( name, '[0-9]+' ) then 'new'
else 'orig'
end;
LCK MN_NUM MX_NUM MIN(EXPIRATION) MAX(EXPIRATION) COUNT(*)
orig <null> <null> 22-FEB-2019 03:19:14 22-FEB-2019 03:19:14 1
new 101 200 12-FEB-2019 03:20:22 12-FEB-2019 03:20:22 100 But the original remains, because we still have nearly 10 days to wait until it expires.
If you allocate_unique for an existing entry, this will update it's expiry time:
select * from sys.dbms_lock_allocated
where name = 'test_lock';
NAME LOCKID EXPIRATION
test_lock 1073742910 22-FEB-2019 03:19:14
declare
handle varchar2(100);
begin
dbms_lock.allocate_unique (
lockname => 'test_lock',
lockhandle => handle,
expiration_secs => 100000
);
end;
/
select * from sys.dbms_lock_allocated
where name = 'test_lock';
NAME LOCKID EXPIRATION
test_lock 1073742910 13-FEB-2019 07:12:09