Oracle 请帮助理解系统中的过期秒

请帮助理解参数的含义expiration_secs在

sys.dbms_lock.allocate_unique

如果未通过同一会话解锁/提交/回滚，它是否代表指定的DB锁保持打开的时间？

https://docs.oracle.com/cd/E24693_01/appdev.11203/e23448/d_lock.htm#i1002533

My assumption在 basic english, was it should have meant the allocated lock_handle auto release time. But it seems to be在tended for some other purpose not sufficiently explained在 the document.

我谦卑地接受我的不理解。

创建用户锁时，数据库将其存储在sys.Dbms_lock_deployed中。

sys.dbms_lock.allocate_unique的expiration_secs与数据库何时从该表中清除行有关。

默认情况下，到期日期为未来10天:

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   

那么数据库什么时候清理这些呢？

正如MOS note 840840.1所说:

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.

我刚刚创建的锁在十天内不会过期。所以即使我创建了一堆更多，它也不会过期:

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

但是我用30s超时创建了所有这些。

因此，如果我们等待一段时间并创建更多的锁，这些都被清除:

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

但是原始的仍然存在，因为我们还有将近10天的时间等待它到期。

如果您为现有条目分配locate_unique，这将更新它的到期时间:

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