有没有最好的方法来避免在 Oracle 中多次执行进程?

2021-12-24 00:00:00 oracle plsql

假设我有一个名为 myprocprocedure.这是一个复杂的过程,我不能允许两个实例同时执行 proc.

Let's say that I have a procedure called myproc. This is a complex process and i cannot allow two instances executing at the same time the proc.

实际上我使用dbms_application_info.set_module来做到这一点:

Actually I do this using the dbms_application_info.set_module:

procedure start_process is
begin
  dbms_application_info.set_module('myproc', 'running');
end;

并在运行该过程之前进行验证:

and verify before run the process:

select 'S'
  from v$session v
 where v.module = 'myproc'
   and v.action = 'running';

在数据库层面,有没有更好的方法来检查这个?

In the database level, is there a better way to check this?

推荐答案

使用 dbms_lock.allocate_unique 以及 dbms_lock.request.使用说明说:

使用新锁名称调用 ALLOCATE_UNIQUE 的第一个会话导致要生成并存储在 dbms_lock_allocated 表中的唯一锁 ID.后续调用(通常由其他会话)返回先前生成的锁 ID.

The first session to call ALLOCATE_UNIQUE with a new lock name causes a unique lock ID to be generated and stored in the dbms_lock_allocated table. Subsequent calls (usually by other sessions) return the lock ID previously generated.

我认为这可能就是您所追求的.

I think this could be what you're after.

相关文章